Splitting into quarter with php and mysql

I have some blogposts and I want to create a link 开发者_如何转开发of archives.

The link will go to a page that will only return results from a particular quarter, how do I do this with php and mysql?

So I have a "date" field in a table and only want to return results from this quarter - e.g. the link to Quarter one will only return results from 1 Jan - 30th March.

I could do this with a set of static queries and dates but I would like to do it a more programmatic way.

Edit: You're talking about quarters of a year, not a quarter (1/4) of the result set.

• Step 1: This depends on how your year is split up. (fiscal/etc)

• Step 2: Use a WHERE clause that filters for the right months. Some variation of Date_Format().

  SELECT * FROM <table>

  WHERE Date_Format(date_stamp,'%Y-%m') IN ('2010-10','2010-11','2010-12')

Of course, you'll want to do some PHP to determine the months you need in place of the hard-coded month strings. But, that should get you where you need to be for a quarterly report. The reason I'm not saying use QUARTER(date) (which returns 1 - 4) is because sometimes your quarter needs to be customized, and the more generic, home-rolled mechanism is far more customizable. (And, you haven't said what quarter system is needed.)

Note: You can also use this sort of technique to do a grouping if you want a summary (total/avg/etc) on some field that's in the group as well.

This is the code I used:

$quarter = $_REQUEST['quarter'];
$quarter_conf = array(1=>"('2011-01','2011-02','2011-03')", 2=>"('2011-04','2011-05','2011-06')",3=>"('2011-07','2011-08','2011-09')", 4=>"('2011-10','2011-11','2011-12')");

$sql = "SELECT * FROM table where Date_Format(`date`,'%Y-%m') IN ".$quarter_conf[$quarter]." ORDER BY `date` DESC";