Reputation: 22243
i have this table for a game
scores (user,score)
with some entries in it, i have this query:
SET @row_num = 0;
SELECT @row_num := @row_num + 1 as row_index, user, score FROM scores ORDER BY score DESC
it works in phpmyadmin but it doesn't work with php code
$query = "set @row_num = 0; SELECT @row_num := @row_num + 1 as row_index, user, score FROM scores ORDER BY score DESC";
$result = mysql_query($query) or die(mysql_error());
php is properly connected to mysql database, i do other queries before that and everything works
it says:
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 @row_num := @row_num + 1 as row_index, user, score FROM scores ORDER BY s' at line 1
What do you think it could be? Thank you, Alessandro
Upvotes: 0
Views: 668
Reputation: 16086
First of all mysql_query
is depricated.
You cannot run two queries in a single call of mysql_query
$query = "set @row_num = 0; SELECT @row_num := @row_num + 1 as row_index, user, score FROM scores ORDER BY score DESC";
You have to do like below:
$query1= "set @row_num = 0;";
$query2 = "SELECT @row_num := @row_num + 1 as row_index, user, score FROM scores ORDER BY score DESC";
$result1 = mysql_query($query1);
$result2 = mysql_query($query2);
Upvotes: 2
Reputation: 143
Try to split in 2 queries:
$query = "set @row_num = 0;";
$query2 = " SELECT @row_num := @row_num + 1 as row_index, user, score FROM scores ORDER BY score DESC;";
$result = mysql_query($query) or die(mysql_error());
$result1 = mysql_query($query2);
Upvotes: 1
Reputation: 13346
This is because phpMyAdmin splits queries on semi-colons for you. The MySQL server does not do this, and neither does the PHP mysql
extension.
You'll need to separate these out into two separate calls to mysql_query
Side note: The PHP mysql
extension is deprecated and it's recommended that you use PDO
or mysqli
for any new code. Please keep that in mind during your future development.
Upvotes: 6