我有一个游戏表
scores (user,score)
中有一些条目,我有这样的查询:
SET @row_num = 0;
SELECT @row_num := @row_num + 1 as row_index, user, score FROM scores ORDER BY score DESC
它在phpmyadmin中工作,但在PHP代码中不起作用
$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正确连接到mysql数据库,在此之前我做其他查询,一切正常
上面写着:
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
你觉得会是什么?谢谢你!亚历山德罗
这是因为phpMyAdmin为您拆分分号查询。MySQL服务器没有做这个,PHP mysql
扩展也没有。
您需要将这些分离为对mysql_query
的两个单独调用
旁注:PHP mysql
扩展已弃用,建议您使用PDO
或mysqli
的任何新代码。
首先禁用mysql_query
。
不能在一次调用mysql_query
$query = "set @row_num = 0; SELECT @row_num := @row_num + 1 as row_index, user, score FROM scores ORDER BY score DESC";
你必须这样做:
$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);
尝试拆分为2个查询:
$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);