session thingy...

sicksound

ok i'm not sure if i'm saying this properly, just let me know if i am making no sense whatsoever.
here is the situation:

i've been trying to figure out a way a user can ALMOST instantaiously find out a change in a MySQL database. ie, if a change has happened in the database, the user will find out right away.

now, i dont know of any way i can PUSH an alert to let the user now there is a change, so i figure the only way to do it is to keep checking say every 5 seconds to see if a change has happened, and if so, use the php script to send an alert to the user. but the problem is, there may be a time when this application will grow to thousands of users simultaneously checking for a database change, and i dont think it would be a good idea to have thousands of users checking every 5 seconds each, cause it seems like that could be cause for lots of server problems. so the solution i'm thinking of, if this is possible, is through sessions. what i'm thikning would happen is that when the change is made to the datase cell that the user needs an alert about, somehow a session variable related to the user's current log on session (stored in the database) will be changed to notify the user of the change, and thus, to find out if there are any changes in the database the user has to check for a change in their session variable as opposed to accessing the database every 5 seconds. plus this way, i could have the users check every 1 second instead of 5 every because of no database checking server problems. and when there are thousands of users on this application, they will be only checking their personal session variables, which will have a much shorter access time... i think.

so what i'm wondering is, is this possible at all? let me illustrate this possible solution:

user1 is logged onto the site and needs to know when the news section of the database has been changed, right away! user2 logs on and changes the news section, and upon doing so, finds out that user1 wants to know that it has been changed, and thus the script finds the session ID associated with user1, changes the user1 variable $SESSION['news_change'] to 'yes', and then a second later user1 checks to see is $SESSION['news_change'] == 'yes', finds out it does, and thus is sent an alert.

possible, or not? one user changing another user's session variable?

or do i make any sense?

please help if you can, and thanks for any reply at all!!

devinemke

a few ideas:

if you are on UNIX/LINUX you could setup a cron job that runs every 5 seconds that runs a SHOW TABLE STATUS query against the db and writes the results to a log file. one of the things SHOW TABLE STATUS spits out is the last update time of all the tables. then on every page request, grab the log and compare the current time with the update time in the log and alert the user if the table has changed. the reason i suggest the log file is so each user doesn't have to poll the db directly on every hit, just one poll via the cron, then each user just polls the log file. this will only show that achange was made, not what the specific change was.
to find out which records were updated you could add a TIMESTAMP field to all of your tables. in mySQL the TIMESTAMP field is automagically updated to the current time on every INSERT/UPDATE query. on every page request you could run a query like this:
```
SELECT * FROM table ORDER BY timestamp DESC LIMIT 1
```
this would return the last record that was INSERTED ot UPDATED. again just compare the current time with the timestamp to see if it was update in the last 5 seconds.

sicksound

hey!
thanks for the help, that sounds like a pretty good solution. but it wouldn't work for my situation, cause i will never know the exact field each user wnats to know has been updated. like in some cases it will be a completely newly made up entry and i will need to get it. this solution is a really good one though, and if i can't get the session thingy sorted out i think that is what i will go for.

does anyone else know about this idea of changing session variables that are other than your own?

thanks!