Creating Interactive Charts with PHP and Cassandra
Introduction
Apache Cassandra is a distributed database with unique benefits that strengthen a developer's toolkit. Cassandra is free and open-source distributed database management system which is designed to manage large amounts of structured data.
In this article, we’ll discuss the step-by-step process of creating charts by fetching chart data from a Cassandra database using FusionCharts, a JavaScript-charting library, and its PHP wrapper.
Requirements
To render charts in PHP, we need three components, as listed below:
Including FusionCharts Core JS Files
To include FusionCharts core packages (fusioncharts.js and fusioncharts.charts.js) in the HTML file, refer to the code below:
1<html>
2 <head>
3 <!-- including FusionCharts core package JS files -->
4 <script src="path/to/fusioncharts.js"></script>
5 <script src="path/to/fusioncharts.charts.js"></script>
6 </head>
7</html>Including FusionCharts PHP Wrapper
Refer to the code below to include FusionCharts PHP wrapper (fusioncharts.php) in your PHP code.
1<?php
2 // including FusionCharts PHP wrapper
3 include("path/to/fusioncharts.php");
4?>Once this step is complete, you are all set with the environment. You should be able to work with a Cassandra database on your system.
Establishing and Verifying Connection with Database
Establish the connection with Cassandra database to fetch data for the chart with the PHP code below:
1$cluster = Cassandra::cluster()->build();
2
3$keyspace = 'marathons';
4
5// creating session with cassandra scope by keyspace
6$session = $cluster->connect($keyspace);
7
8// verifying connection with database
9if(!$session) {
10 echo "Error - Unable to connect to database";
11}In the above code:
Cassandra::cluster()->build();by default establishes a connection with a localhost cluster. You can also specify remote URL to establish connection with remote cluster.withContactPoints()andwithPort()methods are used to specify IP addresses or hostnames, and port number of the nodes in a given Cassandra cluster. Refer to this link for more details.$keyspaceholds the name of keyspace (database) from which data will be fetched for the chart.- The
$sessionvariable is used to establish the connection with the database. In case of any connection error, the above code will throw an error message. You can also create a separate.phpfile for this step and include it instead of writing it every time.
Note: To use Cassandra in PHP, please make sure you have enabled the necessary extensions by including extension=php_cassandra.dll in your php.ini configuration file.
Fetching Data and Forming the JSON Array
Now that we have successfully established connection with the database, we will write the query statement to fetch data for the chart.
Refer to the code below to fetch the data:
1$statement = new Cassandra\SimpleStatement( 'SELECT id, name, entry_cost, permile_cost, finisher_count FROM topten' );
2
3// query execution - fully asynchronous
4$exec = $session->executeAsync($statement);
5
6// getting query result in a variable
7$result = $exec->get();Here, $result variable holds the data once the above code is executed.
FusionCharts understands both XML and JSON data formats. Since we will be using JSON, we will now append the data (located in $result variable) along with FusionCharts chart configurations and parse the end result as an associative JSON array.
Refer to the code below to append data:
1if($result) {
2
3 // creating an associative array to store the chart attributes
4 $arrData = array(
5 "chart" => array(
6 "caption"=> "World's Top Marathons",
7 "captionFontBold"=> "1",
8 "captionFontSize"=> "24",
9 "captionFont"=> "Assistant",
10 "subcaption"=> "By Entry Cost (In Pounds)",
11 "subCaptionFontBold"=> "0",
12 "subCaptionFontSize"=> "19",
13 "subCaptionFont"=> "Assistant",
14 "captionPadding"=> "20",
15 "numberPrefix"=> "£",
16 "canvasBgColor"=> "#729BDF",
17 "bgColor"=> "#729BDF",
18 "canvasBgAlpha"=> "0",
19 "bgAlpha"=> "100",
20 "showBorder"=> "0",
21 "showCanvasBorder"=> "0",
22 "showPlotBorder"=> "0",
23 "paletteColors"=> "#FED34B",
24 "showValues"=> "0",
25 "decimals"=> "2",
26 "usePlotGradientColor"=> "0",
27 "baseFontColor"=> "#FFFFFF",
28 "baseFont"=> "Assistant",
29 "baseFontSize"=> "16",
30 "showAlternateVGridColor"=> "0",
31 "divLineColor"=> "#DBEAF8",
32 "divLineThickness"=> "0.9",
33 "divLineAlpha"=> "60",
34 "toolTipPadding"=> "7",
35 "toolTipBgColor"=> "#000000",
36 "toolTipBorderAlpha"=> "0",
37 "toolTipBorderRadius"=> "3"
38 ));
39
40 $arrData["data"] = array();
41 // iterating over each data and pushing it into $arrData array
42 foreach ($result as $row) {
43 array_push($arrData["data"], array(
44 "label" => $row["name"],
45 "value" => $row["entry_cost"]->value(),
46 "toolText" => "<b>" . $row["name"] . "</b><hr>Entry Cost: £" . number_format((float)$row["entry_cost"]->value(), 2, '.', '') . "<br> Per-mile Cost: £" .number_format((float)$row["permile_cost"]->value(), 2, '.', '') . "<br>Finishers: " . $row["finisher_count"]->value()
47 ));
48 }
49
50 $jsonEncodedData = json_encode($arrData);In the code shown, an if-statement determines whether the value of $result is valid and creates an associative JSON array to form data for the chart.
The chart object under the $arrData variable contains chart configuration options for caption, sub-caption, div lines, values, tooltips, color, etc.
To find out more about customizing the chart cosmetics, you can refer to this developer documentation page.
Creating the Chart Container
To create an HTML container for the chart, use <div> as shown below:
1<body>
2<div id="chart-container"></div>
3</body>This tells the browser that all elements within <div ...> and </div> belong to the same container.
Creating a Chart Instance and Rendering the Chart
- Now, that we have the chart data and container in place, let’s instantiate the FusionCharts PHP wrapper class and specify details like chart type, chart ID, chart dimensions, chart container ID, and more to render the chart:
1// creating FusionCharts instance
2$toptenChart = new FusionCharts("bar2d", "topChart" , '600', '450', "chart-container", "json", $jsonEncodedData);- Next, call the
render()method to render the chart.render()is a PHP class function that invokes the JSrender()method to render the chart. Callrender()as follows:
1// FusionCharts render method
2$toptenChart->render();Conclusion
We are done with all the steps to render an interactive chart using FusionCharts. The output looks like this:
Perfect, isn’t it? If you are having trouble rendering the chart, refer to source code from GitHub repository.
Thanks for reading! Feel free to leave a comment or feel free to say hi on Twitter!