Query not working in PHP but does in Workbench

[bravo14]

Hi Guys

 

I am using the following query

 

 

 
SET @serial=0;
 
SELECT 
   @serial := @serial+1 AS `photo_number`, 
   `photo_filename`
FROM 
   `gallery_photos`
ORDER BY RAND()
LIMIT 20

 

When I run the query in MySQL Workbench, I have a set of results, when I run it on a PHP page the query does not run I get the following error displayed

 

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 'SELECT @serial := @serial+1 AS `photo_number`, `photo_filename` FROM ' at line 3

 

Any ideas why this would happen?

[jazzman1]

Have you quoted the query running by php?

[bravo14]

The PHP code with the query is:

 

 

$bg_sql=("SET @serial=0;

SELECT
   @serial := @serial+1 AS `photo_number`,
   `photo_filename`
FROM
   `gallery_photos`
ORDER BY RAND()
LIMIT 20");
$bg_result=mysql_query($bg_sql) or die (mysql_error());

[jazzman1]

Separate the set from select sql statement.

 

Try,

mysql_query("SET @serial=0;") or die(mysql_error());

$bg_sql=("SELECT
   @serial := @serial+1 AS `photo_number`,
   `photo_filename`
FROM
   `gallery_photos`
ORDER BY RAND()
LIMIT 20");
$bg_result=mysql_query($bg_sql) or die (mysql_error());

[bravo14]

The query now works, althoughn not displaying the results I was expecting, results below.  I wanted the photo number to to display the incrementing number 1-20 as I am limiting it to 20, is there anyway I can have the photo_number generated after the sort?

 

photo_number photo_filename   124 124.jpg 46 46.jpg 55 55.jpg 87 87.jpg 6 6.jpg 21 21.jpg 42 42.jpg 112 112.jpg 18 18.jpg 86 86.jpg 69 69.jpg 108 108.jpg 4 4.jpg 107 107.jpg 93 93.jpg 41 41.jpg 65 65.jpg 11 11.jpg 13 13.jpg 24 24.jpg

Edited March 20, 2014 by bravo14

[Barand]

this'll do it in a single query for you

SELECT 
    @serial:=@serial+1 as photo_number
    , photo_filename
FROM (
    SELECT photo_filename
    FROM gallery_photos
        JOIN (SELECT @serial:=0) as init
    ORDER BY RAND()
    LIMIT 20
    ) as sorted