CODEWITHSUNDEEP
phpmysqldatabase
rostamiani
rostamiani

Reputation: 3245

Execute multiple queries at once in Mysql using PHP

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?

Upvotes: 3

Views: 2749

Answers (3)

Abhinav
Abhinav

Reputation: 8168

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

Upvotes: 5

Tomaso Albinoni
Tomaso Albinoni

Reputation: 1013

As Casimir et Hippolyte said: use transactions. And as Stephen said: don't use a deprecated API. Here's using the mysqli API (as an alternative to PDO):

$link = @mysqli_connect($host, $username, $password, $db_name);
@mysqli_begin_transaction($link);
$stmt = @mysqli_prepare($link, "UPDATE table1 SET col1=?, col2=?");
@mysqli_stmt_bind_param($stmt, 'ss', $someString, $someOtherString);
if (@mysqli_stmt_execute($stmt)) {
    $stmt2 = @mysqli_prepare($link, "UPDATE table2 SET col3=?, col4=?");
    @mysqli_stmt_bind_param($stmt2, 'id', $someInteger, $someDouble);
    if (@mysqli_stmt_execute($stmt2)) {
        @mysqli_commit($link);
    } else {
        @mysqli_rollback($link);
    }
} else {
    @mysqli_rollback($link);
}

Upvotes: 0

Stephen
Stephen

Reputation: 18917

First things first - stop using the mysql_* functions as they are deprecated in recent versions of PHP and will be removed in the next major release (v7)

I would suggest you learn about PDO and in particular for your problem at hand, PDO::beginTransaction, PDO::commit and PDO::rollback

Upvotes: 0

Related Questions