comparing month and year with database table

Question

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.

@Asif data is stored in page_date_list as November 2016. After that I made separation of month name and year — amM, Dec 27 '16 at 13:32
get the first and last day of the month [http://stackoverflow.com/questions/2680501/how-can-i-find-the-first-and-last-date-in-a-month-using-php] and use between query to get data between these two dates. — Asif Rao, Dec 27 '16 at 13:38

score 3 · Accepted Answer · answered Dec 27 '16 at 13:32

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.

Can you please share piece of data stored in DB? I want to see how it returns null. — d.coder, Dec 27 '16 at 13:48

score 1 · Answer 2 · answered Dec 27 '16 at 13:16

1

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

answered Dec 27 '16 at 13:16

pixelarbeit

484
2
11

@ Dennis Thanks for answer, I include both where conditions but it returns null. Date is stored like 2016-12-26 18:32:00 ,so how month() and year() function will work? – amM Dec 27 '16 at 13:24
1

@Deepak Please check query again. Dennis is right it should work. – Nilesh Daldra Dec 27 '16 at 14:38
@Deepak Is the date stored as DATETIME or as VARCHAR? – pixelarbeit Dec 27 '16 at 17:55
@Dennis date stored as datetime. – amM Dec 28 '16 at 06:21
`MONTH()` and `YEAR()` are the right functions. Did you check your query without both? Only with `YEAR()`? Did you check the values of `$month` and `$year` and are they numerical? Is `$month` ranging from 1 to 12? Maybe try with an easier query and add more and more details to find the error: `$this->db->query("SELECT * FROM sa_posts WHERE YEAR(post_date) = '" . $year. "'")` – pixelarbeit Dec 28 '16 at 08:35

comparing month and year with database table

2 Answers2