从日期中选择记录，忽略不工作的时间部分

Question

我有一个奇怪的情况。我使用内置的calendar类创建了一个带有codeigniter的日历。只要抓取事件并将其放在日历上，一切都可以工作，所以我知道选择日期是有效的……但现在我试图选择记录落在一个特定的日期，如2014-03-28，但无论我使用什么查询它不是从那天拉记录。我有一个表和start_date是我试图拉的日期，其设置为dateTime。知道我做错了什么吗，这是我尝试过的(start_date被馈送到函数等于Y-m-d。像2014-03-28):

function get_list_events($start_date) {
    $start_date_start = date('Y-m-d', strtotime($start_date.' 00:00:00'));
    $start_date_end = date('Y-m-d', strtotime($start_date.' 23:59:59'));
    $this->db->where("start_date BETWEEN '$start_date_start%' AND '$start_date_end%'", NULL, FALSE);
    $query = $this->db->get('events');
    $data = array();
    foreach ($query->result() as $row) {
    $data[] = array(
        'id' => $row->id,
        'title' => $row->title,
        'description' => $row->description,
        'cost' => $row->cost,
        'image' => $row->image,
        'start_date' => $row->start_date,
        'end_date' => $row->end_date,
        'venue' => $row->venue,
        'venue_address' => $row->venue_address,
        'venue_city' => $row->venue_city,
        'venue_state' => $row->venue_state,
        'venue_zipcode' => $row->venue_zipcode,
        'contact_name' => $row->contact_name,
        'contact_email' => $row->contact_email,
        'contact_phone' => $row->contact_phone,
        'contact_website' => $row->contact_website,
        'create_date' => $row->create_date,
        'active' => $row->active,
    );
    }
    return $data;
}

也尝试了最明显的方法:

function get_list_events($start_date) {
    $start_date = date('Y-m-d', strtotime($start_date));
    $this->db->where('active', 1);
    $this->db->where('start_date', $start_date);
    $this->db->order_by('start_date', 'desc');
    $query = $this->db->get('events');
    $data = array();
    foreach ($query->result() as $row) {
    $data[] = array(
        'id' => $row->id,
        'title' => $row->title,
        'description' => $row->description,
        'cost' => $row->cost,
        'image' => $row->image,
        'start_date' => $row->start_date,
        'end_date' => $row->end_date,
        'venue' => $row->venue,
        'venue_address' => $row->venue_address,
        'venue_city' => $row->venue_city,
        'venue_state' => $row->venue_state,
        'venue_zipcode' => $row->venue_zipcode,
        'contact_name' => $row->contact_name,
        'contact_email' => $row->contact_email,
        'contact_phone' => $row->contact_phone,
        'contact_website' => $row->contact_website,
        'create_date' => $row->create_date,
        'active' => $row->active,
    );
    }
    return $data;
}

最后我试了这个:

function get_list_events($start_date) {
    $start_date = date('Y-m-d H:i:s', strtotime($start_date.' 00:00:00'));
    $start_time = date('Y-m-d H:i:s', strtotime($start_date.' 23:59:59'));
    $this->db->where('active', 1);
    $this->db->where('start_date >=', $start_date);
    $this->db->where('start_date <=', $start_time);
    $this->db->order_by('start_date', 'desc');
    $query = $this->db->get('events');
    $data = array();
    foreach ($query->result() as $row) {
    $data[] = array(
        'id' => $row->id,
        'title' => $row->title,
        'description' => $row->description,
        'cost' => $row->cost,
        'image' => $row->image,
        'start_date' => $row->start_date,
        'end_date' => $row->end_date,
        'venue' => $row->venue,
        'venue_address' => $row->venue_address,
        'venue_city' => $row->venue_city,
        'venue_state' => $row->venue_state,
        'venue_zipcode' => $row->venue_zipcode,
        'contact_name' => $row->contact_name,
        'contact_email' => $row->contact_email,
        'contact_phone' => $row->contact_phone,
        'contact_website' => $row->contact_website,
        'create_date' => $row->create_date,
        'active' => $row->active,
    );
    }
    return $data;
}

Answer 1

我的建议是使用

// ... Code above ...
$query = $this->db->get('events');
echo $this->db->last_query();
die();

并获得你对DDBB抛出的查询。这样，您就可以在MySQL客户端上抛出查询，并查看您是否真的使用CI正在使用的查询获得行。

事实上，你写的第一个代码块看起来很好，其他代码块有一个$this->db->where('active', 1);，第一个没有，所以，再次检查查询，XD。