文章/答案/技术大牛

发布

社区首页 >问答首页 >需要帮助计算得分的胜负。

问需要帮助计算得分的胜负。
EN

Stack Overflow用户

提问于 2015-12-15 05:58:03

回答 3查看 127关注 0票数 6

我正在创建一个网站，在php，以记录我的学校乒乓球成绩，目前的球员谁赢将记录的WinnerID, LoserID, PointsFor, PointsAgainst。我有两个表，具有以下关系。

表:用户

user_ID (PK)
用户名
Elo

表:小游戏

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

我在php文件中的insert语句是：

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，它就会从select语句中显示出来，但是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
    }

}
?>

php

mysql

回答 3

Stack Overflow用户

回答已采纳

发布于 2015-12-15 19:05:01

您的查询失败，因为它没有考虑到播放机丢失的行。您可以通过使用工会来解决这个问题。下面的查询应该做您想做的事情：

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应该是计数器，作为玩家的“点数”，反之亦然。

票数 1

Stack Overflow用户

发布于 2015-12-15 06:51:21

尝试将其作为内部联接，并去掉username列上的username：

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;

而且，在其他人带您执行任务之前，您应该使用mysqli而不是mysql (或者更好的是使用PDO)，并且应该使用准备好的语句而不是动态SQL。

票数 1

Stack Overflow用户

发布于 2015-12-15 06:35:35

你的

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

查询以潜在的错误顺序包含$userscore和$oppscore值。$Winner_ID和$Loser_ID在条件处理中可能会发生变化，但是$userscore和$oppscore在当时没有类似的翻转。

而且，条件结构通常是不必要的冗长。似乎你应该能够先确定胜利者和输家的ID和得分，然后再做所有处理一次，而不是诉诸容易出错的复制复制几乎相同的代码。

票数 0

页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持

原文链接：

https://stackoverflow.com/questions/34282072

复制

相似问题

问需要帮助计算得分的胜负。
EN

回答 3

Stack Overflow用户

Stack Overflow用户

Stack Overflow用户

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问需要帮助计算得分的胜负。EN

回答 3

Stack Overflow用户

Stack Overflow用户

Stack Overflow用户

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问需要帮助计算得分的胜负。
EN