CODEWITHSUNDEEP
phpmysqlsqlmysqli
user3567864
user3567864

Reputation: 27

Pass on variable sql queries in php

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?

Upvotes: 0

Views: 156

Answers (1)

NoobEditor
NoobEditor

Reputation: 15871

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!! :)

Upvotes: 2

Related Questions