php - 按日期控件中的日期过滤

Question

$filter .="<option value='ALL' selected=selected> All Months  - [" . $allcnt . "]  </option>";

$query = "select LockerInfo_IDNo, ExpiryDate, count(LockerInfo_IDNo) count1 from lockerinfo  
          WHERE ExpiryDate<>'' 
          group by MONTH(ExpiryDate) 
          order by ExpiryDate ";

$result = mysql_query($query) or die('101. Query failed: ' . mysql_error());
while ($row=mysql_fetch_array($result)){

    if($mnt==date("d-m-Y",$row['ExpiryDate']))
        $filter .="<option value='" . $row['ExpiryDate'] . "' selected=selected> " . $space1 . date("M",$row['ExpiryDate']) . " - [" . $row['count1'] . "]</option>";
    else
        $filter .="<option value='" . $row['ExpiryDate'] . "'> " . $space1 . date("M",$row['ExpiryDate']) . " - [" . $row['count1'] . "]</option>";
}


$filter .=" </select>";
echo $filter;

这是我在此列表框中的查询，值应该按月分组...如果使用此查询意味着它只显示 2 条记录...但我在数据库中有 535 个值...帮助我

Answer 1

尝试重写查询并查看结果:

      select YEAR(ExpiryDate) as expireYear, MONTH(ExpiryDate) as expireMonth, count(LockerInfo_IDNo) count1 
      from lockerinfo  
      WHERE ExpiryDate<>'' 
      group by YEAR(ExpiryDate) , MONTH(ExpiryDate) 
      order by 1, 2

PHP 脚本例如:

$resultSet = [];
/* uncomment to use real data 
    while ($row=mysql_fetch_array($result))
          $resultSet[] = $row;
 */
// Example data start
$resultSet[] = ['expireYear' => '2013', 'expireMonth' => '01', 'count1' => 12]; 
$resultSet[] = ['expireYear' => '2013', 'expireMonth' => '05', 'count1' => 20]; 
$resultSet[] = ['expireYear' => '2013', 'expireMonth' => '11', 'count1' => 30]; 
// Example data end 
$currentMonth = date('Ym'); 
$space1 = "  ";
foreach ($resultSet as $row ){
   $ym = $row['expireYear'].$row['expireMonth'];
   $text = date("Y M", mktime(0, 0, 0, $row['expireMonth'], 1, $row['expireYear'])) . " - [".$row['count1']."]";
   $filter .= "<option value='{$ym}' ".($currentMonth == $ym ? 'selected' : '')."> " . $space1 .  $text . "</option>";
}
echo "<select>".$filter."</select>";