CODEWITHSUNDEEP
phpmysql
user2368299
user2368299

Reputation: 369

php mysql update 1 query instead much more

i have table with id and sort i need create this update

mysql_query("
UPDATE `steps` SET sort = '2' WHERE id = '9' ;
UPDATE `steps` SET sort = '1' WHERE id = '5' ;
UPDATE `steps` SET sort = '3' WHERE id = '6' ;
UPDATE `steps` SET sort = '4' WHERE id = '4' ;
UPDATE `steps` SET sort = '5' WHERE id = '2' ;
UPDATE `steps` SET sort = '6' WHERE id = '1' ;
UPDATE `steps` SET sort = '7' WHERE id = '8' ;
");

something like this is incorrect

mysql_query("UPDATE `steps` SET sort = '2' WHERE id = '9' , sort = '1' WHERE id = '5' , sort = '3' WHERE id = '6' ;");

how can minify it? have you some idea?

Upvotes: 0

Views: 72

Answers (1)

Explosion Pills
Explosion Pills

Reputation: 191749

mysql_query does not support running multiple queries with a single string. MySQLI has support for this through mysqli_multi_query, but I personally think that this is an antipattern. As far as I know, PDO does not support it.

What you can do is use multiple mysql_query statements, but it can also be done in one query:

UPDATE steps SET sort = CASE
    WHEN id = 9 THEN 2
    WHEN id = 5 THEN 1
    ELSE sort
END CASE

Use PDO or mysqli in new code instead of ext/mysql

Upvotes: 3

Related Questions