title: What is the proper algorithm to trace time conflict in php/mysql?
body: <p>I'm working with a time scheduling project where I can schedule materials by time and I am really really confused on how can I trace the time conflict within a time range. I worked with many(I think) algorithms but still no luck. My brain is saturated that's why I ask anyone here who can help me.<br /></p>
  
  <p>So I have these data in my database:</p>
  
  <pre><code>    CREATE TABLE schedule_dummy
      (
      `materialID` int,
      `date_reserve` date,
      `start_time` time,
      `end_time` time
      );
  
     ------DB Table schedule_dummy --------
     material |        date     |   start_time  | end_time   
      ----------------------------------------------------------
      7       |    2013-08-01   |  13:00:00     | 14:00:00    
     10       |    2013-09-01   |  13:30:00     | 14:00:00    
      ---------------------------------------------------------- 
  </code></pre>
  
  <p>I used this data to check the availability of a certain material in a particular time. And so if the value of <code>start_time</code> and<code>end_time</code> is <code>equal to</code> or <code>in between</code> those time in the database the material will not be available. I tried so many query but doesn't work with many cases.<br /> Here are some queries that i've tried so far.</p>
  
  <pre><code>   $sql = "SELECT materialID
            FROM schedule_dummy WHERE (date_reserve = ?)
            AND (? BETWEEN start_time AND end_time
            OR ? BETWEEN start_time AND end_time)";
  </code></pre>
  
  <p>And</p>
  
  <pre><code>   //I've tried this query but in some cases doesn't work dunno if I'm missing something.
      $sql = "SELECT * FROM schedule_dummy
          WHERE date_reserve = ?
          AND start_time &gt;=? AND start_time &lt;=?";
  </code></pre>
  
  <p>And here's my php code Note:I'm using codeigniter:</p>
  
  <pre><code>   $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 materialID FROM schedule_dummy WHERE (date_reserve = ?) AND (? BETWEEN start_time AND end_time OR ? BETWEEN start_time AND end_time)";
      $sql = "SELECT * FROM schedule_dummy WHERE date_reserve = ? AND start_time &gt;=? AND end_time &lt;=?";
      $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;
                  }
              }
  
          }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>So could anyone who knows some algorithm to trace the time conflict? So that I can retrieve those available materials in my database? Thanks!</p>