Well, the first step is going to be getting the RIGHT queries for what you’re looking to do; there are three (potentially four) you’re looking at doing here;
Tapping some query gurus to make sure i’m not leading you astray here… @guido2004,@r937, @ScallioXTX… this is how I would do it, but i’m sure these guys can do it better (Is it more efficient to delete all and then add all? I still make it the same number of queries, but the amount of rows added/deleted is different…).
Queries:
1: Initial lookup for a given product. Used for form presentation.
SELECT categories.cat_name,categories.cat_id,catprods.product_id FROM categories LEFT JOIN catprods ON catprods.product_id = $yourprodid
2: (Optional; can be handled by a hidden POST variable instead, or by tweaking the third query to ignore errors)
Same initial lookup. Used for form handling to trim down the list of categories that need to be added to the catprods table.
3: Insert query to add new rows to catprods table. Note that this should be dynamically built.
INSERT INTO catprods(cat_id,prod_id) VALUES (20,1), (13,1), (1000,1);
4: Delete query to remove no-longer-needed rows to catprods table. This should also be dynamically built
DELETE FROM catprods WHERE product_id = 1 AND cat_id IN(16,19,21);
With those three (or four) queries, you can write out the form and form handler.
Form:
Run query 1.
Foreach row in query 1 results;
Echo Category name, followed by checkbox that has value of the category ID. If the value of prod_id is not NULL, check the checkbox.
If you chose to not do query #2 and want to go the POST-hidden method, here take the ID and stuff it into a hidden array.
Endforeach.
Submit Button.
(If you’re using POST, you probably want another field to pass the product_id to the form handler)
Form handler:
If you chose to do query #2:
Run query 2.
Foreach row in Query 2 results:
add id to an array (Calling it the “known” array).
Endforeach
If you chose to send a hidden form array
Your hidden form array is the “known” array.
In all cases:
Sanitize the known array and post array. (I’d do this anyway, even if you’re going to use prepared statements.) All entries in both arrays should be integers (unless you’re using some odd ID columns…)
Array_diff the Post array and the known array. You will be left with the rows that need to be added.
if there exists one or more records in the resultant array:
run query 3, imploding (or parsing into query-string and then Preparing) the resultant array in such a way that it creates the appropriate query format.
Array_diff the known array and the post array. You will be left with the rows that need to be deleted.
if there exists one or more records in the resultant array:
run query 4, imploding (or parsing into query-string and then Preparing) the resultant array in such a way that it creates the appropriate query format.