使用PHP在Mysql中一次执行多个查询

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

我想将一个大注册表单的信息插入到Mysql数据库中。我使用多个表并使用多个mysql_query命令。

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?

我想改变所有表格或改变一切！我怎样才能做到这一点？

3 个解决方案

#1

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

您正在寻找的是TRANSACTIONS，假设您没有使用MyISAM，因为它不支持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的基本示例

mysqli_query("START TRANSACTION");

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

if ($query1 and $query2) {
   mysqli_query("COMMIT"); //Commits the current transaction
} else {        
   mysqli_query("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.

注意：这只是一个简单的例子。如果你使用try和catch块实现正确处理异常，那就更好了。

Take a look at PHP DOCS

看看PHP DOCS

#2

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)

首先要做的事情 - 停止使用mysql_ *函数，因为它们在最新版本的PHP中已被弃用，并将在下一个主要版本中删除（v7）

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

我建议你学习PDO，特别是你手头的问题，PDO :: beginTransaction，PDO :: commit和PDO :: rollback

#3

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):

正如Casimir et Hippolyte所说：使用交易。正如斯蒂芬所说：不要使用已弃用的API。这里使用mysqli API（作为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);
}

#1