需要帮助来计算得分的赢利和亏损

马特·金恩

我正在用php创建一个网站来记录我学校的乒乓球成绩，目前获胜的玩家将登录WinnerID, LoserID, PointsFor, PointsAgainst。我有两个具有以下关系的表。

表：用户

user_ID（PK）
用户名
链接

表：游戏

game_id（PK）
WinnerID（FK）
失败者ID（FK）
要点
积分反对

我在php文件中的插入语句是：

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

这是我尝试过的方法，但是无法正确显示分数。

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

如上图所示，总计的点和相对于总计的点不累加。当前，它仅显示获胜者的统计信息。这意味着如果PlayerA赢得21-5，它将显示在选择语句中，但是PlayerB不会显示5-21的分数。任何帮助表示赞赏。

用于输入分数的页面的PHP代码：

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
    }

}
?>

加布里埃尔·纳尼斯（Ghabriel Nunes）

您的查询失败，因为它没有考虑玩家输掉的行。您可以使用union来解决此问题。以下查询应执行您想要的操作：

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;

注意，在“失败查询”中，该字段PointsAgainst应与玩家的“得分”相反，反之亦然。

本文收集自互联网，转载请注明来源。

如有侵权，请联系 [email protected] 删除。