php / mysql - updating issues

[raydenl]

Hi,

 

I have a custom shopping cart db that has a product table, category table and a producttocategory table.

 

The product table has a ProductID (auto_increment)

The category table has a CategoryID (auto_increment)

and the producttocategory table has a ProductID and a CategoryID, this is so products can appear in multiple categories. It also has a ProductToCategoryID (auto_increment)

 

I have also begun creating and Admin site, I have created the "Add a Product" page, this allows the user to select multiple categories from a list of available categories and when "Saved" updates the producttocategory table.

 

The problem I am having is with the "Edit a Product" page. When they edit a product they see the current list of categories the product currently belongs to, and they can "Add" or "Delete" any of these, but the problem I find is that to accurately know if a category has been added or deleted is to, everytime the "Save" button is clicked, delete all the entries for this product from the producttocategory table and re add them... most times a product is edited the categories wouldnt be changed, but I still have to perform this routine to make sure... so if a product is edited often I am rapidly going through ProductToCategoryID's... now I could just set the ProductToCategoryID data type to be int or larger, but this seems wasteful... my question is... IS THERE A BETTER WAY???

 

 

[btherl]

Why don't you check to see if a category was edited, and only replace the producttocategory entries if there was a change?

 

Better still, you can only replace affected producttocategory entries.  It is all quite possible to do exactly.  If you want, post your code here and we may be able to help you with the SQL.