I am building a new functionality and have an idea how to do it, however there might be a better way out there, which I have not though of. So any 'crazy' ideas are welcomed.
This is the case:
I have a products table that contains 9 different types of products. Let's call it tbl_all_products. This table contain about 100K records and is regularly being updated - either by the users (they can add/remove products) or by automated scripts (like delete outdated ones). So understandably, there will be holes in the table ids and a lot of variations in the product types order.
Now what I want to do is to get a new cron in place which will extract products from tbl_all_products only WHERE prod_id = '1', query might be something like:
$sql_product = "SELECT id,name,date,user_id
FROM tbl_all_products
WHERE type = '1';
Then I want to update those 4 columns for all matching products in a new table, let's call it tbl_type1.
I am planning to to record the count of selected products with type=1 and then update those with a loop in tbl_type1 WHERE id=$count_id.
One question leaves for me to think about if I choose this approach and it is what to do with the excessive rows in tbl_type1 when there are less products found in the original table.
But I would like to get some other opinions if there is a better and more importantly, a faster way to do it.
Any suggestions or thoughts?
