Pass on variable sql queries in php

Question

This query works when entered into phpMyAdmin however I cannot get it to work in PHP:

SET @cnt = 0; UPDATE groupsnumber SET ordering = @cnt := (@cnt+1) ORDER BY name

This is the code I have tried:

 $mysqli = new mysqli($dbhost, $dbuser, $dbpass, $dbname);

 $resSrt =$mysqli->query("SET @cnt = 0; 
                         UPDATE groupsnumber 
                         SET ordering = @cnt := (@cnt+1) ORDER BY name"); 

 if (!$resSrt) 
 {
     echo "Error executing query: (" . $mysqli->errno . ") " . $mysqli->error; 
 }

Here is the error message:

Error executing query: (1064) 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 groupsnumber SET ordering = @cnt := (@cnt+1) ORDER BY name' at line 1

Any ideas how to fix this?

NoobEditor · Accepted Answer · 2014-04-24 09:16:58Z

2

Simple reason :

In

SET @cnt = 0; UPDATE groupsnumber SET ordering = @cnt:= (@cnt+1) ORDER BY name"

You are trying to execute 2 queries through single $mysqli->query function.

SET as first query
Update as second query

mysqli_ doesn't allow multiple queries to be run through its single $mysqli->query, this is the reason why its secure and preferred as an option to prevent sql_injections.

Divide them is 2 separate queries and your are good to go!! :)

answered Apr 24, 2014 at 9:16

NoobEditor

16k20 gold badges85 silver badges117 bronze badges

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

1 Comment

user3567864 Over a year ago

I thought the second query would not know the first set @cnt value if I divide it in 2 separate queries. But it seems to work. Thanks!!!

Collectives™ on Stack Overflow

Pass on variable sql queries in php

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related