Web Analytics Made Easy -
StatCounter Adding in a while loop - CodingForum


No announcement yet.

Adding in a while loop

  • Filter
  • Time
  • Show
Clear All
new posts

  • Adding in a while loop

    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 Code:
    //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) {
    $ors mysql_fetch_array($result)) {

    $strXML .= "<set name='{$ors['date_day']}'
    {$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"725500);
    Last edited by Atrhick; Sep 1, 2011, 02:36 PM.

  • #2
    That's what SQL is *for*.

    // 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)?
    Be yourself. No one else is as qualified.


    • #3
      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?


      • #4
        $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.
        Be yourself. No one else is as qualified.


        • #5
          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.


          • #6
            ??? 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.

            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.
            Be yourself. No one else is as qualified.