CODEWITHSUNDEEP
phpcodeigniter
amM
amM

Reputation: 529

comparing month and year with database table

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.
Thanks.

Upvotes: 0

Views: 153

Answers (2)

d.coder
d.coder

Reputation: 2038

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.

Upvotes: 3

pixelarbeit
pixelarbeit

Reputation: 494

There's a MONTH() and a YEAR() function in MySQL. Try adding this:

 $this->db->where('MONTH(p.post_date)', $month);
 $this->db->where('YEAR(p.post_date)', $year);

Upvotes: 1

Related Questions