bravo14 Posted March 20, 2014 Share Posted March 20, 2014 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? Link to comment https://forums.phpfreaks.com/topic/287111-query-not-working-in-php-but-does-in-workbench/ Share on other sites More sharing options...
jazzman1 Posted March 20, 2014 Share Posted March 20, 2014 Have you quoted the query running by php? Link to comment https://forums.phpfreaks.com/topic/287111-query-not-working-in-php-but-does-in-workbench/#findComment-1473263 Share on other sites More sharing options...
bravo14 Posted March 20, 2014 Author Share Posted March 20, 2014 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()); Link to comment https://forums.phpfreaks.com/topic/287111-query-not-working-in-php-but-does-in-workbench/#findComment-1473268 Share on other sites More sharing options...
jazzman1 Posted March 20, 2014 Share Posted March 20, 2014 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()); Link to comment https://forums.phpfreaks.com/topic/287111-query-not-working-in-php-but-does-in-workbench/#findComment-1473269 Share on other sites More sharing options...
bravo14 Posted March 20, 2014 Author Share Posted March 20, 2014 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 Link to comment https://forums.phpfreaks.com/topic/287111-query-not-working-in-php-but-does-in-workbench/#findComment-1473271 Share on other sites More sharing options...
Barand Posted March 20, 2014 Share Posted March 20, 2014 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 Link to comment https://forums.phpfreaks.com/topic/287111-query-not-working-in-php-but-does-in-workbench/#findComment-1473301 Share on other sites More sharing options...
Recommended Posts
Archived
This topic is now archived and is closed to further replies.