How to split reports by month using php and mysql?
I am trying to do something relatively simple here. Basically I have a table with a bunch of rows in it, timestamped (format: 2009-05-30 00:14:57).
What I want to do is a query that pulls in all the rows and splits them by month, so I am left with an end result like:
February
row name date date date
row name date date date
rowID name order date
january
row name date date date
row name date date date
rowID name order date
and etc.
I have a few vague ideas on how to do this - they just seem long.
One way is to make a request for each month. I would get what the current month is in PHP and then construct a for () that returns a specific number of months.
as:
$currentmonth = 8;
$last6months = $currentmonth - 6;
for($i = $currentmonth; $i == $last6months; $i--) {
$sql = 'SELECT * FROM reports WHERE MONTH(reports.when) = $currentmonth ';
$res = mysql_query($sql);
// something would go here to convert the month numeral into a month name $currentmonthname
echo $currentmonthname;
while($row = mysql_fetch_array($res)) {
// print out the rows for this month here
}
}
Is there a better way to do this?
source to share
Remember that you have to deal with the years. If you have two records, one for January 2009 and one for January 2008, your results may be skewed.
It is best to follow Svetlozar's advice and get all the data at once. Once you've acquired it in memory, use PHP to segment it into something useful:
$monthData = array();
$queryResult = mysql_query("
SELECT
*,
DATE_FORMAT('%m-%Y', when) AS monthID
FROM
reports
WHERE
YEAR(when) = 2009 AND
MONTH(when) BETWEEN 5 and 11
");
while ($row = mysql_fetch_assoc($queryResult))
{
if (!isset($monthData[$row['monthID']]))
$monthData[$row['monthID']] = array();
$monthData[$row['monthID']][] = $row;
}
mysql_free_result($queryResult);
foreach($monthData as $monthID => $rows)
{
echo '<h2>Data for ', $monthID, '</h2>';
echo '<ul>';
foreach($rows as $row)
{
echo '<li>', $row['someColumn'], '</li>';
}
echo '</ul>';
}
source to share
It is best to get all the data once, ordered by month.
Then, selecting with php, you can store the current month in a variable (like $ curMonth) and if there is a change in the month, you echo "New month" ...
Query execution is slow, it is better to minimize your "conversations" with db ..
source to share
You can modify your SQL query to get the whole report. This is much more efficient than a looping database query.
select
monthname (reports.when) as currentmonth,
other,
fields,
go,
here
from reports
order by
reports.when asc
Then you can use this loop to create a nested report:
var $ currentMonth = '';
while ($ row = mysql_fetch_array ($ res)) {
if ($ currentMonth! == $ row ['currentMonth']) {
$ currentMonth = $ row ['currentMonth']);
echo ('Month:'. $ currentMonth);
}
// Display report detail for month here
}
* Note : not indexed, but you get the general meaning. I'm sure.
source to share