Generate Standings Based on Results Table in MYSQL DB

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 written a script (posted below) that will output all players and their records, I just don't know how to order him. Any help would be greatly appreciated. If I have to redo the database it will be a pain, but I can do it. Just need to have it when I enter in a game the standings update on where I have the script.


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

}

?>


Let me know if there is anything else you need to know. Thanks again.

Since its just ordering, you can do this in SQL using the ORDER BY clause.
ORDER BY wins DESC, losses ASC just needs tacking on at the end of you're queries.

Since its just ordering, you can do this in SQL using the ORDER BY clause.
ORDER BY wins DESC, losses ASC just needs tacking on at the end of you're queries.

I don't have a wins or losses column on my table, because I don't want to have to manually add wins and losses in the users table each time i enter a result in the games table. I am able to get the amount of wins and loses as shown in my snippet above, just don't know how to get to the next step and order them.

Sorry should have paid more attention to you're structure. To do this in PHP you need to write a custom sorting algorithm to control how you would like to sort you're results. Before output from the query you need to capture all the results in an array. To do this though you'll need to join you're queries together to capture a single result set. Using an inner or left join on you're results will allow you to capture everything into a single result.

$sQry = "SELECT u.*, h.*, a.*
FROM `users` u
LEFT JOIN `mlcs_games` h ON (h.home_user = u.user_id)
LEFT JOIN `mlcs_games` a ON (a.away_user = u.user_id)";

This result will contain all the information about away and home games for each user. I think you can perform an order by on these ones if you filter an order by on the home score versus the away score. Now, in you're while loop for fetching arrays you'll need to append each record to an array you created. This will let you use a usort command so you can create a custom compareto for each of you're items, and the only way you'll be able to sort this in PHP. After that, you can perform a foreach on the array to do all of the processing.

Check with the guys in the SQL section, it will be far easier if you can create an orderby using the results from above. I think you can just subtract them, but I don't want to say that for certain.

Hi all

Sorry to revive an old thread, i've found this of great use (i'm fairly new to PHP!)

I am trying to achieve the same result and am pleased with the output i have (username, points, won lost, drawn). However, how can i order this by points?

Secondly, all my usernames (from customers table) appear (with 0 points etc) how can i only display users who have played a game?

Apologies for the messy code, thank you in advance

<?php

$player_query = "SELECT * FROM customers";
$player_result = mysql_query($player_query);
while ($row = mysql_fetch_assoc($player_result)) {


$player_id = $row['customers_id'];
$player_name = $row['customers_firstname'];


// Select Home Games
$home_wins_query = "SELECT * FROM scores where event_id = '" . (int)$HTTP_GET_VARS['products_id'] . "' and product_id = '" . $products_related_id . "' and game_event_id = 5 and user_id_1 = '$player_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['user_id_1_score'] > $row['user_id_2_score']) { $home_wins++; }

}

// Select Away Games
$away_wins_query = "SELECT * FROM scores where event_id = '" . (int)$HTTP_GET_VARS['products_id'] . "' and product_id = '" . $products_related_id . "' and game_event_id = 5 and user_id_2 = '$player_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['user_id_2_score'] > $row['user_id_1_score']) { $away_wins++; }

}

// Select home draws
$home_draws_query = "SELECT * FROM scores where event_id = '" . (int)$HTTP_GET_VARS['products_id'] . "' and product_id = '" . $products_related_id . "' and game_event_id = 5 and user_id_1 = '$player_id'";
$home_draws_results = mysql_query($home_draws_query);
$home_draws = 0;
while ($row = mysql_fetch_assoc($home_draws_results)) {

// Add win for each game where away score > home score
if($row['user_id_1_score'] == $row['user_id_2_score']) { $home_draws++; }

}

// Select away draws
$away_draws_query = "SELECT * FROM scores where event_id = '" . (int)$HTTP_GET_VARS['products_id'] . "' and product_id = '" . $products_related_id . "' and game_event_id = 5 and user_id_2 = '$player_id'";
$away_draws_results = mysql_query($away_draws_query);
$away_draws = 0;
while ($row = mysql_fetch_assoc($away_draws_results)) {

// Add win for each game where away score > home score
if($row['user_id_2_score'] == $row['user_id_1_score']) { $away_draws++; }

}

// Add up drawa
$draws = 0;
$draws = $home_draws + $away_draws;

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

//add up points for wins
$winpoints = 0;
$winpoints = $wins * 3 + $draws;

$home_losses_query = "SELECT * FROM scores where event_id = '" . (int)$HTTP_GET_VARS['products_id'] . "' and product_id = '" . $products_related_id . "' and game_event_id = 5 and user_id_1 = '$playersc_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['user_id_1_score'] < $row['user_id_2_score']) { $home_losses++; }

}

$away_losses_query = "SELECT * FROM scores where event_id = '" . (int)$HTTP_GET_VARS['products_id'] . "' and product_id = '" . $products_related_id . "' and game_event_id = 5 and user_id_2 = '$playersc_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['user_id_2_score'] < $row['user_id_1_score']) { $away_losses++; }

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


// Outputs player and his record
echo "<li>$player_name (Pts:$winpoints Won:$wins Lost:$losses Drawn:$draws)</li>";

}

?>