I have a code to get array of dates (This month - 12):
for ($i = 0; $i <= 11; $i++) {
$months[] = date("Y-m", strtotime( date( 'Y-m-01' )." -$i months"));
}
To get records from DB by specified month I can use the code below:
$sql = 'SELECT COUNT(id) FROM `#__records` WHERE MONTH(date)=1 AND YEAR(date)=2010';
Hot to create my request to get data by each month? Thanks!
Try this
for ($i = 0; $i <= 11; $i++) {
$months[] = date("Y-m", strtotime( date( 'Y-m-01' )." -$i months"));
}
$count = sizeof($months);
for($i=0;$i<$count;$i++)
{
$sql = 'SELECT COUNT(id) AS id FROM `#__records` WHERE MONTH(date)='".$months[$i]."' AND YEAR(date)=2010';
$result = mysql_query($sql);
if($result)
{
$data_array = array();
$data = mysql_fetch_assoc($result);
if($data)
{
$data_array[] = array($data['id']);
}
}
}
print_r($data_array);
You can do this directly in SQL with an appropriate GROUP BY query.
The trick is this little expression. Given any DATE or DATETIME`, it yields the first day of the month.
DATE(DATE_FORMAT(datevalue, '%Y-%m-01'))
The query uses that formula a lot to figure things out.
Try this:
SELECT COUNT(id) AS idcount,
DATE(DATE_FORMAT(date, '%Y-%m-01')) AS month_beginning
FROM table
WHERE date >= DATE(DATE_FORMAT(NOW(), '%Y-%m-01')) - INTERVAL 12 MONTH
AND date < DATE(DATE_FORMAT(NOW(), '%Y-%m-01')) + INTERVAL 1 MONTH
GROUP BY DATE(DATE_FORMAT(date, '%Y-%m-01'))
ORDER BY DATE(DATE_FORMAT(date, '%Y-%m-01'))
This gives back one row for each of the most recent twelve months.
Here's a more extensive writeup on this kind of summary query. http://www.plumislandmedia.net/mysql/sql-reporting-time-intervals/
SELECT id, DATE(DATE_FORMAT(date, '%Y-%m-01')) FROM table ? Do you get the right start-of-month date? Also, maybe you should include the column name date in backticks, because it's a reserved word.