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" <[EMAIL PROTECTED]>
To: "Mike Gifford" <[EMAIL PROTECTED]>
Cc: "DL Neil" <[EMAIL PROTECTED]>; <[EMAIL PROTECTED]>
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" <[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
> --
> 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]
>
>
--
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]
