I'm in the process of creating an application where we are fed several external product feeds daily, and we populate our products database with the feeds. However the trick is we need to keep the product db in sync with the latest feed(s).
Previously I had toyed with the theory of populating the current product list from db in an array, and doing array comparison with the latest feed, that got gunned down once the product count was in the thousands. (Ran out of memory when trying to get a 5000 records).
So after abit of research, it seems the solution would probably lie on the SQL side, using TRIGGERS perhaps. Though I'm not quite sure how to go about it, hence my question.
So the 2 objectives I need to accomplish with the syncing process:
1) Insert new products that do not already exist in our db. We can accomplish this with the INSERT IGNORE method.
2) Find products on our DB that do NOT exist on the latest feed, and do something to them. (flag as deleted, or move to a deleted products table, etc.)
Step 2 is where I'm having trouble. I'm thinking now maybe for every sync operation, we insert the products from the latest feed into a 'Temp-Products' table, and somehow compare 'Products Table' with 'Temp-Products' table in finding the records that need to be flagged as deleted.
Any advice please?
Thanks
Obviously over-thought this one. The solution as suspected and further enforced by Anigel is to create a temporary table, 'products_temp' to store new feeds. We then run a simple join to find out what products are in the Products table, but not in 'products_temp', hence suggesting that the products have been sold out or deleted on the retailer.
We can then either flag the results of the query as deleted/sold out/do whatever.
The query I used is this:
SELECT products.sku_number, products_temp.sku_number FROM products LEFT OUTER JOIN products_temp ON products.`sku_number` = products_temp.`sku_number` WHERE products_temp.sku_number IS null