CODEWITHSUNDEEP
phpmysqlarrays
Binbash
Binbash

Reputation: 27

Using PHP to check for mysql DB changes

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.

Upvotes: 1

Views: 4675

Answers (3)

Allen H
Allen H

Reputation: 140

(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.

Upvotes: 0

TimFoolery
TimFoolery

Reputation: 1925

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.

Upvotes: 1

James C
James C

Reputation: 14159

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.

Upvotes: 4

Related Questions