Sign in to follow this  
winch3rry

MultiSeries-Line Chart with sql and php

Recommended Posts

Hello to Forum,

 

I am new one with combining Charts with FusionChart XT.

I have created some normal charts with Pie,Column,Line but i  have some difficulties with MultiSeries Line Chart and would be appreciate if you can help me please. In the text below i will provide you my php code:

 

 <?php
      //We have included ../Includes/FusionCharts.php and ../Includes/DBConn.php, which contains
      //functions to help us easily embed the charts and connect to a database.
      include("FusionCharts.php");
      #include("../Includes/DBConn.php");
      ?>
      <HTML>
         <HEAD>
        <TITLE>FusionCharts XT - Database Example</TITLE>
        <SCRIPT LANGUAGE="Javascript" SRC="FusionCharts.js"></SCRIPT>
     </HEAD>
     <BODY>
     <CENTER>
<?php
  //In this example, we show how to connect FusionCharts to a database.
  //For the sake of ease, we have used an MySQL databases containing two
  //tables.
  // Connect to the DB
  mysql_connect('localhost','root','google');
  mysql_select_db('Difference_Output');
  // SQL query for category labels
  $strQueryCategories ="SELECT DISTINCT `CHANNEL_ID` FROM `DIFFERENTS` WHERE `SECONDS_ID2`>0 AND `SECONDS_ID1`>0 ORDER BY `CHANNEL_ID`";
  // Query database
  $resultCategories = mysql_query($strQueryCategories) or die(mysql_error());
  // SQL query for factory output data
  $strQueryData = "SELECT `SECONDS_ID2`, `SECONDS_ID1`, `CHANNEL_ID` FROM `DIFFERENTS` WHERE `SECONDS_ID2`>0 AND `SECONDS_ID1`>0 ORDER BY `SECONDS_ID2`,`SECONDS_ID1`";
  // Query database
  $resultData = mysql_query($strQueryData) or die(mysql_error());
  //We also keep a flag to specify whether we have to animate the chart or not.
  //If the user is viewing the detailed chart and comes back to this page, he shouldn't
  //see the animation again.
  //$strXML will be used to store the entire XML document generated
  //Generate the chart element
  $strXML = "<chart legendPosition='' caption='Records Output report' subCaption='By Seconds' xAxisName='Factory' yAxisName='Units' showValues='0' formatNumberScale='0' rotateValues='1' animation='1'>";
  // Build category XML
  $strXML .= buildCategories ($resultCategories, "CHANNEL_ID");
  // Build datasets XML
  $strXML .= buildDatasets ( $resultData, "SECONDS_ID2", "SECONDS_ID1");
  //Finally, close <chart> element
  $strXML .= "</chart>";
  //Create the chart - Pie 3D Chart with data from strXML
  echo renderChart("MSLine.swf", "", $strXML, "FactorySum", 1000, 700, false, true);
  // Free database resource
 # mysql_free_result($resultCategories);
 # mysql_free_result($resultData);
 # mysql_close($link);
 
/***********************************************************************************************
* Function to build XML for categories
* @param        $result         Database resource
* @param        $labelField     Field name as String that contains value for chart category labels
*
*       @return categories XML node
*/
function buildCategories ( $result, $labelField )
{
  $strXML = "";
  if ($result) {
    $strXML = "<categories>";
    while($ors = mysql_fetch_array($result)) {
      $strXML .= "<category label='" . $ors[$labelField]. "'/>";
    }
    $strXML .= "</categories>";
  }
  return $strXML;
}
 
/***********************************************************************************************
* Function to build XML for datasets that will contain chart data
* @param        $result         Database resource. The data should come ordered by a control break
field which will require to identify datasets and set its value to
dataset's series name
* @param        $valueField     Field name as String that contains value for chart dataplots
* @param        $controlBreak   Field name as String that contains value for chart dataplots
*
*       @return Dataset XML node
*/
function buildDatasets ($result, $valueField, $controlBreak )
{
  $strXML = "";
  if ($result) {
    $controlBreakValue ="";
    while( $ors = mysql_fetch_array($result) ) {
      if( $controlBreakValue != $ors[$controlBreak] )
      {
        $controlBreakValue =  $ors[$controlBreak];
        $strXML .= ( $strXML =="" ? "" : "</dataset>") . ( "<dataset seriesName='" . $controlBreakValue . "'>" ) ;
      }
  $strXML .= "<set value='" . $ors[$valueField] . "'/>";
    }
    $strXML .= "</dataset>";
  }
  return $strXML;
}
?>
 
</BODY>
</HTML>
 
The cart is running fine but is giving me all of the values, the thing that i need is 2 lines with dots.The CHANNEL_ID i want to be in X axis value and SECONDS1 and SECONDS2 IN Y values.
 
For every other Details i can provide you my help.
 
Thanks
 
 
                                                              

 

post-65787-0-27598400-1430569813_thumb.jpg

Share this post


Link to post
Share on other sites

Hi,

 

Could you please download the sample from the drop box link below?

 

Link: https://www.dropbox.com/s/3p2troiemz951sw/ForumPhpMSDbSample.rar?dl=0

 

In the demo sample we have database sample as well. Please import it to your local server before testing.

 

Also, please note that we have tested the sample in Xampp at our end. Hence, we would suggest you to do the required for the particular set up.

 

Hope the information is helpful.

 

Thanks.

Share this post


Link to post
Share on other sites

Hi i use the code given above, and i have DB like this :

+----------+------------+-------+
| Time       | Date      | A0 |
+----------+------------+-------+
| 17:00:00 | 2015-06-23 | 100 |
| 17:05:00 | 2015-06-23 | 120 |
| 17:10:00 | 2015-06-23 | 200 |
| 17:00:00 | 2015-06-24 | 200 |
| 17:05:00 | 2015-06-24 | 190 |
| 17:10:00 | 2015-06-24 | 200 |
| 08:00:00 | 2015-06-25 | 90  |
+----------+------------+-------+

the chart i get is that the 1st value of A0 data at 2015-06-23 and 2015-06-24 are at 08:00:00 instead of 17:00:00, and so goes on the other data. Anyone can fix it please ?

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
Sign in to follow this