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 protected], [email protected]','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]

 

if($result) {

 

mail('[email protected]','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('[email protected]','Test Results','$body');

[schilly]

you can do this

 

mail('[email protected]','Test Results',$body);

 

or

 

mail('[email protected]','Test Results',"$body");

 

if you put a string in single quotes, PHP will not look for any variables inside the string. So your body would of looked like "$body" when you got the email.

[webguync]

cool thanks. Seems to be working. One more question, if I want to add multiple emails, would I just separate by comma?

 

mail('[email protected]','[email protected]','Test Results',$body);

[schilly]

yup. but keep it in the same string as it's the same argument for mail()

mail('[email protected],[email protected]','Test Results',$body);

 

you can also do:

 

mail('Name1 <[email protected]>, Name2 <[email protected]>','Test Results',$body);

 

[webguync]

COOL, THANKS!