Re: GROUP BY MONTH(DateBilled) misses an entry consistently

Mike, Sorry, I haven't been keeping close track of the list - fortunately you have solved your problem. However you do not appear to have taken on-board Jason's comment. His/our concern would be that there are two mechanisms for extracting the results of the query from the MySQL resultset: DBfetch_array() which is presumably a local wrapper, and (later in the code) a 'native' call to mysql_fetch_array(). These two were both present before the switch from WHILE to DO...WHILE. Well done for spotting the error! I too have built myself 'wrapper' routines to handle db queries and both the subsequent extraction and loop control. They looks like: Fetch( $dbConnection, $NumRows, etc, $ResultSet ) while ( TheresAnotherRowToScan( $RowList, $ResultSet ) ) { etc [in fact, I stole the arg lists from the routines themselves - the language used in the calling routines is much more topical/self-documenting] =Thus there is no need for the 'double' resultset extractions... =Regards, =dn ----- Original Message ----- From: "Mike Gifford" <mike@openconcept.ca> To: "Mike Gifford" <mike@openconcept.ca> Cc: "DL Neil" <PHPml@DandE.HomeChoice.co.uk>; <php-db@lists.php.net> Sent: 11 January 2002 20:21 Subject: Re: [PHP-DB] GROUP BY MONTH(DateBilled) misses an entry consistently > It may have been obvious to many, but I stumbled across the solution > (eventually).. > > Changing the while statement to a do statement did the trick: > > $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); > $i=0; > do { > > $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; > } while($monthly_row = mysql_fetch_array($monthly_result)); > > On Fri, 2002-01-11 at 11:20, Mike Gifford wrote: > > 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" <mike@openconcept.ca> > > > To: <php-db@lists.php.net> > > > 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: php-db-unsubscribe@lists.php.net > > > > For additional commands, e-mail: php-db-help@lists.php.net > > > > To contact the list administrators, e-mail: php-list-admin@lists.php.net > > > > > > > > > > -- > > 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 > -- > 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: php-db-unsubscribe@lists.php.net > For additional commands, e-mail: php-db-help@lists.php.net > To contact the list administrators, e-mail: php-list-admin@lists.php.net > >