Chart Data Sources

The Chart plugin can be bound to multiple types of data collections including arrays, xml, json, tsv, csv or remote data. To data bind the Chart to a data source you need to initialize and set its source property.

Let's see how to visualize XML data. This is the XML Data file that we use to build the Chart. 
        <!--?xml version="1.0" encoding="utf-8" ?-->
        <orders>
  <order>
    <date>1/1/2011</date>
    <quantity>10</quantity>
    <description>Beverages</description>
  </order>
  <order>
    <date>2/2/2011</date>
    <quantity>15</quantity>
    <description>Condiments</description>
  </order>
  <order>
    <date>3/3/2011</date>
    <quantity>20</quantity>
    <description>Grains/Cereals</description>
  </order>
  <order>
    <date>4/6/2011</date>
    <quantity>30</quantity>
    <description>Beverages</description>
  </order>
  <order>
    <date>5/10/2011</date>
    <quantity>40</quantity>
    <description>Beverages</description>
  </order>
  <order>
    <date>5/12/2011</date>
    <quantity>50</quantity>
    <description>Grains/Cereals</description>
  </order>
</orders>

Create a HTML file called Chart.html that contains the following HTML code: 
<html lang="en">
<head>
    <title id='Description'>jqxChart Line Series Example</title>
    <link rel="stylesheet" href="../../jqwidgets/styles/jqx.base.css" type="text/css" />
    <script type="text/javascript" src="../../scripts/jquery-1.11.1.min.js"></script>
    <script type="text/javascript" src="../../jqwidgets/jqxcore.js"></script>
    <script type="text/javascript" src="../../jqwidgets/jqxdata.js"></script>
    <script type="text/javascript" src="../../jqwidgets/jqxchart.core.js"></script>
    <script type="text/javascript" src="../../jqwidgets/jqxdraw.js"></script>
    <script type="text/javascript">
            $(document).ready(function () {
    // prepare the data
    var source = { datafields: [{ name: 'Date', type: 'date' },{ name: 'Quantity' },{ name: 'Description' }],
        root: "Orders",
        record: "Order",
        datatype: "xml",
        url: 'xmldata.xml'
    }
    var dataAdapter = new $.jqx.dataAdapter(source);
    // prepare jqxChart settings
    var settings = {
        title: "Order Details",
        showLegend: true,
        source: dataAdapter,
        categoryAxis: {
            type: 'date',
            baseUnit: 'month',
            dataField: 'Date',
            formatFunction: function (value) {
                var months = ['Jan', 'Feb', 'Mar', 'Apr', 'May', 'Jun', 'Jul', 'Aug', 'Sep', 'Oct', 'Nov', 'Dec'];
                return months[new Date(value).getMonth()];
            },
            showGridLines: true
        },
        colorScheme: 'scheme04',
        seriesGroups:[{
            type: 'line',
            valueAxis:{
                displayValueAxis: true,
                axisSize: 'auto',
                tickMarksColor: '#888888'
            },
            series: [{ dataField: 'Quantity', displayText: 'Quantity' }]
        }]
    };
    // setup the chart
    $('#jqxChart').jqxChart(settings);
});
    </script>
<script async src="https://www.googletagmanager.com/gtag/js?id=G-2FX5PV9DNT"></script><script>window.dataLayer = window.dataLayer || [];function gtag(){dataLayer.push(arguments);}gtag('js', new Date());gtag('config', 'G-2FX5PV9DNT');</script></head>
<body class='default'>
    <div id='jqxChart' style="width: 680px; height: 400px">
    </div>
</body>
</html>

In the Head section of the page, we include the jQuery javascript file, the jQWidgets core framework file – jqxcore.js, the jQWidgets data binding file – jqxdata.js, the main jqxChart plug-in file – jqxchart.js file, and the base jQWidgets stylesheet – jqx.base.css.
On to the body of the page. we create a DIV tag as the placeholder for the chart with a unique ID = “jqxChart”.
The chart is then instantiated using the jqxChart constructor function. The constructor takes in the following parameters: source – dataAdapter object, categoryAxis and seriesGroups objects, colorScheme(available values are from ‘scheme01′ to ‘scheme11′), showLegend(displays the legend section in the chart) and the Chart title. In the source object initialization, we specify the datafields array, the root and record properties, the url(points to a xml data file) and we set the datatype to ‘xml’.
In the seriesGroup object, the series group type is ‘column’ which means that the data will be visualized by columns. The valueAxis definition specifies the appearance of the Y-axis. This series group contains one serie that will render data corresponding to the Quantity dataField that it binds to. In this example we bind the Category Axis (horizontal X axis) to the Date property of our data source.

The result of the above code is:



What happens when the data source is changed? How to refresh the Chart?

To refresh the Chart, you need to simply set its 'source' property again.

In the example code below, the Chart is using the xmldata.xml as a data source. When the 'Refresh Data' button is clicked, the Chart's data source becomes the xmldata2.xml file.

The result of the above code is:



Here's the xmldata2.xml: 
            <!--?xml version="1.0" encoding="utf-8" ?-->
            <orders>
  <order>
    <date>1/1/2011</date>
    <quantity>10</quantity>
    <description>Beverages</description>
  </order>
  <order>
    <date>2/2/2011</date>
    <quantity>15</quantity>
    <description>Condiments</description>
  </order>
  <order>
    <date>3/3/2011</date>
    <quantity>20</quantity>
    <description>Grains/Cereals</description>
  </order>
  <order>
    <date>4/6/2011</date>
    <quantity>30</quantity>
    <description>Beverages</description>
  </order>
  <order>
    <date>5/10/2011</date>
    <quantity>40</quantity>
    <description>Beverages</description>
  </order>
  <order>
    <date>5/12/2011</date>
    <quantity>50</quantity>
    <description>Grains/Cereals</description>
  </order>
  <order>
    <date>5/13/2011</date>
    <quantity>90</quantity>
    <description>Condiments</description>
  </order>
  <order>
    <date>5/16/2011</date>
    <quantity>80</quantity>
    <description>Grains/Cereals</description>
  </order>
  <order>
    <date>4/12/2011</date>
    <quantity>50</quantity>
    <description>Grains/Cereals</description>
  </order>
  <order>
    <date>3/12/2011</date>
    <quantity>10</quantity>
    <description>Condiments</description>
  </order>
  <order>
    <date>6/12/2011</date>
    <quantity>50</quantity>
    <description>Beverages</description>
  </order>
  <order>
    <date>7/12/2011</date>
    <quantity>50</quantity>
    <description>Condiments</description>
  </order>
</orders>


Bind the Chart to MySQL Database using PHP

Let's see how to connect the jqxChart to MySql Database using PHP. The data will be obtained from MySql Database and especially the Northwind Database. The data will be returned in JSON format. You can download the Northwind database .sql script here and run it into MySQL to create the database.

The first thing we need to do is create the file we’ll connect with. We’ll call this file connect.php.

<?php
# FileName="connect.php"
$hostname = "localhost";
$database = "northwind";
$username = "root";
$password = "";
?>

Now we have our file to do the connection for us and we need to create the file that will run the query and bring the data so our Chart can be populated. We will call the file data.php.

<?php
	#Include the connect.php file
	include('connect.php');

	# Connect to the database
$mysqli = new mysqli($hostname, $username, $password, $database);
/* check connection */
if (mysqli_connect_errno())
	{
	printf("Connect failed: %s\n", mysqli_connect_error());
	exit();
	}
	
	
	// get data and store in a json array
$from = 0;
$to = 30;
$query = "SELECT OrderDate, ProductName, Quantity FROM  invoices ORDER BY OrderDate LIMIT ?, ?";
$result = $mysqli->prepare($query);
$result->bind_param('ii', $from, $to);
$result->execute();
/* bind result variables */
$result->bind_result($OrderDate, $ProductName, $Quantity);
/* fetch values */
while ($result->fetch())
	{
	$orders[] = array(
		'OrderDate' => $OrderDate,
		'ProductName' => $ProductName,
		'Quantity' => $Quantity
	);
	}
echo json_encode($orders);
/* close statement */
$result->close();
/* close connection */
$mysqli->close();

?>

The data is returned as JSON. This is it for the connection and data gathering. Let’s see how to add the data we just gathered into our jQuery Chart. Create the index.php file and add references to the following javascript and css files.

<link rel="stylesheet" href="styles/jqx.base.css" type="text/css" />
<script type="text/javascript" src="jquery-1.11.0.min.js"></script>
<script type="text/javascript" src="jqxcore.js"></script>
<script type="text/javascript" src="jqxchart.js"></script>
<script type="text/javascript" src="jqxdata.js"></script>

Create a div tag for the Chart.

<div id="jqxChart"></div>

Create your Chart and load the data. We define a source object for the Chart and bind that source to the data.php which returns the JSON data. We also set up the settings object and define the chart’s categoryAxis(horizontal X axis), valueAxis(vertical Y axis) and the chart serie. For more information about the Chart’s initialization visit this help topic: jquery-chart-getting-started.htm

<script type="text/javascript">
	$(document).ready(function () {
		var source =
		{
			 datatype: "json",
			 datafields: [
				 { name: 'OrderDate', type: 'date'},
				 { name: 'Quantity'},
				 { name: 'ProductName'}
			],
			url: 'data.php'
		};

	   var dataAdapter = new $.jqx.dataAdapter(source,
		{
			autoBind: true,
			async: false,
			downloadComplete: function () { },
			loadComplete: function () { },
			loadError: function () { }
		});

	 // prepare jqxChart settings
		var settings = {
			title: "Orders by Date",
			showLegend: true,
			padding: { left: 5, top: 5, right: 5, bottom: 5 },
			titlePadding: { left: 90, top: 0, right: 0, bottom: 10 },
			source: dataAdapter,
			categoryAxis:
				{
					text: 'Category Axis',
					textRotationAngle: 0,
					dataField: 'OrderDate',
					formatFunction: function (value) {
						return $.jqx.dataFormat.formatdate(value, 'dd/MM/yyyy');
					},
					showTickMarks: true,
					tickMarksInterval: Math.round(dataAdapter.records.length / 6),
					tickMarksColor: '#888888',
					unitInterval: Math.round(dataAdapter.records.length / 6),
					showGridLines: true,
					gridLinesInterval: Math.round(dataAdapter.records.length / 3),
					gridLinesColor: '#888888',
					axisSize: 'auto'
				},
			colorScheme: 'scheme05',
			seriesGroups:
				[
					{
						type: 'line',
						valueAxis:
						{
							displayValueAxis: true,
							description: 'Quantity',
							//descriptionClass: 'css-class-name',
							axisSize: 'auto',
							tickMarksColor: '#888888',
							unitInterval: 20,
							minValue: 0,
							maxValue: 100
						},
						series: [
								{ dataField: 'Quantity', displayText: 'Quantity' }
						  ]
					}
				]
		};

		// setup the chart
		$('#jqxChart').jqxChart(settings);
	});
</script>

Load the Chart from Array

In the following code example, the Chart is loaded from the 'sampleData' array. 
            var sampleData = [
        { Day:'Monday', Keith:30, Erica:15, George: 25},
        { Day:'Tuesday', Keith:25, Erica:25, George: 30},
        { Day:'Wednesday', Keith:30, Erica:20, George: 25},
        { Day:'Thursday', Keith:35, Erica:25, George: 45},
        { Day:'Friday', Keith:20, Erica:20, George: 25},
        { Day:'Saturday', Keith:30, Erica:20, George: 30},
        { Day:'Sunday', Keith:60, Erica:45, George: 90}
    ];
// prepare jqxChart settings
var settings = {
    title: "Fitness &amp; exercise weekly scorecard",
    description: "Time spent in vigorous exercise",
    showLegend: true,
    padding: { left: 5, top: 5, right: 5, bottom: 5 },
    titlePadding: { left: 90, top: 0, right: 0, bottom: 10 },
    source: sampleData,
    categoryAxis:
        {
            dataField: 'Day',
            showGridLines: true
        },
    colorScheme: 'scheme01',
    seriesGroups:
        [
            {
                type: 'column',
                columnsGapPercent: 50,
                seriesGapPercent: 0,
                valueAxis:
                {
                    unitInterval: 10,
                    minValue: 0,
                    maxValue: 100,
                    displayValueAxis: true,
                    description: 'Time in minutes',
                    axisSize: 'auto',
                    tickMarksColor: '#888888'
                },
                series: [
                        { dataField: 'Keith', displayText: 'Keith'},
                        { dataField: 'Erica', displayText: 'Erica'},
                        { dataField: 'George', displayText: 'George'}
                    ]
            }
        ]
};
            
// setup the chart
$('#jqxChart').jqxChart(settings);