创建排名基础系统更新排名

Question

伙计们，我是新来的，也是MySQL的新手。

因此，我试图创建一个管理team record.The数据库的数据库，该数据库包含一个名为team的表，其中包含一组列，如下所示，

• TeamID
• TeamRank
• TeamName
• TeamWins
• TeamLoss
• TeamPoints

因此，我们的议程是根据分数对球队进行排名，分数越高，排名就越高。

<?php
$con = mysqli_connect("", "", "", "");
// Check connection
if (mysqli_connect_errno()) {
  echo "Failed to connect to MySQL: " . mysqli_connect_error();
}

$result = mysqli_query($con, "SET @rownum := 0;
  INSERT INTO team( TeamRank, TeamName)
  SELECT @rownum := @rownum + 1 AS TeamRank, TeamName 
  FROM (SELECT SUM(TeamRank)AS TeamRank , TeamName 
  FROM team
  GROUP BY TeamName 
  ORDER BY TeamRank DESC) as result
  ON DUPLICATE KEY UPDATE TeamName = VALUES(TeamName);"
);

echo "<table border='1'>
  <tr>
  <th>Rank</th>
  <th>TeamID</th>
  <th>TeamName</th>
  <th>Total Points</th>
  </tr>";

while ($row = mysqli_fetch_array($result)) {
  echo "<tr>";
  echo "<td>" . $row['TeamRank'] . "</td>";
  echo "<td>" . $row['TeamID'] . "</td>";
  echo "<td>" . $row['TeamName'] . "</td>";
  echo "<td>" . $row['TeamPoints'] . "</td>";
  echo "</tr>";
}

echo "</table>";

mysqli_close($con);

我收到了这个错误

Warning: mysqli_fetch_array() expects parameter 1 to be mysqli_result

我哪里错了？如果代码有问题，请指导我。

P.SEDIT:我想做的就是把TeamName、TeamWins、TeamLoss和TeamPoints作为输入，当团队的TeamPoints增加/减少时，它应该将排名向上/向下移动，并显示一个排名表。

Answer 1

您正在使用mysqli_query进行多个查询。所以你必须使用mysqli_multi_query.

将代码更改为：

<?php
$con=mysqli_connect("","","","");
// Check connection
if (mysqli_connect_errno())
{
echo "Failed to connect to MySQL: " . mysqli_connect_error();
}

$query="SET @rownum := 0;
INSERT INTO team( TeamRank, TeamName)
SELECT @rownum := @rownum + 1 AS TeamRank, TeamName 
FROM (SELECT SUM(TeamRank)AS TeamRank , TeamName 
FROM team
GROUP BY TeamName 
ORDER BY TeamRank DESC) as result
ON DUPLICATE KEY UPDATE TeamName = VALUES(TeamName);
";

echo "<table border='1'>
<tr>
<th>Rank</th>
<th>TeamID</th>
<th>TeamName</th>
<th>Total Points</th>
</tr>";

if (mysqli_multi_query($con,$query)) {

    do {
        /* store first result set */
        if ($result = mysqli_store_result($con)) {
            while ($row = mysqli_fetch_row($result)) {
                echo "<tr>";
                echo "<td>" . $row['TeamRank'] . "</td>";  
                echo "<td>" . $row['TeamID'] . "</td>";
                echo "<td>" . $row['TeamName'] . "</td>";
                echo "<td>" . $row['TeamPoints'] . "</td>";
                echo "</tr>";
            }
            mysqli_free_result($result);
        }
    } while (mysqli_next_result($con));
}
echo "</table>";


mysqli_close($con);
?>