PHP：SQL 准备语句事务无法正常工作。它插入 1 个 SQL 语句，而不是同时插入两个 SQL 语句
我发现 PDO 事务仅提交了 2 个 SQL 语句中的 1 个。由于某种原因，我的 PHP 脚本没有插入到我的 MySQL 数据库 'homes' 表中，但它确实插入到 'invoices' 表中 - 即使我使用的是PHP PDO 数据库事务。
下面的代码：
$conn_str = DB . ':host=' . DB_HOST . ';dbname=' . DB_NAME;
$dbh = new PDO($conn_str, DB_USERNAME, DB_PASSWORD);
/* Begin a transaction, turning off autocommit */
$dbh->beginTransaction();
$sql_create_home_listing = 'INSERT INTO homes ( customer_id,
account_type_id,
address,
city,
state,
zip,
display_status
) VALUES (?,?,?,?,?,?,true)';
$stmt = $dbh->prepare($sql_create_home_listing);
$stmt->bindParam(1, $customer_id);
$stmt->bindParam(2, $account_type_id);
$stmt->bindParam(3, $_SESSION['street']);
$stmt->bindParam(4, $_SESSION['city']);
$stmt->bindParam(5, $_SESSION['state']);
$stmt->bindParam(6, $_SESSION['zip']);
$stmt->execute();
$home_id = $dbh->lastInsertId();
// another SQL statement
$sql_create_invoice = "INSERT INTO invoices (customer_id, account_type_id, price, cc_authorized, home_id) VALUES (?,?,?,?,?)";
$cc_authorized = false;
$anotherStmt = $dbh->prepare($sql_create_invoice);
$anotherStmt->bindParam(1, $customer_id);
$anotherStmt->bindParam(2, $account_type_id);
$anotherStmt->bindParam(3, $account_plan_price);
$anotherStmt->bindParam(4, $cc_authorized);
$anotherStmt->bindParam(5, $home_id);
$anotherStmt->execute();
/* Commit the changes */
$dbh->commit();
为什么只有“invoices”表获得插入，而不是“invoices”表和“homes”表获得插入>' 表？
注意：PHP 不会报告任何错误。
I'm finding that the PDO Transaction is only commiting 1 of my 2 SQL statement. For some reason, my PHP script is not inserting into my MySQL database 'homes' table BUT it does insert into the 'invoices' table - even though I'm using a PHP PDO database transaction.
Code below:
$conn_str = DB . ':host=' . DB_HOST . ';dbname=' . DB_NAME;
$dbh = new PDO($conn_str, DB_USERNAME, DB_PASSWORD);
/* Begin a transaction, turning off autocommit */
$dbh->beginTransaction();
$sql_create_home_listing = 'INSERT INTO homes ( customer_id,
account_type_id,
address,
city,
state,
zip,
display_status
) VALUES (?,?,?,?,?,?,true)';
$stmt = $dbh->prepare($sql_create_home_listing);
$stmt->bindParam(1, $customer_id);
$stmt->bindParam(2, $account_type_id);
$stmt->bindParam(3, $_SESSION['street']);
$stmt->bindParam(4, $_SESSION['city']);
$stmt->bindParam(5, $_SESSION['state']);
$stmt->bindParam(6, $_SESSION['zip']);
$stmt->execute();
$home_id = $dbh->lastInsertId();
// another SQL statement
$sql_create_invoice = "INSERT INTO invoices (customer_id, account_type_id, price, cc_authorized, home_id) VALUES (?,?,?,?,?)";
$cc_authorized = false;
$anotherStmt = $dbh->prepare($sql_create_invoice);
$anotherStmt->bindParam(1, $customer_id);
$anotherStmt->bindParam(2, $account_type_id);
$anotherStmt->bindParam(3, $account_plan_price);
$anotherStmt->bindParam(4, $cc_authorized);
$anotherStmt->bindParam(5, $home_id);
$anotherStmt->execute();
/* Commit the changes */
$dbh->commit();
How is it possible that only the 'invoices' table is getting the insert and not both the 'invoices' table AND the 'homes' table?
Note: no errors are reported by PHP.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（4）
首先，检查 PHP 中是否真的有任何错误 - 众所周知，它告诉你的错误是垃圾。您可以在 PDO 对象上设置一个选项，以在数据库错误时引发异常 - 我建议您设置它。
在我看来，它正在插入行，但您处于一个从未提交的事务中，因此它会回滚，并且该行永远不可见（您的隔离模式为 READ_COMMITTED 或更高）。
在这种情况下，您需要重新检查应用程序如何使用事务，并尝试查看是否可以使其保持适当的一致性。使用事务并不简单；它要么需要大量代码才能使事情正确，要么需要一些经过深思熟虑的包装代码或其他东西。如果您不明白其中任何一个，请保留自动提交。
Firstly, check whether you really have any errors in PHP - it's notoriously crap at telling you. There is an option you can set on PDO objects to throw an exception on database error - I recommend you set it.
It sounds to me like it is inserting the row, but you're in a transaction which is never committed so it gets rolled back, and the row is never visible (Your isolation mode is READ_COMMITTED or higher).
In that case you need to re-examine how your application uses transactions and try to see if you can get it properly consistent. Using transactions is nontrivial; it needs either a lot of code to get things right, or some well thought out wrapper code or something. If you don't understand any of that, leave autocommit on.
检查您的表是否是事务性的（以 InnoDB 与 MyISAM 为例......）。
可能需要尝试捕获，以便在出现错误时可以回滚。这可能会给您一些见解。
Check that your tables are transactional (InnoDB vs MyISAM as an example..).
Might want to do a try catch, so that if there is an error you can rollback. This may give you some insight.
true 是 display_status 的有效值吗？
MySQL 没有 bool 类型，也没有 true 作为预定义函数或常量。所以我的猜测是这是一个语法错误。
获得一些不错的错误处理。设置出现 SQL 错误时抛出的选项（请参阅 PDO 文档！）
Is true a valid value for display_status ?
MySQL does not have a bool type, nor does it have true as a predefined function or constant. So my guess is that it's a syntax error.
Get some decent error handling. Set the option which throws when there's a SQL error (See PDO docs!)
我发现了问题。在我家的桌子上，我有一个被认为是“唯一”的字段，但它不是，并且阻止了插入的发生
I found the problem. On my homes table I had a field deemed as 'unique' but it was not and preventing the insert from happening