I actually want to mail or somehow notify a user when a record has been added to the database. Is it possble? Like ON INSERT trigger, I execute some PHP?
-
4Why you need to do like this..? Wont you be inserting from PHP code..?Shameer– Shameer2011-04-21 10:41:58 +00:00Commented Apr 21, 2011 at 10:41
-
@Shameer, I am using a WordPress plugin, I dont know or it will take very long to figure out where this is handled I guess ...Jiew Meng– Jiew Meng2011-04-21 14:35:58 +00:00Commented Apr 21, 2011 at 14:35
-
It wont take too much time. Check the plugin directory to find which code is inserting into db. Then write the mail function on successful insertion.Shameer– Shameer2011-04-22 06:57:19 +00:00Commented Apr 22, 2011 at 6:57
Add a comment
|
5 Answers
You can not execute PHP script with mysql events.
You have to do this in PHP end.
Check if the insert command is executed successfully and send mail.
Comments
Just check if the insert is succesfull and then send mail to the customer.
Comments
you cant execute php in a mysql query but you can check if the query was made successfully and then mail someone, mysql_query() will return a boolean (true or false). if you are using php, let's use it the right way ;)
if(mysql_query('// your query here')){
//mail someone
}
Comments
It is possible to execute external programs from within MySQL triggers by installing MySQL sys_exec UDF
However, it seems that it's an overhead to do so. Why would you make MySQL trigger the notification when it's PHP that does the insert and at that point you know you entered the data?
2 Comments
Jiew Meng
I am using a WordPress plugin and I dont know where this is handled ... digging through the code will take time
Michael J.V.
Trust me, making the change to MySQL such as this one, creating the trigger, invoking external program, taking care of errors is MUCH more hassle than digging trough code of the plugin.
The quickest and easiest way of generating notifications (emails, etc.) on database insertions/modifications/deletions is by doing so from the script which is performing the change in the database.
<?php
$res = mysql_query( 'INSERT INTO `table` ( `field1` ) VALUES ( "One" )' );
if( $res ){
# Send Notification
}
?>
Failing that, in the event that you are not performing the database manipulations yourself (for some reason), or you want to be able to perform timed summaries (hourly, daily, weekly), you would need to use something like a Cron Job to poll the database and check for changes.
<?php
# A file containing an integer which is the Highest ID in the table
$indexFile = 'lastIndexID.txt';
# Get that Highest ID from the file, if it exists
$lastIndex = 0;
if( file_exists( $indexFile )
$lastIndex = (int) file_get_contents( $indexFile );
# Check the Database
$res = mysql_query( 'SELECT `id` FROM `table` ORDER BY `id` DESC LIMIT 1' );
if( $res && mysql_num_rows( $res )==1 ){
$row = mysql_fetch_assoc( $res );
# Check if the ID has increased (ie new rows added)
if( $row['id']>$lastIndex ){
# Send Notification
# The number of New Rows will be the difference between $row['id'] and $lastIndex
# Update the Index File
file_put_contents( $indexFile , $row['id'] );
}
}else{
# An Error Occurred
}
?>
Comments
