When working with large applications, data is usually saved in databases rather than static files. This data is retrieved by a server-side language to be served to the client. Further, Highcharts runs only on the client side and is completely independent of the technology that is used at the server side. Hence, any server-side language can be used to dynamically generate HTML and JavaScript to produce charts with Highcharts.
In this section, we will use a database to store data and a server-side language (PHP) to retrieve that data. Due to the vast variety of databases available today, we will use PHP's PDO class that is a consistent interface to access twelve different types of databases; MySQL is one of them. The advantage of using this interface is that we can use the same functions to issue queries and fetch data, regardless of the type of database we use. You can find more about PDO at http://php.net/manual/en/intro.pdo.php.
The database we are using in this example is a popular MySQL Sakila database, that is widely used in educational material. This database contains sample data for an online DVD store. The data includes movies, actors, customers, inventory, and their relationships.
In order to follow this example, you will need to have WAMP, LAMP, or XAMPP installed as we will use PHP with MySQL. If you don't have these stacks installed, you can download and install them by visiting their respective sites:
You will also need to download the Sakila database from the following link under the Example Databases section:
http://dev.mysql.com/doc/index-other.html
Once you have downloaded the archive file, you can install it by importing it in phpmyadmin or via the MySQL console. We are not going to cover those steps here since they are outside the scope of this book. If you need help installing the database, you can follow the steps given at http://dev.mysql.com/doc/sakila/en/sakila-installation.html.
Create a new file index.php and paste the following HTML code in it:
<!doctype html>
<html lang="en">
<head>
<meta charset="UTF-8">
<title>Highcharts Essentials</title>
</head>
<body>
<div id="actor_movies" style="width: 850px; height: 450px;"></div>
<script src="http://ajax.googleapis.com/ajax/libs/jquery/1.10.1/jquery.min.js"></script>
<script src="js/highcharts.js"></script>
<script>
(function() {
})();
</script>
</body>
</html>The basic configuration object for our chart is as follows:
var chartConfigOptions = {
title: {
text: 'Most Number of Movies by Actors'
},
chart: {
type: 'column'
},
tooltip: {
valueSuffix: ' Movies',
useHTML: true
},
xAxis: {
title: {
text: 'Actor'
}
},
yAxis: {
title: {
text: 'Movies'
}
},
series: [{
name: 'Actors'
}]
};We have not yet defined any categories on the xAxis component and data in the series as we are going to fetch it before inserting into the configuration object.
We will first fetch the data from the database using the PDO class and save it inside a variable. The PHP code is as follows:
<?php
try {
$con = new PDO( 'mysql:dbname=sakila;host=localhost', '$username', '$password' );
$data = $con->query( 'SELECT first_name, last_name, COUNT(film_actor.actor_id)
FROM film_actor
JOIN actor
ON film_actor.actor_id = actor.actor_id
GROUP BY film_actor.actor_id
ORDER BY COUNT(film_actor.actor_id) DESC
LIMIT 10' );
} catch ( PDOException $e ) {
echo 'Connection failed: ' . $e->getMessage();
}
?>In order to better deal with errors and exceptions, we have wrapped our code to connect to the database inside a try-catch statement. Inside the try statement, we initialized the connection using the constructor method of the PDO class. The constructor method accepts a string as the first argument that contains the PDO driver name followed by a colon, the name of the database, and, finally, the host. The PDO driver name, the database name, and the host are mysql, sakila, and localhost, respectively. Your details may vary depending upon the environment you are working in. Next comes the $username and the $password as the second and third arguments; these are the username and password required to access the database.
The constructor method returns a PDO object upon connection success, which works as the connection handler. In our case, it is stored in the $con variable.
In the next step, we execute the query to retrieve the first name, last name, and the film count of the top ten actors. This list is sorted in descending order by the number of films done by each actor. We use the SQL COUNT() method to count the number of films using actor_id in the film_actor table. Since we also need to show the first and last names, we use the SQL JOIN clause to retrieve them from the actor table of the database. The results are limited to 10 and are stored in the $data variable for later use.
If the connection fails, PDO throws an exception that is caught by the catch() statement, in which case we show the error message using the getMessage() method of the $e exception object.
Next comes the JavaScript part of our code that handles the chart initialization. It is as follows:
var categories = [], data = [];
<?php
foreach( $data as $row ) {
$actor = $row;
echo 'categories.push("' . $actor['first_name'] . '");';
echo 'data.push(' . $actor['COUNT(film_actor.actor_id)'] . '),';
}
?>
var chartConfigOptions = {
title: {
text: 'Most Number of Movies by Actors'
},
chart: {
type: 'column'
},
tooltip: {
valueSuffix: ' Movies',
useHTML: true
},
xAxis: {
title: {
text: 'Actor'
},
categories: categories
},
yAxis: {
title: {
text: 'Movies'
}
},
series: [{
name: 'Actors',
data: data
}]
};
$( '#actor_movies' ).highcharts( chartConfigOptions );We first initialize two empty arrays, that is categories and data, to hold x axis categories and series data. In the next step, we loop through $data to fetch the results row by row. In each iteration, we assign the current $row to the $actor variable. Although this is not necessary, we do so for better readability of our code. We then echo a PHP statement containing JavaScript's push() method to push an actor name $actor['first_name'] to the categories array. This statement will be parsed as JavaScript once it renders on the page. Similarly, we push the movie count—$actor['COUNT(film_actor.actor_id)']—to the data array. This way, we have our categories and data arrays ready and can use them to define the categories and data for our chart.
In the next step, we initialize the configuration object for our chart. We use the same categories and data arrays to define xAxis.categories and series.data. Finally, we load the chart into its container.
The following chart is produced as a result of the preceding code:
In this section, we used a server-side programming language to fetch the data from a database and render the chart. In the next section, we will learn to load data dynamically into the chart using Ajax.