[SOLVED] Sorting date column by month

[calabiyau]

Hi,

 

I am writing a blog script and on the display end I want to sort the results by month, so there will be a link to each month of the year and then a link to the previous year.  I have done a lot of searching for this and come up empty.  Can someone help me out with query that will select results for the current month, the previous month and the one before that etc.?  And what is the best format to save the date column in?  mysql's date format?  unix timestamp?

 

thanks.

[Barand]

My preference is for DATE column (YYYY-MM-DD). I know there are many that prefer the Unix timestamp but if I use a utlity like MySQL's Query Browser I like to see what the date is, instead of 1173139200.

$current = '200703';

$sql = "SELECT * FROM myblog WHERE DATE_FORMAT(blog_date, '%Y%m') = '$current');

 

or

$currY = 2007;
$currM = 3;

$sql = "SELECT * FROM myblog WHERE YEAR(blog_date) = $currY AND MONTH(blog_date) = $currM";

 

[calabiyau]

Ahhh.  thank you that is very helpful.

[Barand]

Answering your question, now I've re-read it

<?php
$current2 = date ('Ym');
$current1 = date ('Ym', strtotime('-2 months'));

$sql = "SELECT * FROM myblog WHERE DATE_FORMAT(blog_date, '%Y%m') BETWEEN '$current1' AND '$current2' ";
?>