KDnuggets Home » News » 2016 » Mar » Opinions, Interviews, Reports » Dynamic Data Visualization with PHP and MySQL: Election Spending ( 16:n08 )

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:

I hope you found this tutorial useful! Feel free to say hi on Twitter if you have any questions :-)

Related


Sign Up

By subscribing you accept KDnuggets Privacy Policy