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 re开发者_开发问答moving 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?

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

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)

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

See:

• mysqli->commit()
• mysqli->autocommit()
• mysqli->rollback()

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.