php - 按表中的日期对 SQL 数据库中的行进行分组

Question

更新 - 添加查询

我正在开发一个从 MySQL 数据库收集游戏结果的系统。我希望结果按日期“分组”(例如，特定日期的结果将位于一个表中)。

我已经快到了，但正在努力获取同一日期的结果以显示为表格的下一行。

.

这是目前的代码:

$result = mysqli_query($con, "SELECT  `MatchDate`, t1.`TeamName` AS HomeTeam, `MatchHomeScore`, `MatchAwayScore`, t2.`TeamName` AS AwayTeam `FROM  `match` INNER JOIN  `team` t1 ON  `match`.`MatchHomeTeamID` = t1.`TeamID` INNER JOIN  `team` t2 ON  `match`.`MatchAwayTeamID` = t2.`TeamID` ORDER BY `MatchDate` ASC ");`

$currentDate = false;
while($row = mysqli_fetch_array($result))
{
    echo '<div class="col-lg-4">';
        echo '<div class="panel panel-default-fixtures">';
            if ($row['MatchDate'] != $currentDate){
            echo '<div class="panel-heading">';
                echo '<h4 class="panel-title">';
                    echo $row['MatchDate'];
                echo '</h4>';
            echo '</div>';
        $currentDate = $row['MatchDate'];
    }
    echo '<table width="100%" class="table table-striped table-bordered table-hover dataTables">';
                echo '<thead>';
                    echo '<tr>';
                        echo '<th class="all">';
                            echo 'Home';
                        echo '</th>';
                        echo '<th colspan="2">';
                            echo 'Score';
                        echo '</th>';
                        echo '<th class="all">';
                            echo 'Away';
                        echo '</th>';
                    echo '</tr>';
                echo '</thead>';
                echo '<tbody>';                                        
                    echo '<tr>';
                        echo '<td>'.$row['HomeTeam'].'</td>';
                        echo '<td>'.$row['MatchHomeScore'].'</td>';
                        echo '<td>'.$row['MatchAwayScore'].'</td>';
                        echo '<td>'.$row['AwayTeam'].'</td>';
                    echo '</tr>';                                     
                echo '</tbody>';
            echo '</table>'; 
        echo '</div>'; 
    echo '</div>'; 
} 

第二次更新

使用 Shamil Omarov 提供的代码并通过一些更改解决了该问题。这是工作代码:

    $result = mysqli_query($con, "
                        SELECT  `MatchDate`, t1.`TeamName` AS HomeTeam, `MatchHomeScore`, `MatchAwayScore`, t2.`TeamName` AS AwayTeam
                        FROM  `match` 
                        INNER JOIN  `team` t1 ON  `match`.`MatchHomeTeamID` = t1.`TeamID` 
                        INNER JOIN  `team` t2 ON  `match`.`MatchAwayTeamID` = t2.`TeamID`
                        -- WHERE YEAR(`MatchDate`) =2017 
                        ORDER BY `MatchDate` ASC ");

$currentDate = false;
while($row = mysqli_fetch_array($result))
{                                

    if ($row['MatchDate'] != $currentDate){

        if ($currentDate != false){
            echo '</tbody>';
            echo '</table>'; 
             // /.table-responsive
            echo '</div>'; 
             // /.panel
            echo '</div>'; 
            // /.col-lg-4 
        }

        echo '<div class="col-lg-4">';
        echo '<div class="panel panel-default-fixtures">';
        echo '<div class="panel-heading">';
        echo '<h4 class="panel-title">';
        echo $row['MatchDate'];
        echo '</h4>';
        echo '</div>';                   
        echo '<table width="100%" class="table table-striped table-bordered table-hover dataTables">';
        echo '<thead>';
        echo '<tr>';
        echo '<th class="all">';
        echo 'Home';
        echo '</th>';
        echo '<th colspan="2">';
        echo 'Score';
        echo '</th>';
        echo '<th class="all">';
        echo 'Away';
        echo '</th>';
        echo '</tr>';
        echo '</thead>';
        echo '<tbody>';     
        $currentDate = $row['MatchDate']; 
    }                             

    if ($row['MatchHomeScore'] != null && $row['MatchAwayScore'] != null){
        echo '<tr class="success">';
    } else {
        echo '<tr>';
    }
    echo '<td>'.$row['HomeTeam'].'</td>';
    echo '<td>'.$row['MatchHomeScore'].'</td>';
    echo '<td>'.$row['MatchAwayScore'].'</td>';
    echo '<td>'.$row['AwayTeam'].'</td>';
    echo '</tr>';
    }

这个新代码现在输出:

Answer 1

检查一下。

 $currentDate = false;
    while($row = mysqli_fetch_array($result))
    {
        echo '<div class="col-lg-4">';
        echo '<div class="panel panel-default-fixtures">';

        if ($row['MatchDate'] != $currentDate){

        if($currentDate != false){
            echo '</tbody>';
            echo '</table>';
            echo '</div>';
            echo '</div>';
        }

        echo '<div class="panel-heading">';
        echo '<h4 class="panel-title">';
        echo $row['MatchDate'];
        echo '</h4>';
        echo '</div>';
        echo '<table width="100%" class="table table-striped table-bordered table-hover dataTables">';
        echo '<thead>';
        echo '<tr>';
        echo '<th class="all">';
        echo 'Home';
        echo '</th>';
        echo '<th colspan="2">';
        echo 'Score';
        echo '</th>';
        echo '<th class="all">';
        echo 'Away';
        echo '</th>';
        echo '</tr>';
        echo '</thead>';
        echo '<tbody>';
        $currentDate = $row['MatchDate'];
    }


    echo '<tr>';
    echo '<td>'.$row['HomeTeam'].'</td>';
    echo '<td>'.$row['MatchHomeScore'].'</td>';
    echo '<td>'.$row['MatchAwayScore'].'</td>';
    echo '<td>'.$row['AwayTeam'].'</td>';
    echo '</tr>';

}