phpdatecodeigniteractiverecordwhere-clause

CodeIgniter active record query to filter by month and year


I have a dropdown list which contain data as follows -

November 2016
December 2016

There is a filter button, after clicking on filter button I want to display data as per selected month and year. Date is stored in database table like-

2016-12-26 18:32:00

Here is what I have done so far:

ajax

$(document).on('click','#page_filter_btn',function(){
    var page_date_list=$('#page_date_list').val();
    $.ajax({
        url:base_url+'admin/disp_all_pages',
        type:'post',
        data:{page_date_list:page_date_list,action:'page_filter'},
        success:function(data){
            $('#disp_all_pages').html(data);
        }
    });
}); 

controller

public function disp_all_pages()
{
    $date = $this->input->post('page_date_list');
    $month = strtok($date,  ' ');
    echo $year = substr($date, strpos($date, " ") + 1); 
    $data = $this->admin_data->disp_filter_page($month,$year); //Send month and year to model 
}

Model

public function disp_filter_page($month,$year)
{  
    $this->db->select('p.post_type,p.ID, p.post_date, p.post_title, u.user_login');
    $this->db->from('sa_posts p,sa_terms t,sa_users u');
    $this->db->where('post_type','page');
    $this->db->where('u.ID=p.post_author');
    $this->db->group_by('p.ID');
    $query=$this->db->get();
    return $query->result();
} 

I just want to display data as per selected value in dropdown list.


Solution

  • First of all you need to convert month from string to numeric value like 11 instead of November. So code of disp_all_pages function should be like this:

    $date = "November 2016";
    $month = date('m', strtotime(strtok($date,  ' ')));
    $year = substr($date, strpos($date, " ") + 1);
    

    Now you can directly compare it with DB columns as answered by @Dannis:

    public function disp_filter_page($month,$year)
    {  
        $this->db->select('p.post_type,p.ID, p.post_date, p.post_title, u.user_login')
                ->from('sa_posts p,sa_terms t,sa_users u')
                ->where(array('post_type' => 'page', 'u.ID' => 'p.post_author', 'MONTH(p.post_date)' => $month, 'YEAR(p.post_date)' => $year))
                ->group_by('p.ID');
        $query = $this->db->get();
        return $query->result();
    } 
    

    Hope it will help.