php - 使用多个循环呈现来自 SQL 的数据时出现问题

Question

我已经尝试了 24 小时来完成这项工作，现在感觉我已经接近成功了!

我想从 MySQL 获取日期(column:datum)和位置(column:plats)，我的代码如下:

<?php

$sql = "SELECT datum FROM gigs GROUP BY DATE_FORMAT( datum, '%Y' ) ORDER BY datum DESC";
$result = mysql_query($sql);

 while($r = mysql_fetch_array($result)) {

  $date = $r['datum'];
  $date_new = new DateTime($date);
  $year = $date_new->format('Y');
  $month = $date_new->format('M');
  $day = $date_new->format('d');

  $sql2 = "SELECT * FROM gigs WHERE DATE_FORMAT( datum, '%Y' ) = $year ORDER BY datum ASC";
  $result2 = mysql_query($sql2);

  echo "<tr height=20px><td></td><td align=center><b>".$year."</b></td></tr>";

  echo "<tr><td><b>".$month."</b></td></tr>";

  while($r2 = mysql_fetch_array($result2)) {

   $date2 = $r2['datum'];
   $date_new2 = new DateTime($date2);
   $year2 = $date_new2->format('Y');
   $month2 = $date_new2->format('M');
   $day2 = $date_new2->format('j');

 //echo "<b>Month: ".$month."</b>";
 //echo "<b>Month2: ".$month2."</b>";

    if($month != $month2) {
     echo "<tr><td>&nbsp;</td></tr><tr><td><b>".$month2."</b></td></tr>";
    }

    $month = $month2;

    echo "<tr class=giglist><td>".$day2."</td><td>".$r2['plats']."</td></tr>"; 



  }
    echo "</td></tr>";
 }

?>

这给了我:

2011
Jan
1. Location
2. Location
3. Location
...

Feb
1. Location
2. Location
3. Location
...
...
Dec
1. Location
2. Location
3. Location
...

2010
Dec

Jan
1. Location
2. Location
3. Location
...
Feb
1. Location
2. Location
3. Location
...
...
Dec
1. Location
2. Location
3. Location
...

我不想在“2010”下写上额外的 12 月...

我希望有人可以提供帮助，因为我迷失在自己的代码中 xD 谢谢!

Answer 1

你的代码太复杂了，你只需一个查询就可以做到这一点，请参阅此代码

<?php 
$sql = 'SELECT
            *, 
            DATE_FORMAT( datum, "%Y" ) "year", 
            DATE_FORMAT( datum, "%M" ) "month", 
            DATE_FORMAT( datum, "%e" ) "day" 
        FROM gigs
        ORDER BY year DESC, month DESC, day ASC';

$year = null;
$month = null;
$stmt = mysql_query($sql);
while($row = mysql_fetch_array($result)) {
    if ($year != $result['year']) {
        // Year changed
        $year = $result['year'];
        echo "<tr height=20px><td></td><td align=center><b>".$year."</b></td></tr>";
    }
    if ($month != $result['month']) {
        // Month changed
        $month = $result['month'];
        echo "<tr><td>&nbsp;</td></tr><tr><td><b>".$month."</b></td></tr>";
    }
    echo "<tr class=giglist><td>".$result['day']."</td><td>".$row['plats']."</td></tr>"; 
}