PHP MYSQL Batch update

Can someone tell me why I cannot perform this simple script. The error I get is 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 '1update page SET RegionId = 1 WHERE (RegionId = -1 or RegionId = -3) and Descri' at line 1 If I copy this statement in to MySQLPHPAdmin it runs fine. Please help? Thanks in advance.

$sql = "update page SET RegionId = 2  WHERE (RegionId = -1 or RegionId = -3) and Description like '%Saskatoon%' LIMIT 1";
$sql = $sql . "update page SET RegionId = 1  WHERE (RegionId = -1 or RegionId = -3) and Description like '%Regina%' LIMIT 1";

echo $sql;

mysql_query($sql) or die( mysql_error());

The semicolon in your query is being removed and the LIMIT 1 is clashing with the beginning of the second update query.

From the PHP.net mysql_query manual page: 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 will need to use 2 calls to mysql_query to get the desired result.

You're concatenating those two $sql strings, so the final $sql string will have a part that looks like:

LIMIT 1update page SET

which is invalid SQL. In addition, mysql_query doesn't support multiple queries (thanks zerkms), so you'll have to execute each one separately.

$sql = "UPDATE page SET RegionId = 2 WHERE (RegionId = -1 OR RegionId = -3) AND Description LIKE '%Saskatoon%' LIMIT 1"
mysql_query($sql) or die( mysql_error());
$sql = "UPDATE page SET RegionId = 1 WHERE (RegionId = -1 OR RegionId = -3) AND Description LIKE '%Regina%' LIMIT 1";
mysql_query($sql) or die( mysql_error());

If 1update page set … isn't a typo, then this is the cause of the error. Try to find out where those one is coming from.

As said in other answers, mysql_query() does not support multiple queries, if you want to optimize this into one query you may have to re-think the way the query works.

if you are willing to give up the Limit 1 restriction you could do it this way:

$sql = "update page 
    SET RegionId = 2  WHERE (RegionId = -1 or RegionId = -3) and Description like '%Saskatoon%',
    SET RegionId = 1  WHERE (RegionId = -1 or RegionId = -3) and Description like '%Regina%'";

Because you are using like's on strings as your final conditional this could update many records, i would suggest re-designing to use ID's so you could engineer your query with better restrictions, for example:

$sql = "update page 
    SET RegionId = 2  WHERE (`RegionId` = -1 or `RegionId` = -3) and `user` = '1',
    SET RegionId = 1  WHERE (`RegionId` = -1 or `RegionId` = -3) and `user` = '2'";