Insert, Update & Delete Checkbox Selections

[biggieuk]

Hi,

 

Looking for some help with this slight specific mysql query.

 

I have a table `services` which looks something like:

 

id

servicesid

articleid

1

5

10

2

7

20

3

5

16

 

This is used so that i can return all of the articles tagged to a specific service (using serviceid).

 

My ui has the articles selectable as checkboxes.

 

What is the best way to skip the rows that have not changed, delete the rows that have been deselected and insert new rows for newly selected checkboxes?

 

An 'ON DUPLICATE KEY' command will not work as there serviceid & articleid can be used more than once?

 

Thanks for help with this.

[biggieuk]

Im guessing my description wasn't too good.

 

I'm currently having to delete all records with the matching servicesid then input all the selected id's.

 

There must be a cleaner way than this?

 

I would normally try and perform an update and check if any rows were affected before inputting but this also requires a unique ID which I am not passing into the query.

[jdavidbakr]

When I have something like this, I query all the options, then loop through the results, either deleting the row if the checkbox doesn't exist or replace (or insert, which if you have constraints to prevent duplicates would prevent you from overriding the existing rows if there is data there that you don't want to have to recreate).

[DavidAM]

While servicesid can appear in the table more than once; and articleid can appear in the table more than once; I suspect that the combination of servicesid and articleid needs to be unique. You can create a unique index on these two fields (combined) - in addition to your existing primary key. Then INSERT IGNORE DUPLICATE KEY should work. It is my understanding that these phrases do not care which unique key is being duplicated.

 

On removing rows: If what you have is the articleid's that need to be left in the table you can

$list = implode(',', $_POST['articleIDs']);
$sql = "DELETE FROM services WHERE servicesid = 5 and articleid NOT IN ($list)";

or something along those lines