JS LinkedCharts in PHP & MySQL using FusionCharts XT – Pt. 4

In the 4th part of FusionCharts XT in PHP series, we will create LinkedCharts in PHP. LinkedCharts is a pioneering concept introduced by FusionCharts which allows unlimited levels of drill-down. LinkedCharts help users maintain the context when drilling down (or up) in their data. The child LinkedCharts can be configured in every way possible. Let us create LinkedCharts using the bundled PHP script, which takes the hassle out by generating the HTML, JavaScript and XML required to render any chart.

Table of Contents

FusionCharts XT in PHP Series

• Part 1 – JavaScript charts in PHP using FusionCharts XT
• Part 2 – JavaScript charts in PHP and MySQL using FusionCharts XT
• Part 3 – Drill-down JavaScript charts in PHP and MySQL
• Part 4 – JavaScript LinkedCharts in PHP and MySQL
• Part 5 – Using Debugging Tools for JavaScript Charts

What we are going to visualize

In the 3rd part of this series, we created a simple drill-down chart, where the parent chart shows the Top 10 Most Populous Countries, and the child chart shows the Top 10 Most Populous Cities of that country. We will make use of the same data and create a LinkedChart as shown below: LinkedCharts provides a convenient way to navigate through the levels using the overlay button. This helps immensely in maintaining context while traversing the data. There are a few more implementations of LinkedCharts in our Features Gallery.

What we will need

• PHP running on any server
• MySQL
• The latest version of FusionCharts XT
• The sample World database provided by MySQL. Read more to understand the structure of this database and how to set it up.
• The source code for the 2nd part of the series

Our installation process will be the same as we did in the previous article. You may use the same setup or create it as follows:

1. Within the root directory of your web server, create a folder named FusionCharts_XT_with_PHP. This will be our demo folder.
2. Copy the entire Charts folder from the FusionCharts XT Download Package and paste it within our demo folder. This completes the installation of FusionCharts XT in our web application.
3. Copy the Includes folder from FusionCharts XT Download Package > Code > PHP > Includes to our demo folder.
4. Copy the source files used in the previous article and paste them in our demo folder

Attributes of LinkedCharts

When creating LinkedCharts, you will have to provide the data for the child charts in either Data URL or Data String formats using the link attribute.

LinkedCharts using Data URL

In the Data URL method, we can point to a static XML file which provides the data for this particular child. The syntax for Data URL method is: The newChart prefix is used to indicate that it must invoke LinkedCharts. xmlURL indicates that Data URL method is used to specify XML data for the linked child charts. In case of JSON data, jsonurl is used. URL specifies the data path for the linked chart that opens when this column is clicked. So according to the syntax, you would provide the child chart’s data in this way:

<set label='your_label' value='your_value' link='newchart-xmlurl-{xml file URL}'></set>

We can also point to a virtual data provider, such as a server-side script which returns only XML, with Content-type set to text/xml:

<set label='China' value='1277558000' link='newchart-xmlurl-China.xml'></set>

LinkedCharts using Data String

When using the Data String method, the child chart’s data will have to be within the parent chart. Each data plot item in the parent chart is then linked to the child chart’s data (embedded in parent data source) by means of a unique data identifier. The syntax for Data String method is:

<set label='your_label' value='your_value' link='newchart-xml-{linked-data-ID}'></set>
 <linkeddata id='your_id'>
  <chart caption='Child chart'>
   {child chart data comes here}
  </chart>
 </linkeddata>

According to this syntax, this demo’s code would have to be:

<set label='China' value='1277558000' link='newchart-xml-China'></set>
 <linkeddata id='China'>
  <chart caption = 'Top 10 Most Populous Cities in China' showValues='0' useRoundEdges='1' palette='3'>
   <set label='Shanghai' value='9696300'></set>
   <set label='Peking' value='7472000'></set>
   <set label='Chongqing' value='6351600'></set>
   <set label='Tianjin' value='5286800'></set>
   <set label='Wuhan' value='4344600'></set>
   <set label='Harbin' value='4289800'></set>
   <set label='Shenyang' value='4265200'></set>
   <set label='Kanton [Guangzhou]' value='4256300'></set>
   <set label='Chengdu' value='3361500'></set>
   <set label='Nanking [Nanjing]' value='2870300'></set>
  </chart>
 </linkeddata>

Data URL or Data String?

As is apparent from the samples above, the Data URL method is more concise than the Data String method. If you have more than 1 level of drill-down, your XML string will get large, and consequently difficult to debug. Loading times for your charts might suffer too. Therefore, we recommend the Data URL method to keep your code cleaner, and charts leaner.

Creating LinkedCharts using Data URL method

As you already know from the first 3 articles, the Data URL method requires 3 things:

1. The parent chart (which we created in the 2nd article)
2. The correct path to the data provider in the link attribute (see below)
3. The data provider (see further below!)

Make a copy of FusionChartsXT_with_PHP_and_MySQL_DataString.php and save it as FusionChartsXT_LinkedCharts_DataURL.php. Edit the part where you create the dataplots as below:

// Append the names of the countries and their respective populations to the chart's XML string.
// Provide the path to the data provider in the 'link' attribute, along with the country's name in the querystring.
$strXML .= "";

Save this file. Let us now create the PHP script which will provide the data for the child charts:

<?php

// Set the content type in the header to text/xml.
header('Content-type: text/xml');

// Include the DBConn.php and FusionCharts.php files, so that we can access their variables and functions.
include('Includes/DBConn.php');
include('Includes/FusionCharts.php');

// Retrieve the name of the country passed from the parent chart's querystring.
$countryName = $_REQUEST['country'];

// Use the connectToDB() function provided in DBConn.php, and establish the connection between PHP and the World database in our MySQL setup.
$link = connectToDB();

// Form the SQL query which returns the Top 10 Most Populous Cities of the specified country.
$strQuery_cities = 'SELECT city.Name AS City, city.Population AS Population FROM city WHERE city.CountryCode = (SELECT country.Code FROM country WHERE country.Name = "'.$countryName.'") ORDER BY Population DESC LIMIT 10';

// Execute the query, or else return the error message.
$result_cities = mysql_query($strQuery_cities) or die(mysql_error());

// Form the chart data's XML string.
$strXML = "";

while($ors_cities = mysql_fetch_array($result_cities)) {
    // Iterate through the result set and append the data plots to the XML string.
    $strXML .= "";
}

// Close the chart's XML string.
$strXML .= "";  

// Return the valid XML string.
echo $strXML;
?>

Save this file as FusionChartsXT_LinkedCharts_DataProvider.php. Now navigate to the page which has the parent chart, and click on one of the columns. This is what you should see:

Creating LinkedCharts using Data String method

As we’ve seen above, when using the Data String method, the child charts’ data needs to be embedded within the parent chart’s data. Therefore, we need to have a single file only. Make another copy of FusionChartsXT_with_PHP_and_MySQL_DataString.php and save it as FusionChartsXT_LinkedCharts_DataString.php. New code needs to the written only within the while loop; but we’re showing the code for the entire file below:

< ?php
 
// Include the DBConn.php and FusionCharts.php files, so that we can access their variables and functions.
include('Includes/DBConn.php');
include('Includes/FusionCharts.php');

// Use the connectToDB() function provided in DBConn.php, and establish the connection between PHP and the World database in our MySQL setup.
$link = connectToDB();

// Form the SQL query which will return the Top 10 Most Populous Countries.
$strQuery = 'SELECT Name AS Country, Population FROM country ORDER BY Population DESC LIMIT 10';

// Execute the query, or else return the error message.
$result = mysql_query($strQuery) or die(mysql_error());

// If we get a valid response - 
if ($result) {
 
 // Create the parent chart's XML string. We can add attributes here to customize our chart.
 $strXML = "";
 
 while($ors = mysql_fetch_array($result)) {
  // Append the names of the countries and their respective populations to the chart's XML string.
  // Provide the appropriate ID for the LinkedChart in the 'link' attribute.
  $strXML .= "";
  
  // Form the SQL query which returns the Top 10 Most Populous Cities for the specified country.
  $strQuery_cities = 'SELECT city.Name AS City, city.Population AS Population FROM city WHERE city.CountryCode = (SELECT country.Code FROM country WHERE country.Name = "'.$ors['Country'].'") ORDER BY Population DESC LIMIT 10';

  // Execute the query, or else return the error message.
  $result_cities = mysql_query($strQuery_cities) or die(mysql_error());
  
  // Add the unique identifier for this particular child chart.
  $strXML .= "";
  
  // Create the child chart's XML. This chart can have all the attributes as any other regular chart.
  $strXML .= "";
  
  while($ors_cities = mysql_fetch_array($result_cities)) {
   // Append the names of the cities and their respective populations to the child chart's XML string.
   $strXML .= "";
  }
  
  // Close the child chart's XML string, and also close the unique identifier for this child chart.
  $strXML .= "";
 }
}   
// Close the parent chart's XML string.
$strXML .= ""; 
?>
  
< !DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "https://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
  
  
        
    
  

Save this file. Navigate to this page using your browser, and this is what you should see:

Configuring LinkedCharts

What we created above were standard LinkedCharts. The child charts used the same configuration and chart type as the parent chart. Using the FusionCharts XT JavaScript API, you can configure every aspect of the child charts. For this, we’ve provided the configureLink() function. You can pass all the properties that a FusionCharts constructor accepts, as parameters to this function. The syntax for the configureLink() is:

configureLink(objConfiguration: Object, level:Number)

As two separate parameters, the first parameter is an Object containing all configurations (chart and overlay-Button). The second parameter accepts a number which denotes the level being configured. The first drilldown level is 0 (zero). Let us see how we can change the child chart we created above to a Area chart. In FusionChartsXT_DrillDown_DataString.php, write the following code after the echo renderChart(); line: Save the file, navigate to this page, and click on one of the columns again. This is what you should see: You can also render LinkedCharts in another container, or within a lightbox. Read more about such configurations in our documentation.

LinkedCharts Events

The FusionCharts XT JavaScript API provides a number of events when LinkedCharts are in action. Each parent chart can listen to the events and can add more functionality to the implementation. The events are as follows:

• BeforeLinkedItemOpen: Fires before a child chart is created
• LinkedItemOpened: Fires after a child chart is created
• BeforeLinkItemClose: Fires before a child chart is closed
• LinkedItemClosed: Fires after a child chart is closed

Read more about Events in FusionCharts XT JavaScript API.

Download the source code

Next in the series

In the last part of the FusionCharts XT in PHP series, we will learn how to apply styles to our charts, export our charts, and debug our charts. Stay tuned!