Using PHP to check for mysql DB changes

Question

I'm creating a new web application and I only want to update certain tables when the db is changed, this will be done by firing off a PHP script in a JavaScript setInterval.

My issue is that I am having a hard time trying to find a way to easily determine if certain rows in the database table have changed. In other instances I have used "show table status" and compared that against the last update_time that I had already stored.

In this case though, I don't want to look at the entire table, I just need to know if the rows in my query have changed. I originally tried to md5 the array, as well as md5 on the serialization of the array. It seems that neither of them work and return the same md5 hash each time.

Just for example, here is my query:

$getUserTable = mysql_query("select * from uct_admin.resources where teamID='62' order by ResourceName");

Solution that I came up with after reading the answers:

$getUserTable = mysql_query("select * from uct_admin.resources where teamID='62' order by csiname");
while ($row = mysql_fetch_assoc($getUserTable)) {
    $arrayTest[] = $row;
}
$hash = md5(http_build_query($arrayTest, 'flags_'));

This works perfectly due to the nested array returned from the query.

I'm not sure I understand your use case completely, but have you thought about a logging table? — Kevin Peno
– Kevin Peno, Commented May 16, 2011 at 22:16

James C · Accepted Answer · 2011-05-16 22:16:12Z

4

I would consider adding an additional TIMESTAMP field to the table that's updated on UPDATE/INSERT then you could just check SELECT MAX(lastModified) WHERE teamID=62

If you don't want to change the table structure you could add a trigger to this table and have it keep track of the last change to each teams's data in a separate table.

answered May 16, 2011 at 22:16

James C

14.2k1 gold badge37 silver badges45 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

TimFoolery · Accepted Answer · 2011-05-16 22:35:18Z

1

I'd create a table to log all changes, called dbLogTable. ID (foreign key) and timeStamp would be its columns. Each time your PHP script runs, use truncate table dbLogTable in order to clear the table. Your PHP script could detect any changes by running COUNT(*) FROM dbLogTable, which would be zero if there were no changes and nonzero if there were changes. All changes could then be accessed via the foreign key, ID.

Disclaimer: I'm a noob when it comes to DB work, so while this may be how I would do it, it might not be the best way to do it.

answered May 16, 2011 at 22:35

TimFoolery

1,9251 gold badge19 silver badges29 bronze badges

Comments

Allen H · Accepted Answer · 2011-05-16 23:00:39Z

0

(I'd go with James' solution unless there's a specific reason you can't)

I did something similar and managed to get the hashing solution you alluded to working okay. It would be very strange to get the same hash from different data.

In my code I'm doing an implode on the array rather than a serialization. I then store the hash in the database and when I want to compare a new set of data against it I run the md5 on the new data and compare it to the hash.

If it still doesn't work can we maybe see your code for hashing and some sample data? I'd be curious to see what's up if this method doesn't work.

edited May 16, 2011 at 23:00

answered May 16, 2011 at 22:46

Allen H

1401 silver badge8 bronze badges

1 Comment

Binbash Over a year ago

While I like James' idea, in this case you sent me down the path to resolution. Basically once you get the array from the DB run the md5 on http_build_query($array, 'flags') and it works perfectly. This solution is much easier to implement in a short term basis and works quite well.

Collectives™ on Stack Overflow

Using PHP to check for mysql DB changes

3 Answers 3

Comments

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related