PHP/MySQL - “开始...提交"不工作

2022-01-09 00:00:00 insert transactions php mysql

我正在寻找一种在单个查询中将数据插入两个数据库表的方法，这样如果一个失败，则不会保存(我不想要孤立的数据).我遇到了一个 Stack Overflow 问题，该问题向我展示了如何使用 BEGIN...COMMIT 来完成此操作，但它根本不起作用.

I was searching for a way to insert data into two database tables in a single query in such a way that if one failed, neither saved (I don't want orphaned data). I came across a Stack Overflow question that showed me how to use BEGIN...COMMIT to accomplish this, but it simply is not working.

这是我设置的查询:

$query = "BEGIN;
            INSERT INTO content_subpages (title, url_referer) VALUES ('$pagetitle','$url_referer');
            INSERT INTO ccm_main_menu (sub_item, sub_item_link,sub_item_sort_order) VALUES ('$pagetitle','$url_referer','$newsort');
            COMMIT;";
mysql_query($query) or die (mysql_error());

我收到以下错误:您的 SQL 语法有错误；检查与您的 MySQL 服务器版本相对应的手册，以获取在 'INSERT INTO content_subpages (title, url_referer) VALUES ('TESTING','testing') 附近使用的正确语法；' 在第 2 行

这是我第一次使用 BEGIN...COMMIT，所以我可能做错了什么是合理的，但我遵循了我提到的 Stack Overflow 问题的选定答案给出的 SQL Fiddle 示例的语法(http://stackoverflow.com/questions/12649706/mysql-insert-into-multiple-tables-in-same-query-with-begincommit)，但它仍然无法正常工作.

This is my first time using BEGIN...COMMIT, so it's reasonable that I might be doing something wrong, but I followed the syntax of the SQL Fiddle example given by the selected answer to the Stack Overflow question I mentioned (http://stackoverflow.com/questions/12649706/mysql-insert-into-multiple-tables-in-same-query-with-begincommit), but it still won't work.

如果我可以在没有 BEGIN...COMMIT 的情况下轻松实现全有或全无"多重 INSERT 结果，那将是一个可以接受的解决方案.

If I can easily achieve the "all-or-nothing" multiple INSERT result without BEGIN...COMMIT, that would be an acceptable solution.

提前致谢

推荐答案

尝试将这些行分成多个 php 语句:

Try breaking the lines into multiple php statements:

$query = "BEGIN";
mysql_query($query) or die (mysql_error());
$query = "INSERT INTO content_subpages (title, url_referer) VALUES ('$pagetitle','$url_referer')";
mysql_query($query) or die (mysql_error());

$query = "INSERT INTO ccm_main_menu (sub_item, sub_item_link,sub_item_sort_order) VALUES ('$pagetitle','$url_referer','$newsort')";
mysql_query($query) or die (mysql_error())

$query = "COMMIT";
mysql_query($query) or die (mysql_error());

相关文章