CODEWITHSUNDEEP
phpmysql
AlexMorley-Finch
AlexMorley-Finch

Reputation: 6955

mysql undo query or function

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?

Upvotes: 1

Views: 1878

Answers (4)

Black Cobra
Black Cobra

Reputation: 21

Friend, you can't use MYISAM storage engine if you want to do rollback after any fail steps... If you can use InnoDB as your mysql storage engine then i can answer your question that you asked here... Not only that I can say you what you have to write in php, for mysql and can share some sample codes that will clear you concept and it will be very easy to you then... But first you have to confirm me that can you change you table storage engine to InnoDB...?? if you want to change but don't know how can you change you storage engine then you should feel free to ask me... i will try my best...

If you want to ask me anything just edit this answer and add you question last of all... even you can add comments too... Good luck... friend...

Upvotes: 0

Mrugen Ramani
Mrugen Ramani

Reputation: 64

Try using SET AUTOCOMMIT=0 to disable autocommiting

Also realize, you must use a recent MySQL version which supports InnoDB tables

Notice that when doing that you need to always use commit to save changes...

START TRANSACTION;
UPDATE table SET summmary='whatever' WHERE type=1;
COMMIT;

PHP does support transactions, use seperate query statements for each command...

 mysql_query("BEGIN");
mysql_query("COMMIT");
mysql_query("ROLLBACK");

Upvotes: 1

Code Prank
Code Prank

Reputation: 4250

Hope this will help you:
http://www.icommunicate.co.uk/blog/-/myisam-transactions_20/

Upvotes: 1

Niet the Dark Absol
Niet the Dark Absol

Reputation: 324620

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.

Upvotes: 5

Related Questions