CODEWITHSUNDEEP
mysqlsqlselectgroup-by
leonardeveloper
leonardeveloper

Reputation: 1853

SELECT inside SELECT statement returns 0 count

I have this calendar where there is a list of events in every day and I use this query to count the events in a particular date. In my database I have start_time and end_time field where the user have a schedule of materials to be specific. I tried to select the events in that day but it seems there's something wrong in my query. Because the user can borrow more than one material so it will be stored in the database with the same start_time and end_time as well. What I want is to count all the data of the user with the same start_time and I tried GROUP BY but it seems doesn't work either. Here is my 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 is my query:

    $cal_data = array();
   for($i=1;$i<=31;$i++)
    {
    $date = "$year-$month-$i";
    $this->db->select('(SELECT COUNT(id) as count_s FROM schedule WHERE date_reserve='.$date.' GROUP BY start_time) as 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() as $row) {

            $cal_data[$i] = ($row->count > 0) ? $row->count.' ' .'event(s)' : '';
        }
}

So the expected output with the will be: 

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

In here there's a big BUT where in that query it will give you this output. Note: I'm using CodeIgniter.
I used $this->output->enable_profiler(TRUE); and try the date 2013-08-16(because it is multiple selection) to MySQL on my server and give me this.

    count   |   date_reserve    | borrowerID   | start_time  | end_time
    ---------------------------------------------------------------------------------------------------
     NULL   |     2013-08-16     |        bobi       |  07:01:12  | 07:01:12

So what do you think the solution for this?

Upvotes: 1

Views: 290

Answers (2)

DeiForm
DeiForm

Reputation: 664

Based on fancyPants. Query for CI:

$this->db->select('count(distinct concat(date_reserve, " ", start_time)) as my_count)', FALSE);
$this->db->select('date_reserve,borrowerID,start_time,end_time');

Upvotes: 1

fancyPants
fancyPants

Reputation: 51888

Is this what you're looking for?

select
count(distinct concat(date_reserve, ' ', start_time)) as my_count,
date_reserve,borrowerID,start_time,end_time
from
schedule
where borrowerID = 'bobi'

Upvotes: 1

Related Questions