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.