Tutorials

How to Generate Charts from a MySQL Database Using PHP

Fusion Charts, DreamHost
Written by DreamHost

If you have been into programming for some time now, then there is good chance that PHP and MySQL are part of your technology stack. No matter how many new and shiny languages come and go, this combination (PHP and MySQL) will be there for at least some more time. And that’s the reason I decided to use both the technologies today to make some beautiful charts.

And that’s the reason I decided to use both the technologies today to make some beautiful charts.

In this post, we will cover the process of fetching data from a MySQL database through PHP and then render interactive charts using that data. For charting component, we are going to use FusionCharts as it offers a good collection of charts and maps, is compatible with every browser, and is user-friendly. It also has a dedicated PHP charts plugin that will make our job little bit easier.

One note: FusionCharts is free for personal and non-commercial use. You will have to buy a license if you’d like to use it in commercial applications.

This is what we are building today:

MySQL Chart - Browser Market Share

Now, let’s get started!

We have divided this tutorial into following six steps:

  1. Include chart library files
  2. Initiate connection with MySQL database
  3. Validate connection & write SQL query
  4. Form JSON array
  5. Create a chart container
  6. Create FusionCharts instance and close database connection

Step 1: Include Chart Library Files

To render charts using PHP, we need to include below files:

  • FusionCharts’ PHP charts wrapper — fusioncharts.php — will go inside PHP code (download it from plugin page)
  • FusionCharts’ core JavaScript files — fusioncharts.js & fusioncharts.charts.js — will go inside HTML using script tag (download them from this page)

Here is the HTML and PHP code to achieve this:

PHP:

<?php
  // including FusionCharts PHP wrapper
  include("/path/to/fusioncharts.php"); 
?>

HTML:

<html>

<head>
  <!-- including FusionCharts JavaScript file -->
  <script type="text/javascript" src="/path/to/fusioncharts.js"></script>
  <script type="text/javascript" src="/path/to/fusioncharts.charts.js"></script>
</head>

</html>

Step 2: Initiate Connection with MySQL Database

After we have included required JavaScript and PHP files, we need to set up a connection with the database to fetch data for the chart. The PHP code for doing this is shown below:

<?php

    //setting up connection with database
    $host_db="localhost"; // MySQL host server (might vary depending upon user)
    $user_db="db_username"; // MySQL database username
    $pass_db="db_password"; // MySQL password
    $name_db="DB_name"; // name of the database

?>

You need to replace values for $host_db, $user_db, $pass_db, and $name_db with actual values for your MySQL database and database host server.

You don’t have to be a professional to build a great website. Just follow these design best practices.

Step 3: Validate Connection & Write SQL Query

After the connection with MySQL database is done, we will do a quick check to validate the connection and write a query to take result in a variable. Here is how we validate connection:

$dbhandle = new mysqli($host_db, $user_db, $pass_db, $name_db);

if ($dbhandle -> connect_error) {
    exit("There was an error with your connection: ".$dbhandle - > connect_error);
}

If above code is executed successfully, you will have a connection established. Now we can use below code to fetch data:

$strQuery = "SELECT DISTINCT browser, shareval FROM browsershare_feb2016; ";

$result = $dbhandle->query($strQuery) or exit("Error code ({$dbhandle->errno}): {$dbhandle->error}");

$dbhandle is used to make the connection with the database. If the connection is made successfully, no error will be there and $strQuery will be executed. Otherwise, it will throw an error.

Step 4: Form JSON Array

If you followed everything till now, you should have your data fetched from SQL query in $result variable.

Quick Tip: You can `echo` the result to verify data received as SQL query result.

Now we will append the data along with FusionCharts chart configuration and parse the end result as JSON array (FusionCharts understands both JSON and XML data formats).

Here is the code to achieve that:

if ($result) {

  // creating an associative array to store the chart attributes    	
  $arrData = array(
    "chart" => array(
      "theme" => "fint",
      "caption" => "Browser Market Share",
      "subcaption" => "February 2016",
      "captionFontSize" => "24",
      "paletteColors" => "#A2A5FC, #41CBE3, #EEDA54, #BB423F #,F35685",
      "baseFont" => "Quicksand, sans-serif",
      //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["browser"],
      "value" => $row["shareval"]
    ));
  }

  $jsonEncodedData = json_encode($arrData);

}

In this step, we are first checking if the result is valid. If it is valid, then we create an associative array which contains both chart configuration options and the data we fetched from database. And then we encode this array into JSON using json_encode function.

chart object under $arrData contains chart configuration options like caption, background color, data plot color, and display formats for numbers etc. To learn about customizing a chart’s look and feel, you can visit this documentation page for 2D doughnut chart.

Step 5: Create a Chart Container

We are going to use HTML <div> element to render our chart:

&amp;lt;div id=&amp;quot;doughnut-chart&amp;quot;&amp;gt;A beautiful donut chart is on its way!&amp;lt;/div&amp;gt;

Don’t Force choke your website’s design. Elevate your site with these lessons from StarWars.com.

Step 6: Create FusionCharts Instance and Close Database Connection

After final JSON array is created, we will create FusionCharts instance and pass chart type, dimensions, container id, and other details to it to render the chart.

Here’s the format to create FusionCharts instance:

// Syntax for the instance -         
$var = new FusionCharts(&amp;quot;type of chart&amp;quot;, 
                &amp;quot;unique chart id&amp;quot;, 
                &amp;quot;width of chart&amp;quot;, 
                &amp;quot;height of chart&amp;quot;, 
                &amp;quot;div id to render the chart&amp;quot;, 
                &amp;quot;type of data&amp;quot;, 
                &amp;quot;actual data&amp;quot;)

For our project, this is how we create FusionCharts instance, render chart, and finally close the database connection:

// creating FusionCharts instance
$doughnutChart = new FusionCharts(&amp;quot;doughnut2d&amp;quot;, &amp;quot;browserShareChart&amp;quot; , &amp;quot;100%&amp;quot;, &amp;quot;450&amp;quot;, &amp;quot;doughnut-chart&amp;quot;, &amp;quot;json&amp;quot;, $jsonEncodedData);
    
// FusionCharts render method
$doughnutChart-&amp;gt;render();
	
// closing database connection      
$dbhandle-&amp;gt;close();

This was a basic tutorial to get you started with creating charts using PHP & MySQL, and if you want to explore further please visit this official documentation page.

Now that your website is looking sharp, upgrade your hosting for lightning-fast load times. Get DreamPress today!

About the author

DreamHost

Leaders in web hosting, domain registration, and cloud services for individuals, small businesses, and developers!

2 Comments