excute all queries else roll back php/mysql

[niladhar8@gmail.com]

i had a question in mind like how the banking roll back works.... Lets say i am executing 4 queries one after the other, how can i do it in a way that either all queries get executed or none..... do i automatically have the functionality to rollback the changes made by query 1 and 2 incase 3 and 4 were not execute?

[PeejAvery]

There's nothing directly built into MySQL. But, here's what I'd do...it requires using a primary key. But, every table you create should have some sort of key for faster indexing!


Before executing the queries, create a temp table.
As you perform each query, insert the backup data into the temp table.
If a query errors out, update each of the rows using the temp table.


CREATE TEMPORARY TABLE tmp_tbl LIKE test_tbl;
INSERT INTO tmp_tbl SELECT * FROM test_tbl WHERE id = 4;
DELETE FROM test_tbl WHERE id = 4;
INSERT INTO test_tbl SELECT * FROM tmp_tbl WHERE id = 4;