Apr 24
How to select data with month and year in MySQL
One of the easier ways to retrieve data from MySQL by month and year is to use one of MySQL’s built-in functions - MONTH() and YEAR(). The following is a piece of CakePHP code.
function p_month($year, $month) {
$posts = $this->paginate(
‘Post’,
"status=’approved’ AND MONTH(pub_date)=$month AND YEAR(pub_date)=$year"
);
$this->set(compact(‘posts’));
}
$posts = $this->paginate(
‘Post’,
"status=’approved’ AND MONTH(pub_date)=$month AND YEAR(pub_date)=$year"
);
$this->set(compact(‘posts’));
}
If you don’t use CakePHP, a SELECT statement in MySQL would be:
SELECT * FROM `Post` WHERE `Post`.`status`=‘approved’ AND MONTH(`Post`.`pub_date`)=2 AND YEAR(`Post`.`pub_date`)=2008;
The above extracts February 2008 data. Pretty neat.
Previous I wrote crap like:
SELECT * FROM `Post` WHERE `Post`.`status`=‘approved’ AND `Post`.`pub_date`>=‘2008-02-01′ AND `Post`.`pub_date`<‘2008-03-01′;
It’s just messy and ugly. And lots of calculation have to be done before hand.
Hope it helps. I haven’t really tested the SQL statements by the way. But it should work correctly. Only tested the CakePHP code. I use CakePHP 1.2’s paginate function.
Possibly related: