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.

[code lang=“php”]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’));

}[/code]

If you don’t use CakePHP, a SELECT statement in MySQL would be:

[code lang=“sql”]

SELECT * FROM Post WHERE Post.status=‘approved’ AND MONTH(Post.pub_date)=2 AND YEAR(Post.pub_date)=2008;[/code]

The above extracts February 2008 data. Pretty neat.

Previous I wrote crap like:

[code lang=“sql”]

SELECT * FROM Post WHERE Post.status=‘approved’ AND Post.pub_date>=‘2008-02-01′ AND Post.pub_date<‘2008-03-01’;[/code]

It’s just messy and ugly. And lots of calculation have to be done before hand.

[ad#highlight]

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.

comments powered by Disqus