当我在mysql中测试这个查询时，它很好，但是当我在php中运行它时，我不断收到此错误。
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 *, (@rownum := @rownum + 1) AS rank FROM ( SELECT *, (totalWins+(total' at line 1
这是我拥有的 php 代码。
<?php
$sql = " SET @rownum = 0; ";
$sql .= " SELECT *, (@rownum := @rownum + 1) AS rank FROM ( ";
$sql .= " SELECT *, (totalWins+(totalPushs*.5)) AS totalPoints, totalWins+totalLost+totalPushs AS totalBets FROM ( ";
$sql .= " SELECT *, SUM(win) AS totalWins, SUM(lost) AS totalLost, SUM(push) AS totalPushs FROM ( ";
$sql .= " SELECT *, (finalResult = 'Winner') AS win, (finalResult = 'Loser') AS lost, (finalResult = 'Push') AS push FROM ( ";
$sql .= " SELECT " . $db_prefix . "users.userID, userName, ";
$sql .= " IF (pickID=visitorID, visitorResult, homeResult) AS finalResult ";
$sql .= " FROM " . $db_prefix . "users ";
$sql .= " JOIN " . $db_prefix . "picks ";
$sql .= " ON " . $db_prefix . "users.userID = " . $db_prefix . "picks.userID ";
$sql .= " JOIN " . $db_prefix . "schedule ";
$sql .= " ON " . $db_prefix . "picks.gameID = " . $db_prefix . "schedule.gameID ";
$sql .= " ) x ";
$sql .= " ) x ";
$sql .= " GROUP BY userID ";
$sql .= " ) x ";
$sql .= " ) x ";
$sql .= " ORDER BY totalPoints DESC, totalWins DESC, totalPushs DESC, totalLost ";
$result = mysql_query($sql) or die(mysql_error());
while ($row = mysql_fetch_array($result)) {
echo $row[rank] . '|' . $row[userName]. '|' . $row[totalWins] . '|' . $row[totalLost] . '|' . $row[totalPushs] . '|' . $row[totalPoints];
echo '<br>';
}
?>
我可以在没有第一行代码的情况下让 php 代码工作
$sql = " SET @rownum = 0; ";
但它不会回显排名列。
当代码在 php 中时，我必须做一些不同的事情来排列其中一段代码吗？
mysql_query不支持一次运行多个查询。必须先运行
mysql_query("SET @rownum = 0;"); ，则可以在第二次mysql_query调用中运行查询的其余部分。
请尝试表名。
是的，尼特。所以你只需要这样做：
$results = mysql_query('SET @rownum = 0, @rank = 1, @prev_val = NULL;', $localhost);
将其放在数据库选择查询的顶部，它应该可以工作。