PHP MySQL 比较 $row 从今天开始的日期

Question

我想知道是否可以将 while 循环中所有 $row 中的日期与今天的日期进行比较。让我解释一下。

我创建了这张图片所示的表格:

我通过连接多个表格创建了这个表格，实际上在一个 while 循环中有一个 while 循环(这就是我如何让多个游戏显示在一行上，但每行只有一个投注金额。

我想做的是将每场比赛的日期与今天的日期进行比较，以列出连赢投注是否有效(在此图像中，它们都不会有效，但这不是重点)。

我想获取今天的日期，如果它小于所有比赛日期，则 Active == 'Future'，或者如果今天的日期介于一组日期之间，则 Active == 'In Progress' , 如果今天的日期超过所有日期，则 Active == 'Past'。造成这一挑战的一个部分是我不知道会有多少个人游戏，所以我认为这样的事情可能会奏效:

/* fetch row */
$firstrow = $result2->fetch_row();

$date1=date_create("$firstrow[0]");
$date2=date_create(date("Y-m-d"));
$diff=date_diff($date1,$date2);

但我不知道要用多少行来测试我的最后一次约会，也许有一个

$row_count = $result->num_rows;
$datelast=date_create("$lastrow[$row-count-1]");

如果它是正确的逻辑，则不确定如何实现和使用它!

那么有人知道我如何将这些日期行中的每一个与今天的当前日期进行比较吗？

这是我最初的选择查询:我从我的图像中省略了一些信息，试图让问题的核心逻辑更简单，这就是为什么这里有一些额外的字段

//Create parlay select query    
$query = "SELECT
        u.first_name AS 'User First Name',
        u.last_name AS 'User Last Name',
        b.betting_site_name AS 'Betting Site',
        p.id AS 'Parlay ID',
        p.wager AS 'Wager',
        p.odds AS 'Odds',
        p.success AS 'Success',
        DATE_FORMAT(p.creationdate, '%d-%m-%Y') AS 'Date',
        pg.parlayid AS 'PG Parlay ID',
        SUM(p.wager * p.odds) AS Winnings
    FROM parlays p
    JOIN parlaygames pg ON pg.parlayid = p.id
    JOIN bonuses b ON p.bettingsiteid = b.id
    JOIN users u ON p.userid = u.id
    WHERE userid=$id
    GROUP BY p.id
    ORDER BY p.id DESC
    LIMIT 5";

$result = $mysqli->query($query) or die($mysqli->error.__LINE__);

这是我的表格创建代码(和第二个 while 循环来获取单个游戏):

  <h2>Betting History</h2>
      <table class="table table-striped userwinnings">
        <tr>
          <th>Parlay Information</th>
          <th>Wager</th>
          <th>Active</th>
        </tr>
<?php

while($row = $result->fetch_assoc()) {
          $output = '<tr>';
                  $output .= '<td><table><tr><th>Date</th><th>Game</th><th>Bet Info</th></tr>';

                  $parlayid = $row['Parlay ID'];

                  $query3 = "SELECT 
                pg.parlayid AS ParlayID,
                g.date AS GameDate,
                ht.name AS HomeTeam,
                away.name AS AwayTeam,
                pg.betinfo AS BetInfo
            FROM parlaygames pg
            JOIN parlays p ON pg.parlayid = p.id
            JOIN games g ON pg.gameid = g.id
            JOIN teams ht ON g.home_team = ht.id
            JOIN teams away ON g.away_team = away.id
            JOIN users u ON u.id = p.userid
            WHERE p.id = $parlayid";

        $result3 = $mysqli->query($query3) or die($mysqli->error.__LINE__);
        while($row3 = $result3->fetch_assoc()) {
           $gamescount = $result3->num_rows;
            $output .= '<tr>';
            $output .= '<td>'.$row3['GameDate'].'</td>';
            $output .= '<td>'.$row3['HomeTeam'].' vs '.$row3['AwayTeam'].'</td>';
            $output .= '<td>'.$row3['BetInfo'].'</td>';
            $output .= '</tr>';
        }                      

          $output .= '</table>';
                  $output .= '</td>';
                  $output .= '<td>'.$row['Wager'].'</td>';
                  $output .= '<td>'.$row['Active'].'</td>';
                  $output .= '</tr>';

                  echo $output;

}

?>
</table>

Answer 1

您好，请替换这些查询

$query3 = "SELECT 
                pg.parlayid AS ParlayID,
                g.date AS GameDate,
                ht.name AS HomeTeam,
                away.name AS AwayTeam,
                pg.betinfo AS BetInfo
            FROM parlaygames pg
            JOIN parlays p ON pg.parlayid = p.id
            JOIN games g ON pg.gameid = g.id
            JOIN teams ht ON g.home_team = ht.id
            JOIN teams away ON g.away_team = away.id
            JOIN users u ON u.id = p.userid
            WHERE p.id = $parlayid";

有了这些

$query3 = "SELECT 
                pg.parlayid AS ParlayID,
                g.date AS GameDate,
                IF((NOW() between MIN(g.date) AND MAX(g.date)),'In Progress',IF((MAX(g.date) > NOW()),'Future','Past')) as Acctive,
                ht.name AS HomeTeam,
                away.name AS AwayTeam,
                pg.betinfo AS BetInfo
            FROM parlaygames pg
            JOIN parlays p ON pg.parlayid = p.id
            JOIN games g ON pg.gameid = g.id
            JOIN teams ht ON g.home_team = ht.id
            JOIN teams away ON g.away_team = away.id
            JOIN users u ON u.id = p.userid
            WHERE p.id = $parlayid group by pg.parlayid";