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) ...`](http://us1.php.net/manual/en/function.mysql-query.php) — Sean, Oct 12 '13 at 03:41
do 2 `mysql_query()` calls or update to mysqli and use `mysqli_multi_query()` http://php.net/manual/en/mysqli.multi-query.php — Sean, Oct 12 '13 at 03:43
yes you can check http://stackoverflow.com/questions/19330243/sql-syntax-error-when-there-is-none/19330268#19330268 — Shakti Patel, Oct 12 '13 at 03:43
@ShaktiPatel why are comment linking to your own answer below? — Sean, Oct 12 '13 at 03:47

score 3 · Accepted Answer · edited Oct 12 '13 at 03:47

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 '13 at 03:47

Amal Murali

73,160
18
123
143

answered Oct 12 '13 at 03:43

Shakti Patel

3,570
4
20
29

score 2 · Answer 2 · edited May 23 '17 at 12:33

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 '17 at 12:33

Community

1
1

answered Oct 12 '13 at 03:54

user2864740

57,407
13
129
202

Thank you for the alternative, but unfortunately the list also has 'CREATE TABLE' queries, which I didn't include here for simplicity. – Rohan Sood Oct 12 '13 at 03:58

SQL Syntax error, when executing several queries?

2 Answers2