Create Ranking base system and updating Rank

Question

Guys I'm new here and new to MySQL too ..

So I am trying to create a database which manages a team record.The database contains a table named team with a set of columns as follow,

TeamID
TeamRank
TeamName
TeamWins
TeamLoss
TeamPoints

So the agenda is to rank the teams on the basis of points, the greater the points higher the rank.

<?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);

Im recieveing this Error

Warning: mysqli_fetch_array() expects parameter 1 to be mysqli_result

Where am I Wrong? If there a problem with the code please guide me.

P.S[EDIT]: All I want to do is take TeamName,TeamWins,TeamLoss and TeamPoints as input, and as the TeamPoints increase/decrease of a team its supposed to move the rank upwards/downwards and display a Ranking table.

the php quotes were my bad I thought we have to apply quotes to insert a code. — Zain Sohail, Dec 09 '13 at 07:49

Mehdi · Accepted Answer · 2013-12-09T07:58:44.320

1

You are using mysqli_query for multi query. so you have to use mysqli_multi_query.

change your code to:

<?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);
?>

edited Dec 09 '13 at 07:58

answered Dec 09 '13 at 07:53

Mehdi

661
5
17

Ok thanks for the reply Mehdi! But I dont know whats up with the code, its not incrementing the TeamRank, its creating two more columns of TeamRanks and TeamName. All I want to do is take TeamName,TeamWins,TeamLoss and TeamPoints as input, and as the TeamPoints increase/decrease of a team its supposed to move the rank upwards/downwards. – Zain Sohail Dec 09 '13 at 08:13
this is another thing, so your main problem solved, so please select as accepted answer, so i will take a look at the next problem – Mehdi Dec 09 '13 at 08:19
which filed is unique in your table? for using INSERT INTO .. ON DUPLICATE UPDATE a filed must be unique – Mehdi Dec 09 '13 at 08:29
if you mean the primary key, then TeamID – Zain Sohail Dec 09 '13 at 08:31
no, primary key is another thing. take a look at http://dev.mysql.com/doc/refman/5.0/en/insert-on-duplicate.html you must have a UNIQUE filed in your table, witch you have not! create an unique filed in your table for TeamName field. – Mehdi Dec 09 '13 at 08:34
ALTER TABLE `team` ADD UNIQUE ( `TeamName` ); Done ... – Zain Sohail Dec 09 '13 at 08:42
so try your code again – Mehdi Dec 09 '13 at 08:42
Nah, not working.. What if we used UPDATE, instead of INSERT INTO? – Zain Sohail Dec 09 '13 at 08:45
it is going to be chat, so manage to open a new question, this is rule of stackoverflow – Mehdi Dec 09 '13 at 08:48

Create Ranking base system and updating Rank

1 Answers1