[Resolved] Is there a limit to the # of MySql UPDATE statements in a PHP query?

dcardena

Please, if you've ever run into anything like this before-- is this a PHP bug?

why does this work:

$query = "UPDATE data SET title = 'foo' WHERE id = '1'; ";
$result = mysql_query($query) or die('Query failed: ' . mysql_error());

but this fails, throwing a MySql syntax error:

$query = "UPDATE data SET title = 'foo' WHERE id = '1'; UPDATE data SET title = 'bar' WHERE id = '2'; ";
$result = mysql_query($query) or die('Query failed: ' . mysql_error());

the error is :
Query failed: 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 '; UPDATE data SET title = 'bar' WHERE id = '2'' at line 1

the same multi-statement query works fine in a shell, but fails in PHP

dcardena

yes there is- sorta. PHP doesn't like the MySql required semicolon in a mysql_query().

[MAN]mysql_query[/MAN]

Weedpacket

It's not PHP that has the problem - it's MySQL. As far as PHP is concerned, that query is just another string. It's MySQL that actually reads it. (Multiple SQL statements are not a part of SQL.)

You've got a program you can use from your machine to communicate with MySQL from the shell, and in that you can separate queries with semicolons, and so issue multiple queries at once. But the program you use has to separate what you type into individual queries in order to send them to MySQL.