mysql undo查询或功能

Question

Well basically I'm inserting into a relational MySQL database using a MYISAM collation with php.

Lets say a user fills out a web form and posts it for insertion, all the data they provide may belong to parts of several tables.

So using php I'll insert the values into one table, then into another, then into another, etc etc...

But say I insert 3 lots of data into 3 tables, but on the 4th insertion... the sql fails... I then need to return an error message to the user, but ALSO I have to undo all the last inserts.

I could simply just delete all past inserts on fail...

However I wondered if there was an easier way??

Somehow providing the sql queries to mysql engine which temporarily stores the data and SQL, and on command, it runs through all the statements?

Any Ideas?

Answer 1

Start with:

mysql_query("start transaction");

Then, if all of your inserts work successfully:

mysql_query("commit");

Otherwise, if there is a failure somewhere...

mysql_query("rollback");

Done ^_^ I love this feature.

EDIT (following point made in a comment): This will only work in a database engine supporting transactions, and MyISAM is not one of them. I strongly recommend InnoDB, as it supports row-level locking, making your queries much less likely to encounter a lockup.