php - MYSQL 请求 |按年月分组日期

Question

我的问题是:我有一个 mysql 表:事件并希望获得特殊格式的 json_string。

    id   event
    1    2010-01-01 00:00:00 
    2    2010-07-02 00:00:00 
    3    2011-01-04 00:00:00 
    4    2012-01-30 00:00:00 
    5    2012-03-15 00:00:00 
...

我需要获取 json 字符串:

{"events":[
   {"2010":
      {
       "1":{"id":1,"event":2010-01-01 00:00:00},
       "7":{"id":2,"event":2010-07-02 00:00:00}
      },
   },
   {"2011:
      {
       "1":{"id":3,"event":2012-01-30 00:00:00}
      },
   },
   {"2012:
      {
       "1":{"id":4,"event":2011-01-04 00:00:00},
       "3":{"id":5,"event":2012-03-15 00:00:00},
      },
   }
]}

我的代码如下:

$result = DB::query('SELECT id, event FROM events');
 $events = array();
 while($event = $result->fetch_object()) {
     // the following statement won't get desired results
     $events[] = $event;
 }

 return array(
    'events' => json_encode($events);
 ); 

如何更改我的代码以获取上述 JSON 字符串？

Answer 1

只需 substr() 事件日期中您需要的部分，并使用它来构建多维数组:

while($event = $result->fetch_object()) {
    $year = substr($event->event, 0, 4);
    $month = (int)substr($event->event, 5, 2);

    $events[$year][$month][] = $event;
}