• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 538
  • Last Modified:

How to sort data from Mysql table by month

Hello there,

I have created a PHP page that retrieves two columns from a MySQL db (date and amount). What i can't seem to comprehend is the next step... how to sum the amount and show it by month name and year as below.
The Mysql table i have consists of three columns:

-ID Autoincrement
-Date Date
-Minutes Int(11)

This is how i would like to have the data.

January 2001              45673
February 2001            34455
March 2001                23747
April 2001                  86464
May 2001                   6363
June 2001                   0

Here is my PHP code so far...
// connect to the database

$query = "SELECT Date, Minutes FROM tbl_Minutes ORDER BY Date";

$sth = $adb->prepare($query);
        $res = $sth->execute();
        $numrows = $sth->rows();
        for($i = 0; $i < $numrows; $i++)
                $result = $sth->fetchrow_hash();
                $Date = $result["Date"];
                $Minutes = $result["Minutes"];
                PRINT "$Date  ";
                PRINT "$Minutes <BR>";
} else
    PRINT "Could not prepare query: ".$sth->errstr."<br>\n";
Any pointers, ideas or examples would be greatly appreciated. Thanks alot.
  • 2
  • 2
3 Solutions
try this query:
$query = "SELECT date_formt(Date, '%M %Y') as Date , Minutes FROM tbl_Minutes ORDER BY Date";

read this for more info: http://www.mysql.com/doc/en/Date_and_time_functions.html
Just a little change of CosminB's answer to add up the minutes for you:

SELECT date_formt(Date, '%M %Y') as Date , sum(Minutes) as Minutes FROM tbl_Minutes GROUP BY Date
snoyes, this does not add minutes but rather outputs as a different field the sum of all the minutes values for a given date...

richard, can't you store timestamps in the db and convert them afterward ? it usually ends up to be the best solution to avoid problems (of course the type of the field will NOT be 'timestamp' but rather INT(10), atherwise the timestamps will automatically change upon update )

otherwise, cosmin's solution will work as long as mysql properly interprets the content of the field (beware you can't mispell January for example)

good luck

Quoting the question (emphasis mine):
>>>What i can't seem to comprehend is the next step... how to SUM THE AMOUNT and show it by month name and year as below.
most sorry snoyes, u were pertinent as usual and i misunderstood the question.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now