I'm trying to make a website which gets some data from database and generate some statistics with this data.
The idea is to allow the user to pick a month and then only query data from that month. So, if the user picks January, query for data > 01/01/2011 and < 31/01/2011.
I thought about generating the starting data from the month and then add one month and subtract a day so I get the last day of the given month but I don't think that's the best approach and also don't know how to generate a full date from a given month.
SQL centric way:
SELECT * FROM `foo` WHERE MONTH(`date`) = 3 AND YEAR(`date`) = 2011
Substitute the month and year numbers from your selection.
PHP centric way:
$month = 3; $year = 2011; $firstDay = "$year-$month-1"; $lastDay = "$year-$month-" . date('t', strtotime($firstDay)); $query = "SELECT * FROM `foo` WHERE `date` >= '$firstDay' AND `date` <= '$lastDay'";