Generate Standings Based on Results Table in MYSQL DB

[Adalius]

I first posted in the PHP forum, but I was redirected here. I was told that I could this could possibly be accomplished easier with a "left or inner join"?

first post (http://codingforums.com/showthread.php?t=144418)

I am trying to generate standings for a competition based on results I have recorded in a database.

I have a database with 3 tables


users - user_id, user_gamertag
teams - team_id, team_name
games - game_id, home_user, away_user, home_team, away_team, home_score, away_score


I would like players to be ranked by wins DESC and then loses ASC.

I have gotten to the point where I can list the players and their records, but I can't order them based on wins like I would like. Any help would be greatly appreciated.


<?php

$user_query = "SELECT * FROM mlcs_users";
$user_result = mysql_query($user_query);
while ($row = mysql_fetch_assoc($user_result)) {


$user_id = $row['user_id'];
$user_gamertag = $row['user_gamertag'];

// Select Home Games
$home_wins_query = "SELECT * FROM mlcs_games WHERE home_user = '$user_id'";
$home_wins_results = mysql_query($home_wins_query);
$home_wins = 0;
while ($row = mysql_fetch_assoc($home_wins_results)) {

// Add win for each game where home score > away score
if($row['home_score'] > $row['away_score']) { $home_wins++; }

}

// Select Away Games
$away_wins_query = "SELECT * FROM mlcs_games WHERE away_user = '$user_id'";
$away_wins_results = mysql_query($away_wins_query);
$away_wins = 0;
while ($row = mysql_fetch_assoc($away_wins_results)) {

// Add win for each game where away score > home score
if($row['away_score'] > $row['home_score']) { $away_wins++; }

}

// Add up wins
$wins = 0;
$wins = $home_wins + $away_wins;

$home_losses_query = "SELECT * FROM mlcs_games WHERE home_user = '$user_id'";
$home_losses_results = mysql_query($home_losses_query);
$home_losses = 0;
while ($row = mysql_fetch_assoc($home_losses_results)) {

// Add loss for each game where home score < away score
if($row['home_score'] < $row['away_score']) { $home_losses++; }

}

$away_losses_query = "SELECT * FROM mlcs_games WHERE away_user = '$user_id'";
$away_losses_results = mysql_query($away_losses_query);
$away_losses = 0;
while ($row = mysql_fetch_assoc($away_losses_results)) {

// Add loss for each game where away score < home score
if($row['away_score'] < $row['home_score']) { $away_losses++; }

}
// Add up losses
$losses = 0;
$losses = $home_losses + $away_losses ;

// Outputs player and his record
echo "<li>$user_gamertag ($wins-$losses)</li>";

}

?>

[bazz]

Well I'm no expert but I would look into using 'ORDER BY fieldname ASC' or 'ORDER BY fieldname DESC'.

bazz

[Fou-Lu]

You're first post peaked my interest and I decided to take a crack at this one. I know this is for PHP so I'll provide it in PHP code:

$sQry = "SELECT u.*,
(
SELECT count(*)
FROM `games` iw
WHERE
CASE iw.home_user
WHEN u.user_id THEN
iw.home_score > iw.away_score
ELSE
iw.home_score < iw.away_score
END
) AS totalWins,
(
SELECT count(*)
FROM `games` iw
WHERE
CASE iw.home_user
WHEN u.user_id THEN
iw.home_score < iw.away_score
ELSE
iw.home_score > iw.away_score
END
) AS totalLosses
FROM `user` u
ORDER BY totalWins DESC, totalLosses ASC";
$qry = mysql_query($sQry) or die(mysql_error());
while ($row = mysql_fetch_assoc($qry))
{
printf("<li>%s (%d - %d)</li>\n", $row['user_gamertag'], $row['totalWins'], $row['totalLosses']);
}


Since you have a many to many relationship between you're games and users/teams, further normalization of the games table could remove the complication for this query.

[Chapati]

Heres the code I use to rank by level, first showing the top 20, and then the user's (identified by their id with the $id variable) individual rank. Made this a couple days ago, not too sure if there are better ways out there but I like to keep things simple:

$result = mysql_query("SELECT * FROM master ORDER BY level DESC LIMIT 0,20");


echo "<b><u>Highest Levels</u></b><br>";

$i=1;

while ($row=mysql_fetch_array($result)){
$ranklet=$i;
$ranklevel=($row['level']);
$rankname=($row['name']);
echo "<br>".$ranklet.") Level: ".$ranklevel." (".$rankname.")";
$i++;
}

$result = mysql_query("SELECT * FROM master ORDER BY level DESC");

$i=1;

while ($row=mysql_fetch_array($result)){
if ($row['id'] != $id){
$i++;
}else{
$ranklet=$i;
$ranklevel=($row['level']);
$rankname=($row['name']);
}
}
echo "<br><br>".$ranklet.") Level: ".$ranklevel." (".$rankname.")";