SQL Syntax error, when executing several queries?

Question

I am using a readymade script to backup my MySQL database using PHP. I store the resultant query in a variable.

If I echo the variable, and copy paste the output into the MySQL console, it works perfectly.

But when I run the same using 'mysql_query' (I know it is depreciated, kindly ignore that), I get the dreaded Syntax error.

Here's the echo output (first 2 lines) :

INSERT INTO assign VALUES('75085','rsam','CE0001/CZ0001/CPE183/CSC183','1','1','3.0','13','1','1','13','2','10.00','117.00','0','0');INSERT INTO assign VALUES('75086','rsam','CE0001/CZ0001/CPE183/CSC183','1','2','3.0','13','1','1','13','2','10.00','97.50','0','0');

And here's the exact 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 assign VALUES('75085','rsam','CE0001/CZ0001/CPE183/CSC183','1','1'' at line 1

If anyone can point out what I am obviously missing, I would be grateful!

mysql_query() does not allow multiple queries. mysql_query() sends a unique query (multiple queries are not supported) ... — Sean
– Sean, Commented Oct 12, 2013 at 3:41
do 2 mysql_query() calls or update to mysqli and use mysqli_multi_query() php.net/manual/en/mysqli.multi-query.php — Sean
– Sean, Commented Oct 12, 2013 at 3:43
@ShaktiPatel why are comment linking to your own answer below? — Sean
– Sean, Commented Oct 12, 2013 at 3:47

Amal · Accepted Answer · 2013-10-12 03:47:24Z

3

As the documentation for mysql_query() says:

mysql_query() sends a unique query (multiple queries are not supported) to the currently active database on the server that's associated with the specified link_identifier.

You might be interested in mysql_multi_query():

Executes one or multiple queries which are concatenated by a semicolon.

edited Oct 12, 2013 at 3:47

Amal

76.8k18 gold badges133 silver badges154 bronze badges

answered Oct 12, 2013 at 3:43

Shakti Patel

3,8624 gold badges24 silver badges30 bronze badges

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

Comments

Community · Accepted Answer · 2017-05-23 12:33:02Z

2

While mysql_query is limited to a single statement, this situation can be avoided as multiple records can be inserted into the same table with only one statement:

INSERT INTO assign (...)
VALUES(...),
VALUES(...);

This will save on round-trip latency (over multiple mysql_query) which might matter.

See Inserting multiple rows in mysql

edited May 23, 2017 at 12:33

CommunityBot

11 silver badge

answered Oct 12, 2013 at 3:54

user2864740

62.5k15 gold badges159 silver badges234 bronze badges

1 Comment

Rohan Sood Over a year ago

Thank you for the alternative, but unfortunately the list also has 'CREATE TABLE' queries, which I didn't include here for simplicity.

Collectives™ on Stack Overflow

SQL Syntax error, when executing several queries?

2 Answers 2

Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related