CODEWITHSUNDEEP
phpmysql
willdanceforfun
willdanceforfun

Reputation: 11240

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

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?

Upvotes: 2

Views: 4868

Answers (4)

sangdreamer
sangdreamer

Reputation: 11

This is the SQL script:

SELECT*, DATE_FORMAT(fieldname,'%Y-%m') AS report FROM bukukecil_soval WHERE MONTH(fieldname) = 11 AND YEAR(fieldname)=2011

I hope you know where you should put this code :D

Upvotes: 1

Mr. Smith
Mr. Smith

Reputation: 5558

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.

Upvotes: 2

NSSec
NSSec

Reputation: 4551

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

Upvotes: 3

Svetlozar Angelov
Svetlozar Angelov

Reputation: 21660

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..

Upvotes: 5

Related Questions