MySQL trigger + notify a long-polling Apache/PHP connection

Question

I know there are Comet server technologies that do this but I want to write something simple and home-grown.

When a record is inserted into a MySQL table, I want it to somehow communicate this data to a series of long-polled Apache connections using PHP (or whatever). So multiple people are "listening" through their browser and the second the MySQL INSERT happens, it is sent to their browser and executed.

The easy way is to have the PHP script poll the MySQL database, but this isn't really pushing from the server and introduces some unacceptable order of unnecessary database queries. I want to get that data from MySQL to the long-polling connection essentially without the listeners querying at all.

Any ideas on how to implement this?

Answer 1

I have been trying all kinds of ideas for a solution to this as well and the only way to take out the polling of sql queries is to poll a file instead. If the fill equals 0 then continue looping. If file equals 1 have loop run sql queries and send to users. It does add another level of complexity but I would think it means less work by mysql but same for apache or what ever looping daemon. You could also send the command to a daemon "comet" style but it is going to fork and loop on each request as well from what I have seen on how sockets work so hopefully someone will find a solution to this.