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]
