Topic: fire off email when database has entry (w/ PHP)

[webguync]

Is there a way use PHP to send an email to multiple people when there is an entry in a MySQL table?

for example when a field contains data, and email is sent(automatically) w/ the contents of that data?

[schilly]

yea it's pretty easy.

read up on the mail function: www.php.net/manual/en/function.mail.php

so when you update data in whatever table it is, check for the value you want then use mail() to email the people required.

[webguync]

yea, I understand pretty much how the mail function works and I want to send an email whenever a value ==1 in my field, but not sure how to tie the two together. Would I need to have UPDATE SQL statement?

[schilly]

there is a couple ways.

-either when you do an update statement check to see if the value is set to one and if it is send out the email.
-set up a cron to automatically check the database after a certain period of time and send out emails for every record it finds with value = 1

[webguync]

I like the idea of the second one (cron). Can you provide any info on how that is done? Will it only send off emails if the value == 1?

[schilly]

you'll have to check with your web server to see if they allow for crons to be set up. it's basically a daemon that runs on your web server where you can set triggers to go off after certain time intervals (minute, hour, day, month, etc).

In this case, it would trigger a PHP script which would select all the rows from your db where value = 1 then send off an email to your group.

[webguync]

ok, so is it done entirely via server config or would I need to create a PHP script to upload?

[schilly]

you need a php script to do the work. the cron is just the timer that triggers the sciprt.

[webguync]

The hosting company is sometimes hard to get a hold of. going back the first solution with the update. Could you provide a quick example with how this is done?

[schilly]

so you'd likely be doing this via a form post when you update the record.

if($_POST['submitButton']){

//validate post data here

//run update query. value and id are from your $_POST data

$sql = "UPDATE table set value = '$value' WHERE id = $id";

mysql_query($sql);

//check value to see if it meets our threshold 

if($value == 1){

mail('email@domain.com, email2@domain.com','subject','body');

}




}


something like that.

[webguync]

I have code which created the log entry, so I should just be able to add the email part to this right?


<?php
//include Database Class
require_once('databaseClass.php');

$userID = $_POST['userID'];
$sect = 1;
$time = gmmktime();
$attempt = 1;

$db = new Database('localhost','username','password','DBName');

$sql = "INSERT INTO og_March2010 (user_id,section,date,attempt) VALUES ($userID,$sect,$time,$attempt)";
$result = $db->query($sql);

if($result) {

$replySQL = "SELECT log_id FROM og_March2010 WHERE user_id=$userID";

$kickback = $db->query($replySQL);

$kickback->get_rows();

$row = $kickback->fetch_assoc();

$logID = $row['log_id'];

$db->close();

echo 'triumph=sieg&logid='.$logID;

}

?>


anytime there is an entry I would want an email to be sent.

[schilly]

[php]

if($result) {

mail('user@domain.com','subject','body');

   $replySQL = "SELECT log_id FROM og_March2010 WHERE user_id=$userID";
   $kickback = $db->query($replySQL);
   $kickback->get_rows();
   $row = $kickback->fetch_assoc();
   $logID = $row['log_id'];
   $db->close();

   echo 'triumph=sieg&logid='.$logID;
   }
/php]

you can put $userID,$sect,$time,$attempt in a string if you want to include them in the body of the email.

[webguync]

thanks, in order to turn the variables into a string would it be best to use
str_replace?


$body  = str_replace($userID, $sect, $time,$attempt);

[schilly]

nope. just concatenate them.

$body  = "User: $userID\n\nSect: $sect\n\nTime: $time\n\nAttempt: $attempt";

\n will give you a new line in an email.

[webguync]

thanks, and would I just need to change mail to this?


mail('user@domain.com','Test Results','$body');