Execute multiple queries at once in Mysql using PHP

Question

I want to insert information of a big registration form into a Mysql database. I use multiple tables and use multiple mysql_query commands.

The problem is : If an error occur in one of queries, the process stops but previous queries have changes the tables!

I want to alter all tables at ones or alter nothing! How can I do this?

Answer 1

What you are looking for is TRANSACTIONS assuming you are not using MyISAM since it does not supports Transactions.

The concept of transactions is that either all the queries will execute or no query would execute at all.

In simple words all-or-nothing is what Transactions do

This is a basic example using mysqli

mysqli_query($conn, "START TRANSACTION");

$query1 = mysqli_query($conn, "INSERT INTO TABLE1(id) VALUES(2)");
$query2 = mysqli_query($conn, "INSERT INTO TABLE2(id) VALUES(3)");

if ($query1 and $query2) {
   mysqli_query($conn, "COMMIT"); //Commits the current transaction
} else {        
   mysqli_query($conn, "ROLLBACK");//Even if any one of the query fails, the changes will be undone
}

NOTE: This was just a simple example.It would better if you implement using try and catch blocks handling then exceptions properly.

Take a look at PHP DOCS