Mysql variable not working through php

Question

Here is my query woking absolutely fine when entered directly:

SET @new_sno=0; 
SELECT(@new_sno:=@new_sno+1) AS sno_new,Products,Price,
    case sno WHEN 1 THEN 1 WHEN 2 THEN 2 WHEN 3 THEN 3 WHEN 4 THEN 4 
             WHEN 12 THEN 5 
            ELSE 6 
    END AS orderpriority 
FROM   rates ORDER BY orderpriority,sno;

but when I use it in php like

$result = mysqli_query($con,"SET @new_sno=0; 
    SELECT(@new_sno:=@new_sno+1) AS sno_new,Products,Price,
      case sno WHEN 1 THEN 1 WHEN 2 THEN 2 WHEN 3 THEN 3 WHEN 4 THEN 4 
            WHEN 12 THEN 5 
            ELSE 6 
      END AS orderpriority 
FROM rates ORDER BY orderpriority,sno");

The above statement is returning false What can be the solution?

in case you are using multiple queries you need to use mysqli_free_result($result) between them — RST
– RST, Commented Dec 9, 2014 at 8:49

sectus · Accepted Answer · 2014-12-09 08:53:01Z

1

You could use multi_query of mysqli.

Or move initialization.

SELECT(@new_sno:=@new_sno+1) AS sno_new,Products,Price,case sno WHEN 1
THEN 1 WHEN 2 THEN 2 WHEN 3 THEN 3 WHEN 4 THEN 4 WHEN 12 THEN 5 ELSE 6 END AS
orderpriority 
FROM rates, (select @new_sno:=0) `tmp` -- there
ORDER BY orderpriority,sno;

Or simply execute it one by one.

edited Dec 9, 2014 at 8:53

answered Dec 9, 2014 at 8:45

sectus

15.7k5 gold badges59 silver badges97 bronze badges

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

3 Comments

scrowler Over a year ago

For background, mysqli_query() will only accept one query (delimited by a ;) to prevent SQL injection. They have implemented the multi_query option to circumvent this.

Abhinav Over a year ago

Hi.. I just searched the use of 'tmp' -- there, I cudn find much, as a matter of fact I dint understand the query,I want to know about it, can u enlighten me? any other resources other than mysql docs please?

sectus Over a year ago

@user2401175, it's derived table. Sorry, but better to start from mysql documentation: dev.mysql.com/doc/refman/5.6/en/from-clause-subqueries.html

Collectives™ on Stack Overflow

Mysql variable not working through php

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related