时间戳

首页 > 解决方案 > CodeIgniter：查询分组和查找特定数据

php - CodeIgniter：查询分组和查找特定数据

问题描述

首先，我是 CodeIgniter 的新手，并且仍在学习中。

我在 React Native 上创建了一个应用程序，它选择日期和时间来创建约会。我使用 CodeIgniter 作为我的后端。约会有date、start time和end time。

创建预约的条件是：

如果已创建约会，例如：date = 15-01-2020、start time = 7:00pm、end time = 8:00pm。

然后用户不能创建另一个约会，例如：

  1. date = 15-01-2020, start time = 7:00pm, end time = 8:00pm（类似的日期和时间从晚上 7 点到 8 点已经预订）

  2. date = 15-01-2020, start time = 7:20pm, end time = 7:40pm（在已预约的预约内重叠）

  3. date = 15-01-2020, start time = 7:45pm, end time = 8:45pm(开始时间与已预约的时间重叠)

  4. date = 15-01-2020, start time = 6:45pm, end time = 7:45pm(结束时间与已预约的时间重叠)

  5. date = 15-01-2020, start time = 6:00pm, end time = 9:00pm（与已预约的约会重叠）

此外，用户不应该能够创建与时间的约会，例如：start time = 8:00pm，end time = 7:00pm（其中start time > end time）

根据上述条件，我只能达到条件 no.1。但是，我遇到了另一个问题，如果预订了start time = 7:00pmto end time = 8:00pm，我无法从start time = 8:00pmto创建end time = 9:00pm。下面提供的代码片段： 这是我（在朋友的帮助下）在后端 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');*/

任何帮助将不胜感激，因为目前这些都有点压倒性。

标签： phpcodeigniter

解决方案

解决了：

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();
}

推荐阅读