Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 5 of 5
  1. #1
    New to the CF scene
    Join Date
    Feb 2008
    Location
    Louisville, KY
    Posts
    6
    Thanks
    1
    Thanked 0 Times in 0 Posts

    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 Code:
    <?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.

  • #2
    God Emperor Fou-Lu's Avatar
    Join Date
    Sep 2002
    Location
    Saskatoon, Saskatchewan
    Posts
    16,994
    Thanks
    4
    Thanked 2,662 Times in 2,631 Posts
    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.
    PHP Code:
    header('HTTP/1.1 420 Enhance Your Calm'); 
    Been gone for a few months, and haven't programmed in that long of a time. Meh, I'll wing it ;)

  • #3
    New to the CF scene
    Join Date
    Feb 2008
    Location
    Louisville, KY
    Posts
    6
    Thanks
    1
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by Fou-Lu View Post
    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.

  • #4
    God Emperor Fou-Lu's Avatar
    Join Date
    Sep 2002
    Location
    Saskatoon, Saskatchewan
    Posts
    16,994
    Thanks
    4
    Thanked 2,662 Times in 2,631 Posts
    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.
    PHP Code:
    $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.
    PHP Code:
    header('HTTP/1.1 420 Enhance Your Calm'); 
    Been gone for a few months, and haven't programmed in that long of a time. Meh, I'll wing it ;)

  • Users who have thanked Fou-Lu for this post:

    Adalius (07-16-2008)

  • #5
    New to the CF scene
    Join Date
    Jan 2011
    Posts
    1
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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

    Code:
    <?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>";
     
        }
        
    ?>


  •  

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •