PHP MYSQL Update Query with @variable not working

Question

In php I'm trying to update a table column call order with incremental value step by 10 where project = to 1;

My table name is : task My table contain colomn : id,project,name,order

In phpmyadmin I succed with this query.

SET @order := 0; UPDATE `task` SET `order` = @order := @order + 10 WHERE project = 1 ;

Now in PHP I'm doing this:

$query = 'SET @order := 0; UPDATE `task` SET `order` = @order := @order + 10 WHERE project = "'.$project.'";';
$result = mysql_query($query) OR die(mysql_error());

If I echo my $query I have this.

SET @order := 0; UPDATE `task` SET `order` = @order := @order + 10 WHERE project = "1"

And I get this 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 'UPDATE `task` SET `order` = @order := @order + 10 WHERE project = "1" at line 1

Any idea what is my issue;

I know I should user PDO or any more modern SQL stuff but this is a patch on and old project ;)

You can't put multiple queries in a single call to `mysqli_query()` or `PDO::query()`. Do them as separate calls. — Barmar, May 07 '19 at 21:21
@really_operator Where is the missing semicolon? You don't have to put a semicolon at the end of the query. — Barmar, May 07 '19 at 21:22
Possible duplicate of [Executing multiple SQL queries in one statement with PHP](https://stackoverflow.com/questions/13980803/executing-multiple-sql-queries-in-one-statement-with-php) — miken32, May 07 '19 at 22:31
How is this code even running? mysql_query has been removed from PHP since version 7.0 and was deprecated for almost 10 years before. Oldest supported version is now 7.2. You need to upgrade and stop using this outdated code. — miken32, May 07 '19 at 22:33

score 1 · Accepted Answer · answered May 07 '19 at 21:25

You can't put multiple queries in a single call to mysql_query(). Split it into two calls:

$query = 'SET @order := 0'; 
mysql_query($query) OR die(mysql_error());
$query = 'UPDATE `task` SET `order` = @order := @order + 10 WHERE project = "'.$project.'";';
mysql_query($query) OR die(mysql_error());

Variables like @order persist between calls, since they're associated with the connection, not the call.

PHP MYSQL Update Query with @variable not working

1 Answers1