CODEWITHSUNDEEP
phpmysqltransactions
anon
anon

Reputation:

mysql: transaction? insert and update

I need to do 2 query.

Basically it's a 

mysql_query("INSERT INTO table ($value1,$value2)");

and

mysql_query("UPDATE table2 SET field1 = '$value1', field2 = '$value2'");

I think I can simply do a

if (mysql_query("INSERT ...") !== false) {
   mysql_query("UPDATE ...");
}

In this case should I use a transaction? And how should I use it?
Or can i leave that simple if?

Thanks

Upvotes: 0

Views: 4128

Answers (2)

volkan
volkan

Reputation: 9

For insert and updates MySQL has a good, alternative solution - "ON DUPLICATE KEY UPDATE". It does what you want safely in a single query:

INSERT .... ON DUPLICATE KEY UPDATE

(also the key must be set as unique in this scenario)

http://dev.mysql.com/doc/refman/5.0/en/insert-on-duplicate.html

Upvotes: 0

Pascal MARTIN
Pascal MARTIN

Reputation: 401182

You will generally use transactions if you want some "all or nothing" behavior.

Basically, with transactions, you can :

  • Start a transaction
  • Do the first query
  • If it succeeds, do the second query
    • If it succeed, commit the transaction
    • Else, rollback the transaction -- cancelling both queries that correspond to that transaction.


If working with mysql_* function, you'll have to :

  • Start the transaction, with a START TRANSACTION query
  • Do your queries
  • Depending on the result of those queries, either do a COMMIT or a ROLLBACK query.

To detect whether a query succeeded or not, you can indeed check the return value of mysql_query() : it will return false in case of an error.

Note : MySQL is the old extension -- and doesn't have functions to deal with transactions ; which means you have to deal with them as regular queries.


Working with MySQLi, you could use :

Upvotes: 2

Related Questions