PHP MYSQL Batch update

Question

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());

This exact code cannot throw such error

zerkms
– zerkms

2011-09-15 22:34:56 +00:00
Commented Sep 15, 2011 at 22:34 — zerkms
– zerkms, Commented Sep 15, 2011 at 22:34

drew010 · Accepted Answer · 2011-09-15 22:36:39Z

6

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.

answered Sep 15, 2011 at 22:36

drew010

70.3k11 gold badges144 silver badges174 bronze badges

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

Comments

Alex · Accepted Answer · 2011-09-15 22:35:10Z

3

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());

answered Sep 15, 2011 at 22:35

Alex

9,5011 gold badge41 silver badges45 bronze badges

2 Comments

zerkms Over a year ago

mysql_query() doesn't support several queries in a string

feeela Over a year ago

OK, you've found, where that one comes from, but: "mysql_query() sends a unique query (multiple queries are not supported)" php.net/manual/en/function.mysql-query.php So he needs to send both queries one after another…

feeela · Accepted Answer · 2011-09-15 22:34:46Z

0

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.

answered Sep 15, 2011 at 22:34

feeela

30k7 gold badges61 silver badges73 bronze badges

Comments

Adam Purdie · Accepted Answer · 2011-09-15 23:00:01Z

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'";

Collectives™ on Stack Overflow

PHP MYSQL Batch update

4 Answers 4

Comments

2 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

2 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related