Live update notification on database changes MYSQL PHP

Question

I was wondering how to trigger a notification if a new record is inserted into a database, using PHP and MySQL.

Answer 1

You can create a trigger than runs when an update happens. It's possible to run/notify an external process using a UDF (user defined function). There aren't any builtin methods of doing so, so it's a case of loading a UDF plugin that'll do it for you.

Google for 'mysql udf sys_exec' or 'mysql udf ipc'.

Answer 2

The simplest thing is probably to poll the DB every few seconds and see if new records have been inserted. Due to query caching in the DB this shouldn't effect DB performance substantially.

Answer 3

MySQL does now have triggers and stored procedures, but I don't believe they have any way of notifying an external process, so as far as I know it's not possible. You'd have to poll the database every second or so to look for new records.

Even if it were, this assumes that your PHP process is long-lived, such that it can afford to hang around for a record to appear. Given that most PHP is used for web sites where the code runs and then exits as quickly as possible it's unclear whether that's compatible with what you have.

Answer 4

If all your database changes are made by PHP I would create a wrapper function for mysql_query and if the query type was INSERT, REPLACE, UPDATE or DELETE I would call a function to send the respective email.

EDIT: I forgot to mention but you could also do something like the following:

if (mysql_affected_rows($this->connection) > 0)
{
    // mail(...)
}

Answer 5

One day I ask in MySQL forum if event like in Firebird or Interbase exist in MySQL and I see that someone answer Yes (I'm really not sure)

check this : http://forums.mysql.com/read.php?84,3629,175177#msg-175177

Answer 6

This can be done relatively easily using stored procedures and triggers. I have created a 'Live View' screen which has a scrolling display which is updated with new events from my events table. It can be a bit fiddly but once its running its quick.