Help with a MySQL query
Posted: Wed Aug 01, 2007 1:13 pm
I am using this code below:
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)?
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>";
}
?>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)?