PHP Mysql根据日期准备查询排序 - PHP Mysql prepare query sort according with date

PHP Mysql prepare query sort according with date

本文关键字：查询排序日期 Mysql PHP | 更新日期: 2023-09-27

试图根据prepare语句中的日期进行排序。

DISTINCT日期查询

$stmt = $conn->prepare('SELECT DISTINCT match_date FROM premier_league
                    WHERE match_date >= :current_date
                    AND pre_selected = :pre_selected
                    ORDER BY match_date LIMIT 5');

                $stmt-> execute(array('current_date' => $current_date,
                        'pre_selected' => $pre_selected));
                $row_count_date = $stmt->rowCount();
                    $row_match_date = $stmt->fetchAll();
                    foreach ($row_match_date as $row) {
                        echo $row['match_date']."<br>'n";
                    }

结果
2013-07-11
2013-07-12
2013-07-15

和查询基于DISTINCT的日期查询

$stmt = $conn->prepare('SELECT match_id, LEFT (match_time, 5)  match_time, 
                    home_team, away_team, pre_selected, my_choice FROM premier_league
                    WHERE match_date >= :match_date
                    AND pre_selected = :pre_selected
                    ORDER BY match_time, home_team LIMIT 5');
                $stmt-> execute(array('match_date' => $current_date,
                        'pre_selected' => $pre_selected));
                $row_count_match = $stmt->rowCount();
                    $row_match = $stmt->fetchAll();
                    foreach ($row_match as $row) {
                        echo $row['match_id']."<br>'n";
                    }

结果
680
681
682

我所看到的输出应该是以下格式。

2013-07-11
680
2013-07-12
681
2013-07-15
682
老办法我做这件事，但准备的声明令人困惑。。。

 $stmt = $conn->prepare('SELECT DISTINCT match_date FROM premier_league
                WHERE match_date >= :current_date
                AND pre_selected = :pre_selected
                ORDER BY match_date LIMIT 5');

            $stmt-> execute(array('current_date' => $current_date,
                    'pre_selected' => $pre_selected));
            $row_count_date = $stmt->rowCount();
                $row_match_date = $stmt->fetchAll();
                foreach ($row_match_date as $row) {
                    $dates[] = $row['match_date'];
                }

我已经创建了一个具有match_date的数组。现在，我将对下一个match_id:执行同样的操作

$stmt = $conn->prepare('SELECT match_id, LEFT (match_time, 5)  match_time, 
                home_team, away_team, pre_selected, my_choice FROM premier_league
                WHERE match_date >= :match_date
                AND pre_selected = :pre_selected
                ORDER BY match_time, home_team LIMIT 5');
            $stmt-> execute(array('match_date' => $current_date,
                    'pre_selected' => $pre_selected));
            $row_count_match = $stmt->rowCount();
                $row_match = $stmt->fetchAll();
                foreach ($row_match as $row) {
                    $ids[] = $row['match_id'];
                }

现在，我们有两个数组$ids（内部有match_id）和$date（内部有match_date）。因此，我们将按照我们喜欢的顺序显示。

      $i = 0;
      foreach($ids as $id) {
      echo $dates[$i];
      echo "<br/>";
      echo $id;
      echo "<br/>";
      $i++;   
      }

我希望这对你有帮助！