CODEWITHSUNDEEP
phpmysqlphpmyadmin
BackSlash
BackSlash

Reputation: 22243

php - can't execute mysql that works in phpMyAdmin

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

Answers (3)

Suresh Kamrushi
Suresh Kamrushi

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

stearm
stearm

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

Colin M
Colin M

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

Related Questions