Dynamic Data Visualization with PHP and MySQL: Election Spending
Learn how to fetch data from MySQL database using PHP and create dynamic charts with that data, using an interesting example of New Hampshire primary election spending.
Step-4: Converting Query Result into JSON
FusionCharts accepts data in both JSON and XML formats. Since we are using JSON in our example, our next task is to convert the data inside $result
variable into JSON format.
Here is the PHP code to achieve that (explanation below code snippet):
if ($result) {
// creating an associative array to store the chart attributes
$arrData = array(
"chart" => array(
"caption" => "Spendings in New Hampshire Primary",
"xAxisName" => "Candidate",
// more chart configuration options
)
);
$arrData["data"] = array();
// iterating over each data and pushing it into $arrData array
while ($row = mysqli_fetch_array($result)) {
array_push($arrData["data"], array(
"label" => $row["candidate"],
"value" => $row["expenditure"]
));
}
$jsonData = json_encode($arrData);
}
In this step, first we are making sure that the result is valid (through if). If it is valid, then we create an associative array to form JSON data.
chart
object under $arrData
contains the configuration options for the chart like caption, font, data plot color and display formats for numbers etc. To learn more about customizing a column 2D chart, you can visit this documentation page.
data
object contains the data of the chart type to be rendered. The data is in the $result
variable which is pushed into the associative array.
Finally, JSON is encoded with the help of the json_encode
method and stored in the variable $jsonData.
Step-5: Creating Chart Instance and Closing the DB Connection
Every chart on your page needs a separate HTML container to sit inside. We are going to use a <div>
element to house ours:
<div id="column-chart">Awesome Chart on its way!</div>
Now we will create the FusionCharts instance and pass the chart type, its dimensions, <div>
container and other details to it to render the chart, in the format given below:
// syntax for chart instance -
new FusionCharts("type of chart",
"unique chart id",
"width of chart",
"height of chart",
"div container id to render the chart",
"type of data",
"actual data")
And here is the above code filled with our data:
// creating FusionCharts instance
$columnChart = new FusionCharts("column2d", "expenseChart",
"100%", "500", "column-chart", "json", $jsonData);
Finally, we render the chart and close our database connection:
// FusionCharts render() method
$columnChart>render();
// closing database connection
$dbhandle->close();
If you followed the above steps, then you must have a working column 2D chart with you now. If not, you can download the source code of the project from this Google Drive link.
More Resources
Here are some resources that you will find useful if you decide to make interactive charts using PHP and MySQL:
- Improving design: You can use chart attributes to customize a chart’s cosmetics. I am not going to cover them in this post, but you can refer this page to know what attributes can be applied to a 2D column chart.
- PHP wrapper: if you want to know more about what is possible with FusionCharts’ wrapper, you can explore their PHP charts page. It contains many live examples with their source code.
I hope you found this tutorial useful! Feel free to say hi on Twitter if you have any questions :-)
Related