How to Generate a Chart From a Database

With IPU-Chart you can load the chart’s data via an url. Like this you can access data from a database with a simple service. This example demonstrates how to create a php data service that delivers json data out of a MySQL database. The data will afterwards be rendered as a bar chart with IPU-Chart.

The database: For this example we set up a simple database with fruits and the kilo calories they have. The table is created inside the wordpress database. Like this we can access it afterwards easily from WordPress. But of course you can have your tables also in an other database.

CREATE TABLE wordpress.ipu_fruits (
  id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(45),
  kcal FLOAT);

The data: Then we insert some data in our example database.

INSERT INTO wordpress.ipu_fruits values(null, "Apple", 55.0);
INSERT INTO wordpress.ipu_fruits values(null, "Avocado", 145.0);
INSERT INTO wordpress.ipu_fruits values(null, "Banana", 95.0);
INSERT INTO wordpress.ipu_fruits values(null, "Grapefruit", 30.0);
INSERT INTO wordpress.ipu_fruits values(null, "Kiwi", 55.0);

A service to access the data: To load the data afterwards into the chart we need an url that returns the data. For this example we create a php file with the url https://localhost/downloads/fruits.php. The php fetches the data from the database, adapts it to the chart’s data model and returns an json representation of the data.


Testing the service: Now you can access the data with a browser. If you call https://localhost/downloads/fruits.php you should get something like this:

[{"id":"1","name":"Apple","kcal":"55","<strong>x</strong>":"Apple","<strong>y</strong>":55},
 {"id":"2","name":"Avocado","kcal":"145","<strong>x</strong>":"Avocado","y":145},
 {"id":"3","name":"Banana","kcal":"95","x":"Banana","y":95},
 {"id":"4","name":"Grapefruit","kcal":"30","x":"Grapefruit","y":30},
 {"id":"5","name":"Kiwi","kcal":"55","x":"Kiwi","y":55}]

The x and y attributes will be the x and y of the chart.

Accessing the url from the chart’s configuration: Now we can access the created data service with the uri attribute of the ip4.dataReader:

 

This will display a bar chart on your page.

Don’t forget to replace ‘localhost’ with the name of your server! And if your data service resides not on the same server as the page from where you access it, make sure to allow Cross-Origin Resource Sharing (CORS) to the service.

I hope this example was helpful. Please leave a comment or contact us directly if you need further help or examples.

You can download IPU-Chart at the WordPress Plugin Directory.

Share This On
Tweet about this on TwitterShare on LinkedInShare on FacebookShare on Google+Pin on PinterestShare on TumblrEmail this to someone

Leave a Reply