如何计算日期以制作事件列表？

I'm using Codeigniter V2.1.3 and I have a calendar template that have a scheduling stuff and I want to count the events in a particular date. Here is my database:

我正在使用Codeigniter V2.1.3,我有一个日程模板,有一个调度的东西,我想计算特定日期的事件。这是我的数据库:

              ----Table: schedule -----
   id  | materialID  | borrowerID | date_reserve |
   ------------------------------------------------
   1   |     7       |     7      |  2013-08-16  |
   2   |     10      |     10     |  2013-08-16  |
   1   |     12      |     13     |  2013-08-18  |

What I want is in my calendar template the total event for the date=2013-08-16 will be 2 events. Here is my code which is not working coz it keeps on sending me only 1 event maybe you could figure out where is my mistake in here:

我想要的是在我的日历模板中,日期= 2013-08-16的总事件将是2个事件。这是我的代码,这是不工作因为它继续发送给我只有一个事件也许你可以弄清楚我在哪里错误:

    $query = $this->db->select('*')->from('schedule')->like('date_reserve', "$year-$month")->get();
    $cal_data = array();
    foreach ($query->result() as $row) {
        $index = ltrim(substr($row->date_reserve,8,2), '0'); 
        $cal_data[$index] = count($row->borrowerID). 'event(s)';
    }
    return $cal_data;

Any help?

2 个解决方案

#1

If you want number of events for exact date: YYYY-MM-DD you can replace like with where:

如果您想要确切日期的事件数量:YYYY-MM-DD您可以替换为:

$r = $this->db->select('id, materialID, borrowerID, date_reserve')
              ->where('date_reverse', $year."-".$month."-".$day)
              ->get('schedule')
              ->result();

Also you can print_r the result so you will see what the result is. Also you can echo last query: echo $this->db->last_query();

您也可以print_r结果,这样您就可以看到结果。你也可以回显最后一个查询:echo $ this-> db-> last_query();

Edit: We had to run query per each date to fetch the number of events.

编辑:我们必须每个日期运行查询以获取事件数。

$cal_data = array();
    for($i=1;$i<=31;$i++)
    {
        $this->db->select('COUNT(id) as count,date_reserve,borrowerID');
    $this->db->from('schedule');    
    $this->db->where('date_reserve',"$year-$month-$i"); 
    //$this->db->group_by('date_reserve');    
    $query = $this->db->get();


    foreach ($query->result() as $row) {
        $cal_data[$index] = $row->count. 'event(s)';
    }

#2

try using group_by

尝试使用group_by

public function get_results($date) {   

    $this->db->select('COUNT(id),date_reserve');
    $this->db->from('schedule');

    $this->db->like('date_reserve',$date);

    $this->group_by('date_reserve');    
    $result = $this->db->get();

    return $result->result_array();

}

#1