saving mySql row checkpoint in table ?

Question

hello,

I am having a wee problem, and I am sure there is a more convenient/simpler way to achieve the solution, but all searches are throw in up a blanks at the moment !

I have a mysql db that is regularly updated by php page [ via a cron job ] this adds or deletes entries as appropriate.

My issue is that I also need to check if any details [ie the phone number or similar] for the entry have changed, but doing this at every call is not possible [ not only does is seem to me to be overkill, but I am restricted by a 3rd party api call limit] Plus this is not critical info.

So I was thinking it might be best to just check one entry per page call, and iterate through the rows/entires with each successive page call.

What would be the best way of doing this, ie keeping track of which entry/row in the table that the should be checked next?

I have 2 ideas of how to implement this:

1 ) The id of current row could be save to a file on the server [ surely not the best way]

2) an extra boolean field [check] is add to the table, set to True on the first entry and false to all other. Then on each page call it; finds 'where check = TRUE' runs the update check on this row, 'set check = FALSE' 'set [the next row] check = TRUE'

Si this the best way to do this, or does anyone have any better sugestion ?

thanks in advance !

.k

PS sorry about the title

Answer 1

Not sure if this is a good solution, but if I have to make nightly massive updates, I'll write the updates to a new blank table, then do a SQL select to join the tables and tell me where they are different, then do another SQL UPDATE like

UPDATE table, temptable
SET table.col1=temptable.col1, table.col2=temptable.col2 ......
WHERE table.id = temptable.id;

Answer 2

Off topic, please disregard my previous answer.

Answer 3

You can store the timestamp that a row is updated implicitly using ON UPDATE CURRENT_TIMESTAMP [http://dev.mysql.com/doc/refman/5.0/en/timestamp.html] or explicitly in your update SQL. Then all you need to do is select the row(s) with the lowest timestamp (using ORDER BY and LIMIT) and you have the next row to process. So long as you ensure that the timestamp is updated each time.

e.g. Say you used the field last_polled_on TIMESTAMP to store the time you polled a row. Your insert looks like:

INSERT INTO table (..., last_polled_on) VALUES (..., NOW());

Your update looks like:

UPDATE table SET ..., last_polled_on = NOW() WHERE ...;

And your select for the next row to poll looks like:

SELECT ... FROM table ORDER BY last_polled_on LIMIT 1;