mysql ranking system

[Destramic]

i have a query that selects a number of teams ordered by they're results and also have a variable called $rank which generates the teams rank just from a simple loop...but the problem i have is if i order the query by another column (team_name) then the rank will stay the same but obviosuly the query result order will change...what i need/want to know is how it is possible to change the order of the query but still keep the correct rank number for each team please?

 

<?php
$document_root = $_SERVER['DOCUMENT_ROOT'];
require_once $_SERVER['DOCUMENT_ROOT'] . "\classes\MySQL.php";

$league_id = $_GET['league_id'];

$team_select  = "SELECT t.team_id,
                        t.team_name, 
                        COUNT(r.league_match_result_id ) AS 'matches_played',
                        COUNT(CASE r.result WHEN 'Win' THEN '3' END) AS 'wins', 
                        COUNT(CASE r.result WHEN 'Loss' THEN '0' END) AS 'losses'
                        COUNT(CASE r.result WHEN 'Draw' THEN '1' END) AS 'draws'
                 FROM teams t
                 LEFT JOIN league_match_results r ON t.team_id = r.team_id
                 LEFT JOIN league_matches m ON r.league_match_id = m.league_match_id
                 WHERE m.league_id = :1
                 GROUP BY t.team_id
                 ORDER BY wins DESC, 
                          draws DESC,
                          losses DESC";

$mysql = new MYSQL();	

$team_result = $mysql->prepare($team_select);
$team_result->execute($league_id);
$team_count = $team_result->num_row_count();
?>
<table>
<tr>
	<th>Rank</th>
	<th>Team</th>
	<th>MP</th>
	<th>W</th>
	<th>L</th>
	<th>D</th>
	<th>Streak</th>
	<th>PTS</th>
</tr>
<?php 
$rank = 1;

while ($team_row = $team_result->fetch_array())
{
	$team_id        = $team_row['team_id'];
	$team_name      = $team_row['team_name'];
	$matches_played = $team_row['matches_played'];
	$wins           = $team_row['wins'];
	$losses         = $team_row['losses'];
	$draws          = $team_row['draws'];
	$points         = ($wins * 3) + ($draws * 1) + ($losses * 0);

	echo "		<tr>\n";
	echo "			<td>" . $rank . "</td>\n";
	echo "			<td>" . $team_name . "</td>\n";
	echo "			<td>" . $matches_played . "</td>\n";
	echo "			<td>" . $wins . "</td>\n";
	echo "			<td>" . $losses . "</td>\n";
	echo "			<td>" . $draws . "</td>\n";
	echo "			<td>"-"</td>\n";
	echo "			<td>" . $points . "</td>\n";
	echo "		</tr>\n";	

	$rank ++;
}
?>
</table>

[AMcHarg]

Why not have a field in your database labelled 'teamRank'?  This way when you pull the data out then you just need to pull out the rank along with each record and display as you like.  It's much simpler and more efficient than calculating the rank each time you pull out a record.

 

[Destramic]

but that means every column in the teams table would have to be updated everytime a team goes up/down the ladder...isnt there something i can do query wise?