5 Replies Latest reply on Jan 26, 2007 3:28 PM by

    Creating Chart from XML sourcing data from a database

      Hello All,

      i am trying to display a Column Chart with Source as a XML which is being generated using PHP from a database. Some how i am geting errors.. Is this possible to do..it.. if yes how should it be done.

      This table exist in MYSQL

      CREATE TABLE `project1`.`bubble_graph` (
      `Product` varchar(45) NOT NULL default '',
      `delivery` int(10) unsigned zerofill NOT NULL,
      `delay` int(10) unsigned zerofill NOT NULL
      ) ENGINE=InnoDB DEFAULT CHARSET=latin1;


      INSERT INTO bubble_graph VALUES('IteamA',20,2);
      INSERT INTO bubble_graph VALUES('IteamB',20,8);
      INSERT INTO bubble_graph VALUES('IteamC',20,1);
      INSERT INTO bubble_graph VALUES('IteamD',20,6);
      INSERT INTO bubble_graph VALUES('IteamE',20,15);
      INSERT INTO bubble_graph VALUES('IteamF',20,3);
      --------------------------------------PHP----------------code
      <?php
      Define( "DATABASE_SERVER", "localhost:3306" );
      Define( "DATABASE_USERNAME", "root" );
      Define( "DATABASE_PASSWORD", "yyyyyyy" );
      Define( "DATABASE_NAME", "project1" );

      //connect to the database
      $mysql = mysql_connect('localhost:3306', 'root', 'yyyyyy');

      mysql_select_db( DATABASE_NAME );

      //return a list of all the users
      $Query = "select Product,round(delivery) as delivery,round(delay) as delay from bubble_graph";
      $Result = mysql_query( $Query );

      $Return = "<Root>";

      while ( $User = mysql_fetch_object( $Result ) )
      {
      $Return .= "<Data>";
      // $Return .= "<Data ". "Product=\"" .$User->Product."\">";
      $Return .= "<Product>".$User->Product."</Product>";
      $Return .= "<delivery>".$User->delivery."</delivery>";
      $Return .= "<delay>".$User->delay."</delay>";
      $Return .= "</Data>";

      }
      $Return .= "</Root>";
      mysql_free_result( $Result );
      print ($Return)
      ?>
      ++++++++++++++++++++++++++++++Flex code+++++++++++

      <mx:ColumnChart id="chart" dataProvider="{results}" showDataTips="true" width="513" height="286" type="clustered" x="0" y="31">
      <mx:horizontalAxis>
      <mx:CategoryAxis dataProvider="{results}" categoryField="Product"/>
      </mx:horizontalAxis>

      <mx:series>
      <mx:Array>
      <mx:ColumnSeries yField="delay" name="delay"/>
      <mx:ColumnSeries yField="delivery" name="delivery"/>
      </mx:Array>
      </mx:series>
      </mx:ColumnChart>


        • 1. Re: Creating Chart from XML sourcing data from a database
          Ryan@imart
          Consider this, your code is way off. Your idea is fundamentaly correct but take a look at one i built. This actauly returns xml from a database posts it to a page and i access the page & data using query string values to aggregate the correct data. Look closley at the code im using to organize the results.


          <?xml version="1.0" encoding="utf-8"?>
          <mx:Application creationComplete="init();" xmlns:mx=" http://www.adobe.com/2006/mxml" layout="absolute" backgroundGradientColors="[#ffffff, #ffffff]" currentState="stSalesByDate">

          <mx:Script>
          <![CDATA[

          //Import Required Namespaces
          import mx.controls.Alert;
          import mx.validators.DateValidator

          var ResolveUrl:String = "";



          //Initialization Function handles program init
          private function init():void {


          ResolveUrl = Application.application.parameters.ResolveUrl;

          }


          //Validates input and fires http service
          private function buildReports():void{

          var retVal:Boolean = isValidDates(dateStart.text, dateEnd.text);

          if (retVal == true){

          wService.send();
          currentState='stSalesResults';

          } else {

          Alert.show('Please enter valid dates.', 'Invalid Dates', Alert.OK);

          }

          }

          //Handles State Switch Eevents for new reports
          private function newReport():void{

          currentState = 'stSalesByDate';

          }


          //Function Handles date comparison to ensure the dates are valid
          private function isValidDates(strStart:String, strEnd:String):Boolean {

          var blnIsValid:Boolean = false;


          if (strStart.length == 0 || strEnd.length == 0){

          blnIsValid = false;

          }else{

          blnIsValid = true;

          }

          return blnIsValid

          }

          ]]>
          </mx:Script>

          <mx:HTTPService showBusyCursor="true" method="POST" id="wService" url="{ResolveUrl}Site/Test/wsSalesByOrder.aspx?StartDate={dateStart.text}&amp;EndDate={da teEnd.text}"/>


          <mx:states>
          <mx:State name="stSalesByDate">
          <mx:AddChild position="lastChild">
          <mx:Panel resizeEffect="Resize" width="250" height="186" layout="absolute" id="pnlReportInput" title="Sales Report" backgroundColor="#ffffff" borderColor="#F6F9FC" color="#31639C" verticalCenter="0" horizontalCenter="0.5">
          <mx:Label x="16" y="26" text="Start Date:" id="label1"/>
          <mx:Label x="22" y="69" text="End Date:" id="label2"/>
          <mx:DateField x="93" y="24" width="120" id="dateStart"/>
          <mx:DateField x="93" y="67" width="120" id="dateEnd"/>
          <mx:Button label="Generate Report" bottom="10" right="10" click="buildReports();" id="button1"/>
          </mx:Panel>
          </mx:AddChild>
          <mx:SetProperty name="width" value="100%"/>
          <mx:SetProperty name="height" value="100%"/>
          </mx:State>
          <mx:State name="stSalesResults" basedOn="stSalesByDate">
          <mx:RemoveChild target="{dateStart}"/>
          <mx:RemoveChild target="{label1}"/>
          <mx:RemoveChild target="{label2}"/>
          <mx:RemoveChild target="{dateEnd}"/>
          <mx:SetProperty target="{pnlReportInput}" name="width" value="90%"/>
          <mx:SetProperty target="{pnlReportInput}" name="height" value="90%"/>
          <mx:RemoveChild target="{button1}"/>

          <mx:AddChild relativeTo="{pnlReportInput}" position="lastChild">

          <mx:ColumnChart id="column" dataProvider="{wService.lastResult.sales.sale}" showDataTips="true" type="clustered" width="95%" horizontalCenter="0" themeColor="#ffffff" height="88%" top="15">
          <mx:series>
          <mx:ColumnSeries xField="retDate" yField="retTotal" displayName="Sales By Day"/>
          </mx:series>

          <mx:horizontalAxis>
          <mx:CategoryAxis title="Sale Dates" categoryField="retDate"/>
          </mx:horizontalAxis>
          </mx:ColumnChart>

          </mx:AddChild>

          <mx:AddChild relativeTo="{pnlReportInput}" position="lastChild">
          <mx:Button label="New Report" bottom="10" right="10" click="newReport();"/>
          </mx:AddChild>

          </mx:State>
          </mx:states>

          </mx:Application>



          That code is called e4x it treats xml as a native object, research it :)
          • 2. Re: Creating Chart from XML sourcing data from a database
            Thanks Ryan

            This is good..
            i'll ckeck it out and let you know.. how it goes.

            Naveen
            • 3. Re: Creating Chart from XML sourcing data from a database
              Hello Ryan

              I was able to create the charts

              Appreciate your inputs.

              Regards,
              Nwin
              • 4. Re: Creating Chart from XML sourcing data from a database
                Hello Ryan

                I was able to create the charts

                Appreciate your inputs.

                Regards,
                Nwin
                • 5. Re: Creating Chart from XML sourcing data from a database
                  Hello Ryan

                  I was able to create the charts

                  Appreciate your inputs.

                  Regards,
                  Nwin