在php/mysql中跟踪时间冲突的正确算法是什么？

Question

我正在做一个时间安排项目，我可以按时间安排材料，我真的很困惑如何才能在一个时间范围内跟踪时间冲突。我使用了许多(我认为)算法，但仍然没有成功。我的大脑是饱和的，这就是为什么我向这里的任何人寻求帮助。

因此，我的数据库中有以下数据：

    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    
    ---------------------------------------------------------- 

我使用这些数据来检查特定时间内特定材料的可用性。因此，如果start_time和end_time的值在数据库中的时间为equal to或in between，则材料将不可用。我尝试了这么多查询，但在很多情况下都不起作用。

以下是我到目前为止尝试过的一些查询。

   $sql = "SELECT materialID
          FROM schedule_dummy WHERE (date_reserve = ?)
          AND (? BETWEEN start_time AND end_time
          OR ? BETWEEN start_time AND end_time)";

和

   //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 >=? AND start_time <=?";

这是我的php代码注意:我使用的是codeigniter：

   $start =  date("H:i:s", strtotime($this->input->post('start')));
    $end =  date("H:i:s", strtotime($this->input->post('end')));
    $date_reserve =  $this->input->post('date_reserve');
    $id =  $this->input->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 >=? AND end_time <=?";
    $query = $this->db->query($sql,array($date_reserve,$start,$end));

    $ids = array();
    if($query->num_rows() > 0 ){
        foreach($query->result() as $rows){
            $ids[] = $rows->materialID;
        }
    }

    $data = array();
    $n = count($ids);

    $idz = array();
        if($n > 0){
            $q = $this->db->select('*')->where_not_in('id',$ids)->where('cid', $id)->get('materials');

            if($q->num_rows() > 0){
                foreach($q->result() as $row){
                    $data[] = $row;
                }
            }

        }else{
            $q = $this->db->select('*')->where('cid',$id)->get('materials');

            if($q->num_rows() > 0 ){
                foreach($q->result() as $row){
                    $data[] = $row;
                }
            }

        }
        return $data;

那么，知道一些算法来追踪时间冲突的人会吗？这样我就可以在我的数据库中检索这些可用的材料了？谢谢!

Answer 1

如果您想要查看某个项目是否在某个日期和特定时间内使用，则可以使用以下命令：

select
    material
from
    schedule_dummy
where
    date_reserve=checkDate
    and start_time <= checkTime
    and end_time >= checkTime

Answer 2

$qry = "SELECT * FROM schedule WHERE day='$day' AND starttime>='$starttime' AND endtime<'$endtime'";
        $result = mysql_query($qry);
        if($result) {
            if(mysql_num_rows($result) > 0) {
                $errmsg_arr[] = 'The Room or Time Schedule is  already exist in the database';
                $errflag = true;
            }
            @mysql_free_result($result);
        }
        else {
            die("Query failed");
        }


    //If there are input validations, redirect back to the registration form
    if($errflag) {
        $_SESSION['ERRMSG_ARR'] = $errmsg_arr;
        session_write_close();
        echo "<script>alert('The Room or Time Schedule is  already exist in the database.');history.back();</script>";
        exit();
    }