Dynamic Data Visualization with PHP and MySQL: 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

• Visualizing Unstructured Analysis – Elections, Words, and Zika virus
• Building Zoomable Line Charts in jQuery
• OpenText Data Digest Nov 27: Data Mapping Music