Adding in a while loop

[Atrhick]

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]

That's what SQL is *for*.

<?php
// Fetch all needed records
// [COLOR="Red"]But fetch *ONLY* the FIELDS that are needed![/COLOR]
$strQuery = "SELECT [COLOR="Red"]date_day, SUM(price_CIQFY_paid) AS amount[/COLOR] "
          . " FROM lead_partners_pages "
          . " WHERE afid_seller = '202' "
          . " [COLOR="Red"]GROUP BY date_day[/COLOR] " 
          . " [COLOR="Red"]ORDER BY date_day[/COLOR]";
$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['[COLOR="Red"]amount[/COLOR]']}' />";
    }
}
//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]

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]

Trivial.

$strQuery = "SELECT date_day, SUM(price_CIQFY_paid) AS amount "
          . " FROM lead_partners_pages "
          . " WHERE afid_seller = '202' "
[COLOR="Red"]          . " AND YEAR(date_day)=YEAR(NOW()) AND MONTH(date_day)=MONTH(NOW()) "[/COLOR]
          . " 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]

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]

??? 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.