PHP/MySQL-"BEGIN ... COMMIT"；无法运作 [英] PHP/MySQL - "BEGIN...COMMIT" Not Working

问题描述

我正在寻找一种在单个查询中将数据插入到两个数据库表中的方式，如果一个失败，则不会保存(我不需要孤立的数据).我遇到了一个堆栈溢出问题，该问题向我展示了如何使用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());

我收到以下错误: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'INSERT INTO content_subpages (title, url_referer) VALUES ('TESTING','testing'); ' at line 2

这是我第一次使用BEGIN ... COMMIT，所以我可能做错了，这是合理的，但是我遵循了SQL Fiddle示例的语法，该语法是由我提到的Stack Overflow问题的选定答案给出的(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.

如果我可以轻松获得全有或全无"的多个INSERT结果而无需BEGIN ... COMMIT，那将是一个可以接受的解决方案.

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());

这篇关于PHP/MySQL-"BEGIN ... COMMIT"；无法运作的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！