Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 6 of 6
  1. #1
    New Coder
    Join Date
    Aug 2011
    Posts
    52
    Thanks
    0
    Thanked 0 Times in 0 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:
    <?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"725500);
    ?>
    Last edited by Atrhick; 09-01-2011 at 07:36 PM.

  • #2
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,020
    Thanks
    75
    Thanked 4,323 Times in 4,289 Posts
    That's what SQL is *for*.

    Code:
    <?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)?
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • #3
    New Coder
    Join Date
    Aug 2011
    Posts
    52
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,020
    Thanks
    75
    Thanked 4,323 Times in 4,289 Posts
    Trivial.
    Code:
    $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.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • #5
    New Coder
    Join Date
    Aug 2011
    Posts
    52
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,020
    Thanks
    75
    Thanked 4,323 Times in 4,289 Posts
    ??? 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.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.


  •  

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •