Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Need help calculating wins and losses from points scored

Tags:

php

mysql

I am creating a website in php to log ping pong scores for my school, and currently the player who wins will log the WinnerID, LoserID, PointsFor, PointsAgainst. I have two tables with the following relationships.

Table: users

  • user_ID (PK)
  • username
  • Elo

Table: games

  • game_id (PK)
  • WinnerID (FK)
  • LoserID (FK)
  • PointsFor
  • PointsAgainst

My insert statement in the php file is:

INSERT INTO games(WinnerID,LoserID,PointsFor,PointsAgainst) VALUES('$Winner_ID','$Loser_ID','$userscore','$oppscore')"

Here is what I have tried, but it doesn't display the scores correctly.

SELECT min(u.username) 'Username', COUNT(g.WinnerID) 'Wins', sum(g.PointsFor) 'Points For', sum(g.PointsAgainst) 'Points Against', u.Elo 'Ranking' 
from games g 
LEFT JOIN users u 
on g.WinnerID = u.user_id 
Group by g.WinnerID

This is the select statement output

As you can see by the image above, the points for and points against totals don't add up. Currently, it only displays the stats for whoever was winner. Meaning if PlayerA wins 21-5, it will show up from the select statement, but PlayerB will not show a score of 5-21. Any help is appreciated.

PHP code for page to enter scores:

if(isset($_POST['btn-post']))
{
    $opponent = $_POST["opponent"];
    //$opponent = array_key_exists('opponent', $_POST) ? $_POST['opponent'] : false;

    $userscore = mysql_real_escape_string($_POST['userscore']);
    $oppscore = mysql_real_escape_string($_POST['oppscore']);

    if($userscore != $oppscore)
    {
        if($userscore > $oppscore)
        {
            $Winner_ID = $_SESSION['user'];
            $query = mysql_query("SELECT user_id FROM users WHERE username = '".$opponent."'");
            $result = mysql_fetch_array($query) or die(mysql_error());
            $Loser_ID = $result['user_id'];

            $query1 = mysql_query("SELECT Elo FROM users WHERE user_id=".$_SESSION['user']);
            $result1 = mysql_fetch_array($query1) or die(mysql_error());
            $winnerRating = $result1['Elo'];

            $query2 = mysql_query("SELECT Elo FROM users WHERE user_id=".$Loser_ID);
            $result2 = mysql_fetch_array($query2) or die(mysql_error());
            $loserRating = $result1['Elo'];

            $rating = new Rating($winnerRating, $loserRating, 1, 0);            
            $results = $rating->getNewRatings();

            if(mysql_query("UPDATE users SET Elo = " . $results['a'] . " WHERE user_id=".$_SESSION['user']))
            {

            }
            else
            {
                ?>
                <script>alert('There was an error while entering winners(user) ranking...');</script>
                <?php
            }
            if(mysql_query("UPDATE users SET Elo = " . $results['b'] . " WHERE user_id=".$Loser_ID))
            {

            }
            else
            {
                ?>
                <script>alert('There was an error while entering losers(opp) ranking..');</script>
                <?php
            }

        }   
        elseif($oppscore > $userscore)
        {       
            $Loser_ID = $_SESSION['user'];
            $query = mysql_query("SELECT user_id FROM users WHERE username = '".$opponent."'");
            $result = mysql_fetch_array($query) or die(mysql_error());
            $Winner_ID = $result['user_id'];

            //get rating from user table in database

            $query1 = mysql_query("SELECT Elo FROM users WHERE user_id=".$_SESSION['user']);
            $result1 = mysql_fetch_array($query1) or die(mysql_error());
            $loserRating = $result1['Elo'];

            $query2 = mysql_query("SELECT Elo FROM users WHERE user_id=".$Loser_ID);
            $result2 = mysql_fetch_array($query2) or die(mysql_error());
            $winnerRating = $result1['Elo'];

            $rating = new Rating($winnerRating, $loserRating, 1, 0);            
            $results = $rating->getNewRatings();

            $results = $rating->getNewRatings();
            if(mysql_query("UPDATE users SET Elo = " . $results['b'] . " WHERE user_id=".$_SESSION['user']))
            {

            }
            else
            {
                ?>
                <script>alert('There was an error while entering losers(user) ranking...');</script>
                <?php
            }
            if(mysql_query("UPDATE users SET Elo = " . $results['a'] . " WHERE user_id=".$Winner_ID))
            {

            }
            else
            {
                ?>
                <script>alert('There was an error while entering winners(opp) ranking...');</script>
                <?php
            }

        }
        if(mysql_query("INSERT INTO games(WinnerID,LoserID,PointsFor,PointsAgainst) VALUES('$Winner_ID','$Loser_ID','$userscore','$oppscore')"))
        {
            ?>
            <script>alert('Your scores were successfully entered');</script>
            <?php
        }
        else
        {
            ?>
            <script>alert('There was an error while entering your score...');</script>
            <?php
        }
    }
    else
    {
        ?>
        <script>alert('There cannot be a tie in ping pong, please re-enter your scores...');</script>
        <?php
    }

}
?>
like image 967
Matt Kinne Avatar asked Dec 15 '15 05:12

Matt Kinne


People also ask

How do you find average wins and losses?

Win to Loss Ratio Formula To calculate a win-to-loss ratio, divide the number of wins by the number of losses. To calculate a win rate percentage, divide the number of wins by the total number of games, then multiply by 100.

How do I calculate wins and losses in Excel?

To calculate the number of losses, simply replace "L" in each formula with "W". This array formula, entered by pressing Shift+Ctrl+Enter, returns the number of wins (W characters) in the range B3:H3. The function returns the number of uppercase W characters in the range.

How is win rate calculated?

How to calculate win rate. To calculate your win rate, divide the number of won opportunities by the total number of opportunities over a given time. It is important to calculate the win rate based on the opportunities that are sales qualified, rather than on every opportunity in your pipeline.


2 Answers

Your query fails because it doesn't take into account the rows where the player loses. You can fix that by using unions. The following query should do what you want:

SELECT  username AS "Username",
    SUM(wins) AS "Wins",
    SUM(PF) AS "Points For",
    SUM(PA) AS "Points Against",
    elo AS "Ranking"
FROM (
    (SELECT users.user_ID,
            users.username AS username,
            COUNT(games.WinnerID) AS wins,
            SUM(games.PointsFor) AS PF,
            SUM(games.PointsAgainst) AS PA,
            users.Elo AS elo
    FROM users, games
    WHERE games.WinnerID = users.user_ID
    GROUP BY users.user_ID)
    UNION ALL
    (SELECT users.user_ID,
        users.username AS username,
        0 AS wins,
        SUM(games.PointsAgainst) AS PF,
        SUM(games.PointsFor) AS PA,
        users.Elo AS elo
    FROM users, games
    WHERE games.LoserID = users.user_ID
    GROUP BY users.user_ID)
) AS t
GROUP BY username
ORDER BY user_ID;

Note that in the "losing query" the field PointsAgainst should be counter as the player's "points for" and vice-versa.

like image 58
Ghabriel Nunes Avatar answered Oct 28 '22 13:10

Ghabriel Nunes


Try it as an inner join, and get rid of the MIN() on the username column:

SELECT u.username, COUNT(g.WinnerID),
       SUM(g.PointsFor), SUM(g.PointsAgainst), u.Elo
  FROM users u, games g
  WHERE u.user_id = g.WinnerID
  GROUP BY u.username, u.Elo;

Also, before anyone else takes you to task, you should be using mysqli instead of mysql (or better yet, PDO) and you should be using prepared statements instead of dynamic SQL.

like image 40
Darwin von Corax Avatar answered Oct 28 '22 15:10

Darwin von Corax