MySQL Transaction Syntax Error

Question

The following is throwing a syntax error. The entire query works in a MySQL client with literals, but breaks down when passing from PHP. In PHP, each query works if submitted separately, but not if within START and COMMIT.

The error:

1064: You have an error in your SQL syntax; check the manual ...etc...right
syntax to use near 'INSERT INTO user_degrees (user_id, degree_id, acadcat_id
, inst_id) VALUES (' at line 2

Query:

$query="START TRANSACTION;

INSERT INTO user_degrees (user_id, degree_id, acadcat_id, inst_id) 
VALUES ((SELECT id FROM users WHERE users.username = '$user')
   , '$degreeid', '$studyfocus', '$institution');

UPDATE users 
SET degree".$dnum." = (SELECT LAST_INSERT_ID())
WHERE username = '$user';

COMMIT;";

All the $vars are class properties and pass integers, except for $user, which passes a username session variable. $dnum is used to change column names between instances of the class and I might be concatenating it incorrectly within MySQL.

Marc B · Accepted Answer · 2011-11-15 19:14:23Z

4

PHP's mysql driver only allows a single query per mysql_query() call as a security measure. You'll have to issue multiple separate queries:

$result = mysql_query("START TRANSACTION");
$result = mysql_query("INSERT ...");
$result = mysql_query("UPDATE ...");
$result = mysql_query("COMMIT;");

... with appropriate checking at each stage to make sure the query didn't fail (which I've omitted from here).

Note that this security measure only applies to top-level queries. One one top-level query per call. You can have as many subqueries as you want/need.

answered Nov 15, 2011 at 19:14

Marc B

362k44 gold badges433 silver badges508 bronze badges

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

Collectives™ on Stack Overflow

MySQL Transaction Syntax Error

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related