尝试使用“SET @rownum = 0;"时出错在 PHP 中 [英] Error Trying to Use "SET @rownum = 0;" in PHP

问题描述

当我在 mysql 中测试这个查询时，它很好，但是当我在 php 中运行它时，我不断收到这个错误.

When I tested this query out in mysql it was fine but when I went to run it in php I keep getting this error.

您的 SQL 语法有错误；检查与您的 MySQL 服务器版本相对应的手册，了解在 'SELECT *, (@rownum := @rownum + 1) AS rank FROM ( SELECT *, (totalWins+(total' at line 1) 附近使用的正确语法

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 代码.

This is the php code I have.

    <?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 代码

I can get the php code to work without the first line of code

$sql = " SET @rownum = 0; ";

但它不会回显等级列.

当它在 php 中时，我是否需要做一些不同的事情来行代码之一?

Is there something I have to do differently to line one of the code when it's in php?

推荐答案

mysql_query 不支持一次运行多个查询.您必须先运行
mysql_query("SET @rownum = 0;");，然后您可以在第二个 mysql_query 调用中运行其余的查询.

mysql_query does not support running more than one query at a time. You must first run
mysql_query("SET @rownum = 0;");, then you can run the rest of your query in a second mysql_query call.

这篇关于尝试使用“SET @rownum = 0;"时出错在 PHP 中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！