title: Searching time range not working in php/mysql
body: <p>I have this two queries. My first query is to check the materials availability, and this is also the main problem that i'm encountering right now. It will select all the materials that has been reserved at a time.
  So I have this table date for  schedule_dummy table:
  Note: I'm using codeigniter. </p>
  
  <pre><code>   CREATE TABLE schedule_dummy
     (
          `materialID` int,
          `date_reserve` date,
          `start_time` time,
          `end_time` time
     );
  
                ------DB Table schedule_dummy --------
     materialID  |  date_reserve  |   start_time   | end_time   
     ----------------------------------------------------------
         7       |    2013-08-31   |  13:00:00     | 13:30:00    
         8       |    2013-08-31   |  13:00:00     | 14:00:00   
     ----------------------------------------------------------
  </code></pre>
  
  <p>My second query is to retrieve the materials where_not_in the id that has been retrieve from the first query. And I have this table date for materials table:</p>
  
  <pre><code>                  ------DB Table materials--------
         id     |    cid      |     mname         |   mdesc    
     ----------------------------------------------------------
         7      |     1       |     accer         |  acer   
         10     |     1       |     scanner       |  scanner
         12     |     1       |     prjector      |  scanner
     ----------------------------------------------------------
  </code></pre>
  
  <p>I've tried <a href="https://stackoverflow.com/questions/16298319/mysql-could-not-select-data-with-time-range">MySQL: could not select data with time range</a>.
  I've been through many research but still don't get what I needed. </p>
  
  <p>So this is the my whole function look like:</p>
  
  <pre><code>   function check_materials(){
      //$this-&gt;output-&gt;enable_profiler(TRUE);
  
      $start =  date("H:i:s", strtotime($this-&gt;input-&gt;post('start')));
      $end =  date("H:i:s", strtotime($this-&gt;input-&gt;post('end')));
      $date_reserve =  $this-&gt;input-&gt;post('date_reserve');
      $id =  $this-&gt;input-&gt;post('id');
  
      $sql = "SELECT id, materialID FROM schedule_dummy WHERE date_reserve = ? AND time(?) BETWEEN start_time AND end_time AND time(?) BETWEEN start_time AND end_time";      
      $query = $this-&gt;db-&gt;query($sql,array($date_reserve,$start,$end));
  
      $ids = array();
      if($query-&gt;num_rows() &gt; 0 ){
          foreach($query-&gt;result() as $rows){
              $ids[] = $rows-&gt;materialID;
          }
      }
  
      $data = array();
      $n = count($ids);
  
      $idz = array();
      if($n &gt; 0){
          $q = $this-&gt;db-&gt;select('*')-&gt;where_not_in('id',$ids)-&gt;where('cid', $id)-&gt;get('materials');
  
          if($q-&gt;num_rows() &gt; 0){
              foreach($q-&gt;result() as $row){
                  $data[] = $row;
              }
          }
          return $data;
      }else{
          $q = $this-&gt;db-&gt;select('*')-&gt;where('cid',$id)-&gt;get('materials');
  
          if($q-&gt;num_rows() &gt; 0 ){
              foreach($q-&gt;result() as $row){
                  $data[] = $row;
              }
          }
          return $data;
      }
  
  }
  </code></pre>
  
  <p>In this query the problem is when I select the <code>start_time = 13:30:00</code> to <code>end_time = 14:00:00</code> then the data from schedule_dummy with the <code>materialID</code> of <code>8</code> doesn't retrieve. Since the start_time and end_time is from <code>13:00:00</code> to <code>14:00:00</code>.</p>
  
  <p>And also when I try this: </p>
  
  <pre><code>$sql = "
  SELECT materialID 
  FROM schedule_dummy 
  WHERE 
    date_reserve = ? 
   AND start_time &gt;= time(?) AND end_time &lt;= time(?)"; 
  </code></pre>
  
  <p>When I select the same input it doesn't retrieve the data from schedule_dummy.
  So what do you think where i'm having mistake in here? Does my logic is wrong? Or any of my queries? Thanks.</p>