Action Listener in PHP/Mysql, whenever any record inserted into table?

Question

Is their any method/way that we come to know, any change occurs in desired table?

I have implemented one solution that it checks in db every 30 sec, that is if any change occur in chats table then refresh my chats listing. it waists lot of performance and slow down the site.

Is their any way that our action listener only calls whenever any row is inserted in table?

Any idea?

Answer 1

You may create table with notifications (which will always contains just unread messages for last 2 hours or so) and then create trigger [syntax] that will create new notification each time change occurs:

CREATE TRIGGER create_notification AFTER INSERT ON chats
FOR EACH ROW INSERT INTO notifications (...) VALUES( NEW.col1, NEW.col2)...

Then you'll end up with much smaller (and faster) database table.

There are some alternatives like writing a socket server but I think it would be less resource effective than pooling database plus I have a bad experience running php scripts for a long time.