Compare 2 MySQL tables with PHP and display them along with differences

Question

Hello,

I am working on an inventory system.

Basically here is how I imagine it would work. If you have a better solution, please don't hesitate to let me know.

I get daily inventory update from my supplier in the following format: sku, inventory_count, status

sku is the unique identifier here. inventory_count is integer and status is normally, "in stock" or "out of stock" or discontinued.

My idea is that I will load up the file in a mysql table. I will name the mysql table with the current date so for instance 20101020 for Oct 20 and 20101021 for Oct 21

Basically the idea is to compare those 2 tables and link the two tables (I am guessing via innerjoin) through sku which is a unique identifier. It will then display the data from the temp tables which were merged together. If there are any differences in inventory, it will just show like a warning message.

Wanted to get some feedback and see if I am approaching this comparing with the right mindset. Also, wanted to get a rough idea on the php code needed to merge the tables and insert the data and then retrieve the merged data and echo it.

Thanks

Answer 1

http://www.mysqldiff.org/

If that what you want?

Answer 2

Creating a new though identical table on each update looks like a serious denormalization. If you absolutely need to keep a history of inventory data I see no other sensible way than adding a DATETIME column. Having that, you just need to perform an appropriate self JOIN on both date and sku.

However, I don't think I understand your situation. Do you your supplier and you keep parallel inventories? If that's the case, it looks like the right approach is to keep two tables (and you'd only need a date column in the supplier's data).