Hello, Thanks for your quick reply.. I'm trying to improve the stats feature for gcdb (a pretty decent little PHP/MySQL accounting package on SF.net)
On Fri, 2002-01-11 at 05:24, DL Neil wrote: > Have you posted all of the relevant code - for example, how the 'result' is limited >to three month's worth of > data??? I didn't provide all of the code in the initial response as it was using a wrapper so I didn't know how relevant it would be.. However, your note made me realize that I could rewrite the code without the wrapper.. It still worked the same way. The code stands as: $sql = " SELECT COUNT(DateBilled) AS count, MONTH(DateBilled) AS BilledMonth, YEAR(DateBilled) AS BilledYear FROM Invoices GROUP BY MONTH(DateBilled) ORDER BY DateBilled DESC"; $monthly_result = mysql_query($sql, $db); $monthly_row = DBfetch_array($monthly_result); while($monthly_row = mysql_fetch_array($monthly_result)) { ++$i; $InvMonth[$i] = $monthly_row["BilledMonth"]; $InvCount[$i] = $monthly_row["count"]; echo "<strong>Month: " . date ("F", mktime(0,0,0,$InvMonth[$i],1,2002)) . " <!-- ($InvMonth) --> Number of Invoices: " . $InvCount[$i] . "</strong><br>"; } RESULTS: Month: December Number of Invoices: 22 Month: November Number of Invoices: 17 Month: October Number of Invoices: 21 > Have you extracted the SQL from the PHP and applied it directly to the command line >or used it in a MySQL > Management package? Was the result any different? Also a damn good idea (I haven't had root access to MySQL until recently, hadn't thought of that either) mysql> SELECT COUNT(DateBilled) AS count, MONTH(DateBilled) AS BilledMonth, YEAR(DateBilled) AS BilledYear FROM Invoices GROUP BY MONTH(DateBilled) ORDER BY DateBilled DESC; +-------+-------------+------------+ | count | BilledMonth | BilledYear | +-------+-------------+------------+ | 15 | 1 | 2002 | | 22 | 12 | 2001 | | 17 | 11 | 2001 | | 21 | 10 | 2001 | +-------+-------------+------------+ 4 rows in set (0.00 sec) Ok.. So the problem seems to be with my code.. $sql = " SELECT COUNT(DateBilled) AS count, MONTH(DateBilled) AS BilledMonth, YEAR(DateBilled) AS BilledYear FROM Invoices GROUP BY MONTH(DateBilled) ORDER BY DateBilled ASC"; $monthly_result = mysql_query($sql, $db); $monthly_row = DBfetch_array($monthly_result); $i=0; while($monthly_row = mysql_fetch_array($monthly_result)) { $InvMonth[$i] = $monthly_row["BilledMonth"]; $InvCount[$i] = $monthly_row["count"]; echo "<strong>Month: " . date ("F", mktime(0,0,0,$InvMonth[$i],1,2002)) . " <!-- ($InvMonth) --> Number of Invoices: " . $InvCount[$i] . "</strong><br>"; ++$i; } RESULTS: Month: November Number of Invoices: 17 Month: December Number of Invoices: 22 Month: January Number of Invoices: 15 I can't see the bug in the PHP I've got, but there certainly must be one.. Any suggestions would be appreciated! Mike > ----- Original Message ----- > From: "Mike Gifford" <[EMAIL PROTECTED]> > To: <[EMAIL PROTECTED]> > Sent: 11 January 2002 08:12 > Subject: [PHP-DB] GROUP BY MONTH(DateBilled) misses an entry consistently > > > > Hello > > > > I've got the following SQL Query, which consistently pulls up only 3 out > > of 4 months from the database: > > > > $sql = " SELECT COUNT(DateBilled) AS count, MONTH(DateBilled) AS > > BilledMonth, YEAR(DateBilled) AS BilledYear FROM Invoices GROUP BY > > MONTH(DateBilled) ORDER BY DateBilled ASC"; > > > > This results in: > > Month: November Number of Invoices: 17 > > Month: December Number of Invoices: 22 > > Month: January Number of Invoices: 15 > > > > But when I do change the order of the query from ASC to DESC like this: > > > > $sql = " SELECT COUNT(DateBilled) AS count, MONTH(DateBilled) AS > > BilledMonth, YEAR(DateBilled) AS BilledYear FROM Invoices GROUP BY > > MONTH(DateBilled) ORDER BY DateBilled ASC";$sql = " SELECT > > COUNT(DateBilled) AS count, MONTH(DateBilled) AS BilledMonth, > > YEAR(DateBilled) AS BilledYear FROM Invoices GROUP BY MONTH(DateBilled) > > ORDER BY DateBilled DESC"; > > > > I get: > > Month: December Number of Invoices: 22 > > Month: November Number of Invoices: 17 > > Month: October Number of Invoices: 21 > > > > (I gained October and Lost January) > > > > The date format in the DB is like this: > > 2001-12-05 > > > > I've tried a whole stack of variations on the above query, but I still > > seem to be coming up one short. Any idea why I'm not getting a display > > of all of the months? > > > > Thanks. > > > > Mike > > -- > > Mike Gifford, OpenConcept Consulting, http://www.openconcept.ca > > Supporting progressive organizations in online campaigns and tools. > > Feature: Women's Learning Partnership http://learningpartnership.org > > Truth is that which confirms what we already believe. Northrop Frye > > > > > > -- > > PHP Database Mailing List (http://www.php.net/) > > To unsubscribe, e-mail: [EMAIL PROTECTED] > > For additional commands, e-mail: [EMAIL PROTECTED] > > To contact the list administrators, e-mail: [EMAIL PROTECTED] > > > > -- Mike Gifford, OpenConcept Consulting, http://www.openconcept.ca Supporting progressive organizations in online campaigns and tools. Feature: Women's Learning Partnership http://learningpartnership.org Truth is that which confirms what we already believe. Northrop Frye -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] To contact the list administrators, e-mail: [EMAIL PROTECTED]