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?
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
mysqli_query() requires a connection here. php.net/manual/en/mysqli.query.php - Not sure why this was accepted as posted.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
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);
}
@ in front of all your calls is a bad coding practice. None of these should fail but you need to know when they do
