need tricky query to resolve set change

[sneakyimp]

i have two data tables (company, market) and one association table (company_market). As you can imagine, company_market allows me to create a many to many relationship between companies and markets. in practice, a company generally operates in 1-10 markets.

I have made a form that lets a user edit a company. This form shows the markets associated with a company in a multi-select input and they can add or remove markets to that multi-select using javascript. I have all the javascript worked out.

What I'm dealing with now is how to handle the queries. My Javascript doesn't keep track of which markets are added and which are removed - it just presents me with a list of the markets that should be associated with a company. It seems pretty easy to get rid of the markets that are assigned which have been removed:

$companyID = $_POST['companyID'];
$currentMarkets = $_POST['markets']; // something like Array(2, 5, 10, 16);
$query = "DELETE from company_market WHERE company_id=$companyID AND market_id NOT IN (" . implode(',', $currentMarkets) . ")";

What does not seem so easy is inserting new company_market records for the associations that don't exist yet. The approach I'm considering right now is to fetch all the records that are in $currentMarkets and put them in an array like $existingRecords and then loop through $currentMarkets, inserting a new record for every $marketID that's not in_array($marketID, $existingRecords)

If anyone can recommend a whiz-bang query I could use that would be most appreciated.

[tomhath]

One way is to put a unique index on the association table (which you probably should have anyway), then try to insert every row in $currentMarkets. The ones that are already there will return a constraint violation error, just ignore it.

That technique bothers some people, they want to avoid errors instead of letting them happen and handling them. But it doesn't break anything to have the DB throw the error. And it's going to be checking for duplicates on every insert anyway.

[Sxooter]

Another option is to build your queries with extra comparisons. For instance

update table set a=1, b=2 where a<>1 and b<>2

will only update that row if a isn't 1 and b isn't 2.

I use this with pgsql and with innodb which are both mvcc to prevent the creation of dead tuples.