Multiple Checkboxes Updating Table

[freakystreak]

I have a table for products and another for categories. I'm using a lookup table of product_id and category_id to link the products to categories. So in all 3 tables in my database. In my admin side I have a page that shows all the products and then a link to another page to assign categories to that product. Ideally I would like a list of categories with a checkbox for each that I can select or deselect and then once the submit button is pressed the form updates the lookup table to reflect my selections.

I have got a query to get a list of categories and then I would think I need in some way to go through each one using the lookup table as reference so that any products that match are then already checked. Can anyone help in getting me on the road to doing this?

Single checkbox forms I have no problem with but I need it to be multiple checkboxes so that a category can be added or removed from the product. Cheers

[StarLion]

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.

[guido2004]

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..

More efficient I don't know, but easier to code absolutely
Delete all product-category rows for the product present in the table, and then insert rows for all categories checked in the form (creating the query dynamically as StarLion said). That way there is no need to confront the new situation with the old one, and delete only those categories that have been unchecked, and add only those categories that aren't present in the product-category table yet.

[captainccs]

Unless there are a very large number of categories it would seem quite efficient and easier to code to delete and rebuild the link (lookup) table.

I would approach it a bit dfferently. In pseudo code.

Form:

// the array will be used to to build checkboxes (or drop down menus)
Get all category names and ids from category table
while category {
    Save in associative array: key=category-id, value=category
}

Get all products
while product {
    get product category link-list
    while category {
        create numeric array
    }
    // for each category
    foreach category {
        build checkboxes (or drop down menus)
        using the category array as the template
        and the product-category array as values (checked)
    }
    output product row
}
Output form buttons and close form

Form processing:

Create new table (link_new)
foreach product {
    foreach category {
        create sql query
    }
    write link records
}
delete old link file
rename "link_new" to "link"