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

Question

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

mysql_query() sends a unique query (multiple queries are not supported) - docs Also, as the docs suggest, please consider using a newer library such as MySQLi or PDO instead of the old MySQL extension. — Wiseguy
– Wiseguy, Commented Oct 1, 2012 at 18:57
By the way, mysql_* functions are deprecated (php.net/manual/en/function.mysql-query.php). You should either use mysqli_* or a PDO wrapper class — Chris Forrence
– Chris Forrence, Commented Oct 1, 2012 at 19:01
I know mysql_query() doesn't support multiple queries, but.... OH WAIT! For some reason I was thinking that the BEGIN/COMMIT thing would make that work. As suggested in a couple of the answers, I need to separate each query into its own mysql_query(). What a simple yet important oversight on my part. Thank you. — vertigoelectric
– vertigoelectric, Commented Oct 1, 2012 at 19:06
Additionally, I keep hearing that I need to use MySQLi or PDO, but in this case I'm working on a somewhat big existing project that uses mysql_ throughout and it's almost 'finished' so I'm not sure if it's worth it to go back and change everything. This is work related, so I'll have to run it by my boss to see if he wants me to do that. — vertigoelectric
– vertigoelectric, Commented Oct 1, 2012 at 19:08
@vertigoelectric Right on. Its use is highly discouraged for new development, but that's totally understandable if it's part of an existing project. You would only be absolutely required to update your MySQL extension usage if you're upgrading to a future version of PHP that does not have it anymore. — Wiseguy
– Wiseguy, Commented Oct 1, 2012 at 19:12

mdd061000 · Accepted Answer · 2012-10-01 18:58:27Z

7

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

answered Oct 1, 2012 at 18:58

mdd061000

3661 silver badge6 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

vertigoelectric Over a year ago

Lots of great answers and advice regarding my question, but I've selected this answer because this format is the first thing that has officially got this working for me. Does just what I need it to. Thank you. Apparently I had a brain fart and somehow didn't realize that my $query string was STILL multiple statements in one query, even though I already knew mysql_query() doesn't support that. Thanks again!

Majid Laissi · Accepted Answer · 2012-10-01 18:56:42Z

3

you need to use multi_query instead.

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

answered Oct 1, 2012 at 18:56

Majid Laissi

19.8k20 gold badges72 silver badges107 bronze badges

Comments

Simon Germain · Accepted Answer · 2012-10-01 19:01:17Z

2

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.

answered Oct 1, 2012 at 19:01

Simon Germain

6,8641 gold badge29 silver badges42 bronze badges

1 Comment

vertigoelectric Over a year ago

According to SQLYog, this table is InnoDB. However, I'm still having trouble after splitting the queries into multiple mysql_query() statements. I didn't get an error, but due to a typo in the 2nd INSERT query it inserted the first record but not the other one, which is what I'm trying to avoid.

Aviel Fedida · Accepted Answer · 2016-10-13 08:16:51Z

0

Try:

 $dbh->beginTransaction();
 $query = "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')";
 $dbh->exec($query);
 $dbh->commit();

Btw, Simon Germain got a good point, Transaction will work with tables using InnoDB engine.

edited Oct 13, 2016 at 8:16

answered Oct 1, 2012 at 19:08

Aviel Fedida

4,1129 gold badges59 silver badges90 bronze badges

Collectives™ on Stack Overflow

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

4 Answers 4

1 Comment

Comments

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

1 Comment

Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related