2

I have two MySQL queries in my PHP code.

Basically I need the two queries to execute (they are two INSERT) queries and both must be successful. If for example query1 is successful but query2 is not successful; I need to rollback query1 as both queries must be successful.

I am using MyISAM and I know I cannot do rollbacks etc but is there any way I can overcome my problem (another solution) to do what I want?

They both depend on each other as query1 stores data from form and query2 inserts some other details into another table (it's basically a counter) that tracks total counts for a referral system.

halfer
  • 19,824
  • 17
  • 99
  • 186
PHPLOVER
  • 7,047
  • 18
  • 37
  • 54
  • If you want ACID compliance, then you should use an ACID compliant storage engine (ie. InnoDB). There's no way around this. If your second query fails, then the removal of the first record could fail too... So there's no way to enforce this without actually using transactions. – wimvds Apr 19 '11 at 11:42

4 Answers4

3

One option would be to store the ID of the insterted record in the first table. If the second query fails, use a delete query to remove the record inserted by the first query. I would advice you to change the engine to InnoDB however, so you can use transactions. Or do you a specific reason to use MyISAM (like full text searches?)

user254875486
  • 11,190
  • 7
  • 36
  • 65
  • Hi, i could change both tables to InnoDB but i don't know and have never done rollbacks or anything so not sure what the syntax is like. Thanks PHPLOVER – PHPLOVER Apr 19 '11 at 11:36
  • 2
    There are plenty of tutorials on MySQL transactions, for example http://dev.mysql.com/doc/refman/5.0/en/commit.html I would strongly advice to do this, you will use it very often after you learn how to use it. – user254875486 Apr 19 '11 at 11:37
3

After the first insert, you can get the ID using mysql_insert_id() and store it.

Then when you do the second query, get that ID as well. If the ID is null, then it means it wasn't successful, and you've got the old ID so you can then delete it, like so

DELETE FROM table WHERE id='$insertId'

Edit:

Just a note, this isn't a complete solution, the only true way would be to convert to InnoDB

fin1te
  • 4,289
  • 1
  • 19
  • 16
  • 1
    And if the delete fails and data remains in the table linking the two entries? Then what happens? – Michael J.V. Apr 19 '11 at 11:44
  • There's not going to be a perfect solution unless the tables are converted to InnoDB – fin1te Apr 19 '11 at 11:46
  • Exactly, and that's why your solution is, at best, partial solution. Don't get me wrong, I just think you should point out all possible outcomes along with the answer so someone doesn't get the wrong idea. – Michael J.V. Apr 19 '11 at 11:58
  • Well, it's not limited to InnoDB (though it's the most obvious option), any transactional storage engine will do (InnoDB, NDB, Falcon, XtraDB, ...). – wimvds Apr 19 '11 at 12:04
1

Sounds to me like you need transactions.

http://www.techrepublic.com/article/implement-mysql-based-transactions-with-a-new-set-of-php-extensions/6085922

They allow you to rollback an entire series of database modifications within a single transaction.

maple_shaft
  • 10,435
  • 6
  • 46
  • 74
  • @Johan, I know what he has, I am stating the best tool that he should use. He could probably build a deck with just a hammer too but that doesn't make it right. – maple_shaft Apr 19 '11 at 11:58
  • 1
    Your suggestion is correct, however you did not specify that transactions only work with InnoDB, the OP knows that MyISAM cannot do transactions, so it is really not an answer to the question. It's like I'm asking `how do I catch a chicken` and you tell me to eat fruit instead because it does not run away. – Johan Apr 19 '11 at 12:08
  • Of course he needs transactions, but then he also will have to change the storage engine used because MyISAM is non-transactional... @Johan Transactions aren't limited to InnoDB, there are more transactional storage engines for MySQL out there (Falcon, NDB, XtraDB and maybe Aria in the future). – wimvds Apr 19 '11 at 12:08
  • Yes yes I know transactions work with lots of engines, just not MyISAM. Sorry for cutting corners in the comment. – Johan Apr 19 '11 at 12:29
  • @maple_shaft Thanks will look into transactions thanks for all the help everyone :) – PHPLOVER Apr 19 '11 at 15:23
1

Transactions are the answer. If your chosen storage engine does not support them, and you have this scenario, then you probably picked the wrong storage engine.

Lightness Races in Orbit
  • 378,754
  • 76
  • 643
  • 1,055