...

View Full Version : Adding in a while loop



Atrhick
09-01-2011, 08:32 PM
Hey guys everything is working fine except for one thing. some of the dates are the same dates like "08/26/2011" but the dollar amounts are different is it possible just to show the amount of dollars made on that date?

right now i am getting data like this.

08/27/2011 $23
08/27/2011 $51
08/27/2011 $43
08/26/2011 $53
08/26/2011 $32
08/26/2011 $94

but I would rather get it like this.

08/27/2011 $117
08/26/2011 $179

please help :)

<?php
//Fetch all needed records
$strQuery ="SELECT * FROM lead_partners_pages ";
$strQuery .="WHERE afid_seller = '202'"; //$password
$result = mysql_query($strQuery) or die(mysql_error());

$strXML = "<graph caption='Monthly Report' decimalPrecision='0' showNames='1' numberSuffix=' dollars' pieSliceDepth='30' formatNumberScale='0'>";

//Iterate through each factory
if ($result) {
while($ors = mysql_fetch_array($result)) {

$strXML .= "<set name='{$ors['date_day']}'
value='{$ors['price_CIQFY_paid']}' />";
}
}
//Finally, close <graph> element
$strXML .= "</graph>";

//Create the chart - Pie 3D Chart with data from $strXML
echo renderChart("FusionCharts/FCF_Column3D.swf", "", $strXML, "myFirst", 725, 500);
?>

Old Pedant
09-01-2011, 10:26 PM
That's what SQL is *for*.



<?php
// Fetch all needed records
// But fetch *ONLY* the FIELDS that are needed!
$strQuery = "SELECT date_day, SUM(price_CIQFY_paid) AS amount "
. " FROM lead_partners_pages "
. " WHERE afid_seller = '202' "
. " GROUP BY date_day "
. " ORDER BY date_day";
$result = mysql_query($strQuery) or die(mysql_error());

$strXML = "<graph caption='Monthly Report' decimalPrecision='0' showNames='1' numberSuffix=' dollars' pieSliceDepth='30' formatNumberScale='0'>";

//Iterate through each factory
if ($result)
{
while($ors = mysql_fetch_array($result))
{
$strXML .= "<set name='{$ors['date_day']}' value='{$ors['amount']}' />";
}
}
//Finally, close <graph> element
$strXML .= "</graph>";

//Create the chart - Pie 3D Chart with data from $strXML
echo renderChart("FusionCharts/FCF_Column3D.swf", "", $strXML, "myFirst", 725, 500);
?>

Not ask a silly question, but if $result is false, such that you don't do the loop, then why will you still try to render the chart (with no data)?

Atrhick
09-01-2011, 10:44 PM
your right I don't need to add that if statement in there... I have another question for you I only ant this script to be for the month that we are in not the past months. is this possible?

Old Pedant
09-01-2011, 10:50 PM
Trivial.


$strQuery = "SELECT date_day, SUM(price_CIQFY_paid) AS amount "
. " FROM lead_partners_pages "
. " WHERE afid_seller = '202' "
. " AND YEAR(date_day)=YEAR(NOW()) AND MONTH(date_day)=MONTH(NOW()) "
. " GROUP BY date_day "
. " ORDER BY date_day";

That *assumes* that your field date_day is a DATETIME field. If it is not--if it is a text field or an integer--then it has to be converted to DATETIME to work.

Try it. If it works, you are home.

Atrhick
09-02-2011, 03:16 PM
in MySql it is only giving me the option to change it to either a "Date" or "Time" field so i picked Date for now.

Old Pedant
09-02-2011, 09:32 PM
??? What version of MySQL?

DATETIME has been in MySQL since at least version 3.2 (I used it in 1999).

And it's certainly in current versions.

http://dev.mysql.com/doc/refman/5.5/en/datetime.html

DATE is fine, if you only want to store date-alone. If you want to store date PLUS time, you need either DATETIME or TIMESTAMP.



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum