How to break up reports by month with php and mysql?

Question

Hi everybody,

I'm trying to do something relatively simple here. Basically I have a table with a bunch of rows in it marked with a timestamp (format: 2009-05-30 00:14:57).

What I'm wanting to is do is a query which pulls out all of the rows, and splits them by the month so I'm left with a final result like:

February
rowID name order date
rowID name order date
rowID name order date

January
rowID name order date
rowID name order date
rowID name order date

etc.

I have a few vague ideas how to do this - they just seem long winded.

One of the ways would be to do a query for each month. I'd derive what the current month is in PHP then construct a for() which goes back a certain number of months.

like:

$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?

Answer 1

It's better to fetch all data once,ordered by month..

Then while fetching with php you can store your current month in a variable (for example $curMonth) and if there is a change in the month, you echo "New Month"...

Executing a query is slow, it's better to minimize your "conversations" with the db..

Answer 2

Don't forget that you have to deal with years aswell. If you have two records, one for January '09 and one for January '08, your results may be skewed.

Best to follow Svetlozar's advice and fetch all data at once. Once you have it in memory, use PHP to segment it into something usefull:

$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>';
}

Answer 3

You could change your SQL query to get your entire report. This is much more efficient than querying the database in a loop.

select
    monthname(reports.when) as currentmonth,
    other,
    fields,
    go,
    here
from reports
order by
    reports.when asc

You could then use this loop to created 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: Untested, but you get the general gist of it I'm sure.