mysql - SELECT inside SELECT statement returns 0 count -


i have calendar there list of events in every day , use query count events in particular date. in database have start_time , end_time field user have schedule of materials specific. tried select events in day seems there's wrong in query. because user can borrow more 1 material stored in database same start_time , end_time well. want count data of user same start_time , tried group by seems doesn't work either. here database:

    ----table: schedule----     id    |   materialid  | borrowerid   | date_reserve | start_time | end_time|      -----------------------------------------------------------------------------------     9     |    7          |    bobi      | 2013-08-16   | 07:01:12  |  07:01:12|         10    |    10         |    bobi      | 2013-08-16   | 07:01:12  |  07:01:12|     11    |    12         |    bobi      | 2013-08-16   | 07:01:12  |  07:01:12|     12    |    7          |    sobi      | 2013-08-18   | 07:01:12  |  07:01:12|    ------------------------------------------------------------------------------------ 

here query:

    $cal_data = array();    for($i=1;$i<=31;$i++)     {     $date = "$year-$month-$i";     $this->db->select('(select count(id) count_s schedule date_reserve='.$date.' group start_time) count', false);     $this->db->select('date_reserve,borrowerid,start_time,end_time');     $this->db->from('schedule');         $this->db->where('date_reserve',"$year-$month-$i");      $this->db->group_by('start_time');     $query = $this->db->get();          foreach ($query->result() $row) {              $cal_data[$i] = ($row->count > 0) ? $row->count.' ' .'event(s)' : '';         } }    

so expected output be:

    count   |   date_reserve    | borrowerid   | start_time  | end_time     ---------------------------------------------------------------------------------------------------         1      |     2013-08-16     |        bobi       |  07:01:12  | 07:01:12 

in here there's big but in query give output. note: i'm using codeigniter.
used $this->output->enable_profiler(true); , try date 2013-08-16(because multiple selection) mysql on server , give me this.

    count   |   date_reserve    | borrowerid   | start_time  | end_time     ---------------------------------------------------------------------------------------------------      null   |     2013-08-16     |        bobi       |  07:01:12  | 07:01:12 

so think solution this?

is you're looking for?

select count(distinct concat(date_reserve, ' ', start_time)) my_count, date_reserve,borrowerid,start_time,end_time schedule borrowerid = 'bobi' 

Comments

Popular posts from this blog

Detect support for Shoutcast ICY MP3 without navigator.userAgent in Firefox? -

web - SVG not rendering properly in Firefox -

java - JavaFX 2 slider labelFormatter not being used -