Help with a MySQL query

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Locked
icesolid
Forum Regular
Posts: 502
Joined: Mon May 06, 2002 9:36 pm
Location: Buffalo, NY

Help with a MySQL query

Post by icesolid »

I am using this code below:

Code: Select all

<?php 
include("connect.php"); 

$result = mysql_query("SELECT count(*) as ordercount, LEFT(DATE(date_ordered), 7) as yearmonth FROM cases WHERE user_code!='001' AND user_code!='149' AND user_code!='150' AND YEAR(date_ordered)>=2006 GROUP BY LEFT(DATE(date_ordered), 7)"); 

while($row = mysql_fetch_assoc($result)) { 
   echo $row["yearmonth"] . " = " . $row["ordercount"] . "<br>"; 
} 
?>
I am getting this result:

2006-03 = 9
2006-04 = 12
2006-05 = 58
2006-06 = 166
2006-07 = 155
2006-08 = 243
2006-09 = 191
2006-10 = 208
2006-11 = 199
2006-12 = 358
2007-01 = 367
2007-02 = 364
2007-03 = 423
2007-04 = 474
2007-05 = 438
2007-06 = 378
2007-07 = 380

It is not printing the January and February from 2006. It also is not printing August of 2007. This is because those months have 0 cases ordered. How do I make it also include months that had 0 ordered?

Another thing, is there any easy way to format dates from their current format to a different one (ex: 2006-03 to March 2006)?
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

You'll have to add those to the result set yourself.
programmingjeff
Forum Commoner
Posts: 26
Joined: Fri Jan 05, 2007 10:56 am

Re: Help with a MySQL query

Post by programmingjeff »

icesolid wrote:Another thing, is there any easy way to format dates from their current format to a different one (ex: 2006-03 to March 2006)?
Lookup the date_format function
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

This thread is duplicated. Locked.
Locked