比较两个While循环的值(PHP MYSQL) - Compare Values From 2 While Loops (PHP MYSQL)

Compare Values From 2 While Loops (PHP MYSQL)

本文关键字：PHP MYSQL 循环两个比较 While | 更新日期: 2023-09-27

我正在尝试创造梦幻足球联赛中正面对决的历史记录。我使用两个单独的查询和抓取数据与两个单独的while循环。我希望我可以用IF语句比较这两个，但是不行。

我的查询如下:

$query5 = "SELECT *, SUM(POINTS) ";
    $query5 .= " FROM SCHEDULE";
    $query5 .= " WHERE owner = 'DUSTIN'";
    $query5 .= " AND OPPONENT = 'LEE'";
    $query5 .= " GROUP BY WEEK ";
    $result5 = mysqli_query($con, $query5);
    $query6 = "SELECT *, SUM(POINTS) ";
    $query6 .= " FROM SCHEDULE";
    $query6 .= " WHERE owner = 'LEE'";
    $query6 .= " AND OPPONENT = 'DUSTIN'";
    $query6 .= " GROUP BY WEEK ";
    $result6 = mysqli_query($con, $query6);

我的PHP:

while($row5 = mysqli_fetch_assoc($result5)){$row5 ["SUM(POINTS)"];
while($row6 = mysqli_fetch_assoc($result6)){$row6 ["SUM(POINTS)"];  
if ( $row5 ["SUM(POINTS)"] > $row6 ["SUM(POINTS)"] ){
$win++;
}
else {
$loss++;
}
}
}

该函数适用于$row6，但对于$row5，它会一次又一次地抓取相同的数字，而不是循环遍历数组。

下面是示例数据:

YEAR    WEEK    OWNER   OPPONENT    POSITION    PLAYER  POINTS
2008    1   Dustin  Brandon RB  Chris Johnson, Ten RB   19.7
2008    1   Dustin  Brandon QB  Ben Roethlisberger, Pit QB  13.1
2008    1   DUSTIN  BRANDON D/ST    Titans D/ST, Ten D/ST   17
2008    1   DUSTIN  BRANDON WR  Roddy White, Atl WR 5.4
2008    1   DUSTIN  BRANDON RB  Laurence Maroney, NE RB 6.1
2008    1   BRANDON DUSTIN  RB  Adrian Peterson, Min RB 21.4
2008    1   BRANDON DUSTIN  RB  Edgerrin James, Ari RB  15
2008    1   BRANDON DUSTIN  WR  Larry Fitzgerald, Ari WR    9.1
2008    1   BRANDON DUSTIN  k   Joe Nedney, SF K    4.5
2008    1   BRANDON DUSTIN  WR  Chris Chambers, SD WR   11.4

我需要将达斯汀在第一周的总得分加起来，并将其与布兰登在第一周的总得分进行比较。

下面的查询将返回dustin, lee的总赢#和平局的总#。获胜的定义是在同一周内比对手得分更多。

该查询假设两名球员每周至少有一些积分。如果不是这种情况，您必须从内部连接转换为完整连接。

SELECT
    COUNT(CASE WHEN t1.points > t2.points THEN 1 END) dustin_win_count,
    COUNT(CASE WHEN t2.points > t1.points THEN 1 END) lee_win_count,
    COUNT(CASE WHEN t2.points = t1.points THEN 1 END) tie_count
FROM
    (SELECT WEEK, SUM(POINTS) points
    FROM SCHEDULE WHERE
    OWNER = 'DUSTIN'
    AND OPPONENT = 'LEE'
    GROUP BY WEEK) t1
    JOIN (SELECT WEEK, SUM(POINTS) points
    FROM SCHEDULE WHERE
    OWNER = 'LEE'
    AND OPPONENT = 'DUSTIN'
    GROUP BY WEEK) t2 ON t1.WEEK = t2.WEEK

我对复杂的mysql查询的来龙去路不是很清楚，但这与我在设计的一个投票系统中用来计算选票的方法类似。

$query = "SELECT SUM(POINTS)(CASE WHEN owner = 'DUSTIN' AND opponent = 'LEE' THEN 1 END) dustin, 
SUM(POINTS)(CASE WHEN owner = 'LEE' AND opponent = 'DUSTIN' THEN 1 END)lee, FROM SCHEDULE GROUP BY WEEK;";
while($row = mysql_fetch_assoc($query))
{
    if($row['dustin'] > $row['lee'])
    {
        $win++;
    }else{
        $loss++;
    }
}

它可能不适合您的情况，因为我使用它与COUNT而不是SUM，但它可能工作。