Help with MYSQL query

[1042]

Im trying to run this query to get totals on per month basis, so far this is what i have below;

 

PHP Code:

//run the query to get the totals on per month basis//
            $query = "SELECT order_date, SUM(grand_total) FROM order_details WHERE order_status='completed' GROUP BY order_date order by order_date";
    
          $result = mysql_query($query) or die(mysql_error());

         // Print out results
         while($row = mysql_fetch_array($result)){

         $order_date=$row['order_date'];
        
    // show the results by month//    
    echo "$order_date = $". $row['SUM(grand_total)'];
    echo "<br />";
}

 

this returns the following format:

2007-06-19 17:51:15 = $117.69

2007-06-19 17:58:57 = $76.99

2007-06-19 18:08:46 = $137.5

2007-06-19 18:22:25 = $94.19

2007-06-20 10:57:50 = $82.41

2007-06-21 15:09:21 = $94.19

2007-07-17 11:05:39 = $94.2

2007-08-19 17:52:52 = $26.5

 

but what i would like is to get the following format

july, 2007 = $total

June, 2007 =$total

 

in other words group the totals for the month for each month, hope i made sense.

what would be the best way to do this? thanks all for the help

[jscix]

if you only want to show the month, do some parsing.. rip the month out of the numerical date.

 

$newdate = substr($order_date,5,6);

 

then a simple case statement,

 

switch ($newdate) {

 

case "01":

$month = "January";

break;

 

}

etc..

[Barand]

$query = "SELECT YEAR(order_date) as yr, MONTHNAME(order_date) as mth, 
            MONTH(order_date) as mnum, SUM(grand_total) as total
            FROM order_details 
            WHERE order_status='completed' 
            GROUP BY yr, mnum ";