INSERT on duplicate key update question

mysql

[belfasttim]

Hi -

I have a database of products with a couple thousand rows. I get a feed of current available products from a supplier via a custom API, accessed through php and updated several times a day.

I have no problem doing most of the stuff I need to-- my question is this:

I've built a PHP import routine that grabs each row returned, checks if it's been updated since the last insert job was run, and if so, does an INSERT ON DUPLICATE KEY UPDATE (since some rows will be new, thus having a more recent timestamp, and some will be older rows that were modified).

Is there a way to check the value of certain items (say, price) and note if it's been reduced from the existing row to the new row, then setting a "reduced" flag in the db?

I can think of several ways to handle this, one being build a duplicate products table, inserting all rows into it, then running a stored procedure to check price difference between the two rows where there's duplicate IDs. Then, as I said you could set a flag to say reduced or whatever.

I'm hoping there's a tried and true, elegant solution to this issue, since it's gotta be a really common one.

Thanks in advance for any help.

[maximillianos]

An easy solution that comes to mind... why not have this "reduced" field be a signed int field. Then simply have it loaded with the result of subtracting the new cost from the existing cost before over-writing it.

If the value stored in the field is positive, you know their was a price reduction, if negative, a price increase, and if zero then no change.

[belfasttim]

that's a pretty clever idea-- I'll give it a try.

thanks maximillianos!