update only changed items

cretaceous

I have a chunk of code I use to control entries that go into "relationship" tables .
The table's purpose is to record which people have what categories
- so it's one to many

data is input via dyanmic checkboxes on the page

right now, on submit, I delete all entries for that person, then do an INSERT of the submitted entries

it works, no problem

if($ref_id) {
        	$sql = "DELETE FROM customer_type_matches WHERE cust_id='$ref_id'";
        	$result=runsql($sql,'delete services','','');
//run sql function

        foreach ($serv_clicks as $key => $row)
        {
        $sql = "INSERT INTO customer_type_matches (cust_id,	cust_type_id) VALUES ('$ref_id','$row')";
        $result=runsql($sql,'update services','','');
//run sql function
	        }

	}

is there a better way of doing this?

I know I could make an array of current entries then update or insert based on live checkbox entries... but that would end up as more queries and more code

johanafm

cretaceous;10959376 wrote:
I know I could make an array of current entries then update or insert based on live checkbox entries... but that would end up as more queries and more code

Slightly more code, less queries than you have now. You run 1 delete query + 1 query per inserted row. Running no delete query, then 1 query per inserted or updated row is one query less.
If you are using mysql you do not even need to keep track of which rows are inserts and which are updates, since you can use "INSERT ... ON DUPLICATE KEY UPDATE ...". See mysql doc for syntax/info.
But anyway, for existing rows you have a primary key. For new rows, you don't, Assuming your PK is an auto_incremented value instead of real world data.

Shrike

If the list is very large it might be worth only deleting the records which are not in the new set, and then inserting any new entries. Probably not much in it in most situations though.

cretaceous

thanks - didn't know about ON DUPLICATE KEY UPDATE

relationship table could easily have a thousand or more entries but only around 6 possible 'types' for each user

maybe it's not such a big deal