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
– Barmar, Commented May 7, 2019 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
– Barmar, Commented May 7, 2019 at 21:22
Possible duplicate of Executing multiple SQL queries in one statement with PHP — miken32
– miken32, Commented May 7, 2019 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
– miken32, Commented May 7, 2019 at 22:33

Barmar · Accepted Answer · 2019-05-07 21:25:13Z

1

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.

answered May 7, 2019 at 21:25

Barmar

789k57 gold badges554 silver badges669 bronze badges

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

Collectives™ on Stack Overflow

PHP MYSQL Update Query with @variable not working

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related