CODEWITHSUNDEEP
phpmysqlatomic
Anonymous1
Anonymous1

Reputation: 3907

Multiple commands atomically

I'm using PHP and MySQL to call a couple of commands. There is a group-employee cross-reference table, and employees may be added or removed from the group. I'm writing this feature by first removing all the employees and then adding back in each employee that is in the table if the group is modified. But I want to make sure that all the commands go through or else have the table roll back. How can I make sure the commands happen atomically?

Upvotes: 0

Views: 138

Answers (3)

bob-the-destroyer
bob-the-destroyer

Reputation: 3154

Assuming your target tables support transactions, then you want the mysqli transaction family of methods.

See:

If your tables do not support transactions, you could likely change your table types to 'InnoDB` without a problem, and thus support transactions. Check with your DB admin before doing so first.

Upvotes: 1

webbiedave
webbiedave

Reputation: 48897

If you're using the MyIsam engine instead of InnoDB, then you can't take advantage of transactions. In that case, your best bet is to perform all your INSERTS firsts (your cross-ref table should have a two-field unique constraint so dups will silently fail). Then perform a single DELETE:

DELETE FROM employees_groups 
WHERE group_id = <groupid> 
AND employee_id NOT IN (list of employee ids you just inserted)

Upvotes: 1

Jason Benson
Jason Benson

Reputation: 3399

You're looking for Transactions unless I'm mistaken.

Here's a great place to start: http://dev.mysql.com/doc/refman/5.0/en/sql-syntax-transactions.html

Upvotes: 3

Related Questions