How To Generate A Pie Chart In PHP MySQL Using The Infographic HTML Tags Library
In this short post I’m going to show you how you can create a pie graph using the Infographic JS HTML Library based on data taken from MySQL. Some of the users of the Infographic Library have been asking this question so I’m trying to answer through this post.
The Infographic HTML Library is created using JavaScript and therefore it is a client side component – that means that it is interpreted and ran by the browser. You can download it from here. So, it is limited in what it can do. In order to retrieve data from a database it needs a server side component. For this post I will show you how you can do that with PHP and MySQL, but any server side technology would work in a similar way (Java, ASP.NET, etc.) and on any relational database (Oracle, MSSQL, etc.)
NOTE: Looking for a way to include pie charts, bar charts and other charts right in your WordPress posts and pages? Take a look at this plugin: Charts and graphs WordPress Visual Designer
These are the things that we will have to do to create our PHP MySQL pie graph:
- Create the MySQL table and populate it with data that we want to display in the piechart (in a real case scenario you will probably already have a table with data coming from an application).
- We will create dynamic pie chart in PHP. For this, create the PHP page that will display the pie graph and add in it the Infographic HTML Library
- In the PHP page created include the code that: connects to the database, executes a database query to retrieve the data and writes back in the page the code to produce the pie chart
How to create the MySQL table to hold the chart data
Usually you manage the MySQL structure and content via a tool like phpMyAdmin. Once logged in you can execute database queries with which you can for example create tables.
In order to draw the pie chart in php with database data, we will have to extract the data and feed it to the chart library.
For this post let’s say that we have a table for storing a website visits. The code for creating this table might look like this:
CREATE TABLE IF NOT EXISTS `website_visits` ( `id` bigint(20) NOT NULL AUTO_INCREMENT, `page` varchar(255) NOT NULL, `_date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP, `country` varchar(255) NOT NULL, PRIMARY KEY (`id`) );
| ID | PAGE | _DATE | COUNTRY |
|---|---|---|---|
| 1 | index.html | 2014-04-10 12:00 | US |
| 2 | index.html | 2014-04-10 12:00 | US |
| 3 | index.html | 2014-04-10 12:00 | Mexico |
| 4 | page1.html | 2014-04-10 12:00 | Brazil |
| 5 | page1.html | 2014-04-10 12:00 | France |
| 6 | page2.html | 2014-04-10 12:00 | Netherlands |
| 7 | page2.html | 2014-04-10 12:00 | US |
| 8 | page2.html | 2014-04-10 12:00 | Germany |
| 9 | page2.html | 2014-04-10 12:00 | Italy |
As you see, this table will store the page that was visited, the date/time when the visits occurred and the country of the visitors.
How to create the PHP page with the chart
This step is very simple to do, just create a text file with the .php extension and upload it to the web server (the server has to know how to interpret PHP). The file can have a regular HTML content, and in its header we will include the reference to the Infographic HTML Library (which you previously downloaded from here; for more information on how to install the library go to the documentation page). The start of the file will look something like:
<html> <head> <script type='text/javascript' src='JS_FILES/infographic.min.js' </head> ...
How To Create A Pie Chart In PHP Using Database Values From MySQL
tweet about creating a pie chart using php and mysqlLet’s see how to create a pie chart in PHP using database values.
In the .php file created we need to insert the code that will connect to the database, retrieve information via a query and write the code for producing the pie graph. Here is the code snippet:
<?php
// Connecting, selecting database
$link = mysql_connect('mysql_host', 'mysql_user', 'mysql_password')
or die('Could not connect: ' . mysql_error());
mysql_select_db('my_database') or die('Could not select database');
// Performing SQL query
$query =
'SELECT country, count(*) visits FROM `website_visits` group by country';
$result = mysql_query($query) or die('Query failed: ' . mysql_error());
// Printing results inside an HTML infographic tag
echo "<infographic-piechart width='300' height='300'>\n";
echo "<infographic-data>\n";
while ($row = mysql_fetch_array($result)) {
echo "<infographic-pieslice value='" .
$row['visits'] . "'>" .
$row['country'] .
"</infographic-pieslice>\n";
}
echo "<infographic-data>\n";
echo "</infographic-piechart>\n";
// Free resultset
mysql_free_result($result);
// Closing connection
mysql_close($link);
?>and the result should display a pie graph similar to this:
Better MySQL Performance Using Views
Before closing, I just want to add that working with MySQL on a daily basis, I’ve often run into performance challenges.
When working with huge amounts of data means that displaying them in a chart can take a long time. This is not ideal for user experience. To improve the performance of charts drawing data from MySQL databases, you can use optimized views.
However, at some point even them will be too slow.
So, is there any alternative?
Materialized views are effectively snapshots of views. They have the performance of regular tables with all the advantages and flexibility of MySQL views.
In one of my posts I’m looking at methods how to create materialized views in MySQL. This is a handy solution for improving MySQL based charts and reports performance.
And That’s How You How To Create A Graph In PHP Using A Database
You can create PHP pie charts using the JavaScript library of your choice. If you don’t know how various charts libraries work you should take a look at these simple examples for how to create pie charts with 3 different libraries.
how can I found Infographic HTML Library trial (free download)?
There is no trial version, only the commercial version http://codecanyon.net/item/infographic-charts-and-graphics-html-tags-library/6351274?ref=psddude and it’s cheaper than your lunch box money.
[…] Hope you enjoyed this post about making a simple pie chart. All these are client side libraries, so they run inside the client browser. If you are interested how to hook charts to data taken from the database, here’s a nice post about making a pie chart with PHP and MySQL. […]
hello , where i can find the javascript file
hi, you can find the JavaScript files for the infographic charts library here: http://codecanyon.net/item/infographic-charts-and-graphics-html-tags-library/6351274?ref=psddude