How can I receive an e-mail when my MySQL table is

Question

Hi I was wondering if there was a way in MySQL to automatically send an e-mail to myself whenever there is a row added to a MySQL table?

Answer 1

You can get the lastInsertedId and if it's not zero send e-mail.

$db = new PDO('mysql:host=localhost;dbname=data','root','');

function insertData(){
    global $db;
    $db->exec("INSERT INTO `albums`(`Artist`, `Album`, `Year`, `Cover`, `Tracks`) VALUES ('Cosmic Gate','Start to feel','2014','cover.jpg',13)");
    $id = $db->lastInsertId();
    //returns last inserted ID
    return $id;
}

function selectData($id){
    global $db;
    $results = $db->query("SELECT * FROM albums WHERE ID = $id");
    $results = $results->fetchAll(PDO::FETCH_ASSOC);
    $message = "Artist: ".$results[0]['Artist']."\n"."Album: ".$results[0]['Album'];
    return $message;
}
$email = insertData(); //get last inserted id
$EmailTo = "[e-mail]";
$subject = 'mysql update';
$message = "New data inserted: \n".selectData($email);

if($email != 0){

    if(mail($EmailTo, $subject, $message)){
        echo 'success'; // for testing
    }
} 

Answer 2

The best way to achieve this would be using a trigger and a cron. Create a 'notification queue' table and populate that with a trigger when a row is inserted in the desired table.

eg.

CREATE TABLE `notification_queue` (
  `notification_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `sent` tinyint(1) unsigned NOT NULL,
  PRIMARY KEY (`notification_id`)
);

Then define a simple trigger:

DELIMITER $$
CREATE TRIGGER t_notification_insert 
AFTER INSERT ON [table_being_inserted]
FOR EACH ROW 
BEGIN 
    INSERT INTO `notification_queue` (`sent`) VALUES (0);
END$$
DELIMITER ;

From that point, all you need to do is make a crontab run on the server (say every minute) which selects from the notification table where sent = 0, send the notification and set sent = 1

As far as I know, that's the best way to get that information out of the DB without reading the bin logs.

If you need an example of the script to run with cron:

#!/bin/bash

DB_USER=''
DB_PASS=''
DB_NAME=''

ID=`mysql -u$DB_USER -p$DB_PASS $DB_NAME -Bse "SELECT notification_id FROM notification_queue WHERE sent=0 LIMIT 1;"`

if [[ ! -z $ID ]] 
then
    # SEND MAIL HERE
    RESULT=`mysql -u$DB_USER -p$DB_PASS $DB_NAME -Bse "UPDATE notification_queue SET sent=1 WHERE notification_id = $ID;"`
    echo "Sent"
fi