What's your approach of updating rows coming from a checkbox?

[bugzy]

Newbie question guys..

 

Just want to know the best approach on this before I code it.

 

I have this merge table called "item_category" an item could have multiple rows base on how many categories the user chose.

 

I have this on my mind...

 

Delete all the item's rows(using item_ID PK)

and then insert it again with the new one.

 

 

So there's no update sql that will happen, only delete and insert...

 

What do you think guys?

[DavidAM]

I usually avoid the DELETE and INSERT approach for several reasons, most of which are just personal preference. If I have a group of checkboxes, such as categories, I name them so it produces an array of the category IDs in the POST array: <INPUT type="checkbox" name="chkCategory[]" value="$CategoryID" ... then I use the following approach -- remember, un-checked checkboxes will not be posted:

 

$selectedIDs = $_POST['chkCategory'];
# Do the sanitation (make sure they are integers)

# Delete any existing entries that are not now selected
$query = "DELETE FROM ItemCategories WHERE ItemID = $itemID AND CategoryID NOT IN (" . implode(',', $selectedIDs) . ")";

# Insert any selected entries that are not already there
$query = "INSERT INTO ItemCategories (ItemID, CategoryID)
SELECT $itemID, CategoryID FROM Categories 
WHERE CategoryID IN (" . implode(',', $selectedIDs) . ")
AND NOT EXISTS (SELECT * FROM ItemCategories WHERE ItemID = $itemID AND CategoryID IN (" . implode(',', $selectedIDs) . ") )";

The subquery is necessary since mySql will not allow you to JOIN to the table you are inserting.

 

Note: You can avoid the subquery if you have a UNIQUE INDEX on (in this case) ItemID,CategoryID by using INSERT IGNORE

 

My reasons for this approach (in no particular order):

• Avoids unnecessary holes in the AUTO INCREMENT value in the table (if any);
  
• Avoids "wasting" AUTO INCREMENT values (if any) so I don't have to worry about running out;
  
• Does not fragment the table and indexes as bad as DELETE and INSERT;
  
• If I have a CreateTime column (and I usually do), we are not loosing that data;
  

 

Disclaimer: I'm not 100% sure of that syntax, I'm not at home where I can check my code base. But I think you can see the logic I use.

[bugzy]

Thanks for the suggestion DavidAM

 

I'll definitely look into that. 

[Christian F.]

I'm really interested in the reason why you don't want to run an UPDATE query on the database? That is the real question, at least in my mind.

 

Depending upon your reasoning for this decision the answer you got is either the correct one, or this whole thread is a red herring. Which is why I'm wondering.