CodeIgniter: Query grouping and Looking for Specific Data

First of all I am new with CodeIgniter and still in the process of learning.

I have created an app on React Native that selects date and time to create an appointment. I am using CodeIgniter as my back-end. The appointment has a date, start time, and end time.

The conditions for creating appointment are:

If an appointment is already created, such as: date = 15-01-2020, start time = 7:00pm, end time = 8:00pm.

Then user CANNOT create another appointment such as:

1. date = 15-01-2020, start time = 7:00pm, end time = 8:00pm (similar date and time since 7pm to 8pm already booked)

2. date = 15-01-2020, start time = 7:20pm, end time = 7:40pm (overlapping inside already booked appointment)

3. date = 15-01-2020, start time = 7:45pm, end time = 8:45pm (start time overlapping with already booked appointment)

4. date = 15-01-2020, start time = 6:45pm, end time = 7:45pm (end time overlapping with already booked appointment)

5. date = 15-01-2020, start time = 6:00pm, end time = 9:00pm (overlapping with already booked appointment)

Also, user should not be able to create appointment with time such as: start time = 8:00pm, end time = 7:00pm (where start time > end time)

From the above conditions, I have been able to achieve only condition no.1. However, there’s another problem I faced, where If start time = 7:00pm to end time = 8:00pm is booked, I CANNOT create from start time = 8:00pm to end time = 9:00pm. Code snippet provided below: This is the function I have created (with the help of a friend) on the back-end MODEL:

public function check_datetime_range($where)
    {
        $this->db->where('freelancer_id', $where['freelancer_id']);
        $this->db->where("DATE_FORMAT(app_date_start, '%Y-%m-%d') = ", date('Y-m-d', strtotime($where['app_date_start'])));
        $this->db->group_start();
            $this->db->group_start();
                $this->db->where('app_date_start >=', $where['app_date_start']);
                $this->db->where('app_date_end <=', $where['app_date_end']);
            $this->db->group_end();

            $this->db->or_group_start();
                $this->db->where('app_date_start >=', date('Y-m-d H:i:s', strtotime($where['app_date_start'])-3600));
                $this->db->group_start();
                    $this->db->where('app_date_end <=', $where['app_date_start']);
                    $this->db->or_where('app_date_end <=', $where['app_date_end']);
                $this->db->group_end();
            $this->db->group_end();
        $this->db->group_end();

        return $this->db->get($this->_table_name)->num_rows();
    }



  //the code below are from the CONTROLLER. 
//Information for: app_date_start  and app_date_end (it contains both date and time).
            /*$start = new DateTime($this->post('app_date') . ' ' . $this->post('start_time'));
            $end = new DateTime($this->post('app_date') . ' ' . $this->post('end_time'));
            $save['app_date_start'] = $start->format('Y-m-d H:i:s');
            $save['app_date_end'] = $end->format('Y-m-d H:i:s');*/

Any help would be appreciated since these are all a bit overwhelming at the moment.

Answer

Solved:

public function check_datetime_range($where)
{
    $this->db->where('freelancer_id', $where['freelancer_id']);
    $this->db->where("DATE_FORMAT(app_date_start, '%Y-%m-%d') = ", date('Y-m-d', strtotime($where['app_date_start'])));

    $this->db->group_start();
    //  Example: 
    //  start time = 7:00pm, end time = 8:00pm, date = 15-01-2020 (Existing Appointment)

    //1. Prevents overlapping time with an existing appointment
    //  start time = 7:00pm, end time = 8:00pm, date = 15-01-2020
    //  OR
    //  start time = 6:00pm, end time = 9:00pm, date = 15-01-2020
        $this->db->group_start();
            $this->db->where('app_date_start >=', $where['app_date_start']);
            $this->db->where('app_date_end <=', $where['app_date_end']);
        $this->db->group_end();

    //2. Prevents overlapping time from within an existing appointment
    //  start time = 7:15pm, end time = 7:45pm, date = 15-01-2020
        $this->db->or_group_start();
            $this->db->where('app_date_start <=', $where['app_date_start']);
            $this->db->where('app_date_end >=', $where['app_date_end']);
        $this->db->group_end();

    //3. Prevents overlapping end time inside an existing appointment
    //  start time = 6:45pm, end time = 7:45pm, date = 15-01-2020
        $this->db->or_group_start();
            $this->db->where('app_date_start >', $where['app_date_start']);
            $this->db->where('app_date_start <', $where['app_date_end']);
            $this->db->where('app_date_end >', $where['app_date_end']);
        $this->db->group_end();

    //4. Prevents overlapping start time inside an existing appointment
    //  start time = 7:45pm, end time = 8:45pm, date = 15-01-2020
        $this->db->or_group_start();
            $this->db->where('app_date_start <', $where['app_date_start']);
            $this->db->where('app_date_start <', $where['app_date_end']);
            $this->db->where('app_date_end >', $where['app_date_start']);
            $this->db->where('app_date_end <', $where['app_date_end']);
        $this->db->group_end();

    //5. Prevents start time > end time
    // start time = 7:00pm, end time = 6:00pm
        $this->db->or_group_start();
            $this->db->where('app_date_start >', $where['app_date_end']);
        $this->db->group_end();

    $this->db->group_end();

    return $this->db->get($this->_table_name)->num_rows();
}