CODEWITHSUNDEEP
phpmysqlcodeigniterdatetime
Jordan
Jordan

Reputation: 733

Duration Overlap Check in PHP CodeIgniter

Let's suppose I have a record in database which has a start DateTime: 2016-03-10 00:00:00 and end DateTime 2016-03-10 06:00:00. Now each time i insert a new record in the database I want to check that the start DateTime & End DateTime are such that the duration never overlaps the previous records present in the Database. Is there any built-in function present in PHP CodeIgniter to check this?

Right now I am doing it using the following code but it doesn't work:

public function check_b_adds($data){
 $sql='select * from listing_b_screen where (start_time >= ? AND end_time >= ?) OR (start_time <= ? AND end_time <= ?) OR (start_time <= ? AND end_time >= ?) OR (start_time = ? AND end_time = ?) OR (start_time >= ? AND end_time <= ?)';
    $query=$this->db->query($sql, array($data['mystart_time'],$data['myend_time'],$data['mystart_time'],$data['myend_time'],$data['mystart_time'],$data['myend_time'],$data['mystart_time'],$data['myend_time'],$data['mystart_time'],$data['myend_time']));
    $result = $query->result_array();
    if ($result == NULL || $result == 0){
        return false;
    } else {
        return True;
    }
}

Upvotes: 0

Views: 613

Answers (2)

kuldeep
kuldeep

Reputation: 318

    $fromdaysDate="2016-07-13";//changed date
    $todaysDate="2016-07-25";//changed date
    $this->db->select('task_id,task_start_date, task_due_date'); 
    $this->db->where('task_start_date <="'.date('Y-m-d', strtotime($fromdaysDate)).'"');
    $this->db->where('task_due_date >="'.date('Y-m-d', strtotime($todaysDate)).'"');    
    $this->db->or_where('task_due_date BETWEEN "'. date('Y-m-d', strtotime($fromdaysDate)). '" and "'. date('Y-m-d', strtotime($todaysDate)).'"');   
    $alltask=$this->db->get('wba_task')->result_array();
    echo $this->db->last_query();

Upvotes: 1

Jaaaaaaay
Jaaaaaaay

Reputation: 1975

Don't know what your db table looks like, but below is an example:

public function check_b_adds($data){
    //I suppose your start_time and end_time are stored as datetime type in db like 2016-03-01 07:30:00
    $myStart_time = $data['mystart_time']; //for example, this is a string like 2016-03-01 07:30:00 in the format of %Y-%m-%d %H:%i:%s
    $myEnd_time   = $data['myEnd_time'];   //for example, this is a string like 2016-03-01 09:30:00 in the format of %Y-%m-%d %H:%i:%s

    $query = $this->db("
                          select * 
                          from listing_b_screen 
                          where id not in
                                      (
                                       select id
                                       from listing_b_screen 
                                       where start_time >  STR_TO_DATE('$myEnd_time', '%Y-%m-%d %H:%i:%s')
                                       or end_time      <  STR_TO_DATE('$myStart_time', '%Y-%m-%d %H:%i:%s')                                          
                                      )
                       ");      

    $result = $query->result_array();
    if ($result == NULL || $result == 0){
        return false; //not overlap
    } else {
        return true;
    }
}

Notice that your logic is wrong. For example, the start_time and end_time in db is 3rd and 7th, the start_time and end_time in your array is 1st and 2nd. This is not overlap. but your logic start_time >= ? AND end_time >= ? consider it is overlap. Try to use "not in" in the query

Upvotes: 0

Related Questions