Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

PHP/MySQL - "BEGIN...COMMIT" Not Working

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.

Here's the query I've set up:

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

I get the following 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

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.

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

Thanks in advance

like image 497
vertigoelectric Avatar asked Oct 01 '12 18:10

vertigoelectric


3 Answers

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());
like image 69
mdd061000 Avatar answered Oct 20 '22 01:10

mdd061000


you need to use multi_query instead.

http://php.net/manual/en/mysqli.multi-query.php

like image 29
Majid Laissi Avatar answered Oct 20 '22 01:10

Majid Laissi


Make sure you're using an InnoDB table and not a MyISAM table, as MyISAM is not transactional.

To use an InnoDB table, when you create it, after the closing paren, add ENGINE=InnoDB. By default, MyISAM is used.

like image 30
Simon Germain Avatar answered Oct 20 '22 00:10

Simon Germain