Forum Moderators: coopster
table_items
- item_id
- item_name
table_categories
- category_id
- category_name
table_assoc_items_categories
- item_id
- category_id
Each item can have multiple categories. That's why I have the association table.
My question is how do you update the association table when user makes changes?
Right now, I would DELETE all rows where category_id = X, and then INSERT item_ids selected by the user and category_id into the table.
Is this the right way of doing it? It seems too extreme to have to DELETE and INSERT. Can I do it with UPDATE?
Thanks.
UPDATE table SET field='$val' WHERE id='$id';
I know you can update the values using the SQL you gave me, but let's say you are looking at this entries form:
------------------------------
Item ID: itemA
Select all applicable categories for this item:
[] Category1
[] Category2
[] Category3
[] Category4
[] Category5
[Submit]
------------------------------
If you selected, say, Category2 and Category4 for this item for the very FIRST time, I could use an INSERT INTO to insert the values into the association table.
So the table would look like this:
table_assoc_items_categories
item_id ¦ category_id
-----------------------
itemA ¦ Category2
itemA ¦ Category4
On the next day, you want to change the category. Instead of Category2, you want Category3. You also want to add Category5. So you go to the entries form, uncheck Category2 and check Category3 and Category5 and hit Submit.
How do you do that in SQL the easiest way?
Because the entries form data is sent by the browser indicating what was checked, not what was unchecked, you only see Category3 and Category5 that you selected.
So my question is, how can I put these new Category3 and Category5 into the table?
If I used "UPDATE assoc_table SET category = 'Category3' WHERE item = 'itemA'", I would change every category with item = 'itemA' to Category3, not just one. This is certainly wrong.
The UPDATE call also assumes that there's a record in the table already. What if the table is still empty? So you have to make another query for each record to find out.
If I used INSERT, what if the same values already exist in the table? Then you have to make another query to check. Also what would happen with the category that you unchecked?
So my current solution is that I delete all records with item = 'itemA', and then do INSERT for all categories selected.
But I don't know if this is the right away because deleting records seems too scary for me. is there another simple way that doesn't require DELETE?
I hope I asked the question right this time :)
that is what I would do, was thinking that would be my response as I was reading through.
a simpler way? not really, it is a lot more work any other way, you would need to check each one, count number of entries already, see if that number matches the entries you have, then update/insert accordingly for each change.
dump all for that item
add all new ones
I agree that deleting is scary but you are only deleting associations for a single product at a time. The worst scenario (assuming uniqueness of ids and soundeness of queries) is that a single product becomes unassociated with any cat.
Thanks again for your assurance.
