Creating a simple Bar Chart with SQL Query

[azghanvi1]

Introudction

A lot of PHP based libraries are currently in market that support chart types. The important aspect is how they are connecting with our data source as mostly they are used with some database (e.g. mysql or sqlite).

Getting Chart data from Database

For this article, i am fetching data from northwind sqlite database. Lets say we like to display bar chart of product category and overall sales. The query would be like:

select c.categoryname, sum(a.quantity) as Sales
				from products b, `order details` a, categories c
				where a.productid = b.productid and c.categoryid = b.categoryid
				group by c.categoryid
				order by c.categoryid

This would result in following data:

"Beverages"		"9532"
"Condiments"		"5298"
"Confections"		"7906"
"Dairy Products"	"9149"
"Grains/Cereals"	"4562"
"Meat/Poultry"		"4199"
"Produce"		"2990"
"Seafood"		"7681"

Chart Code

I will be using PHP Charts Framework that uses pretty simple API to connect to database and draw your desired chart type.

Here are the steps:

1. We create a chart object
2. Set data SQL query
3. Set Chart properties and Labels
4. Get chart output

$p = new chartphp();

$p->data_sql = "select c.categoryname, sum(a.quantity) as Sales
				from products b, `order details` a, categories c
				where a.productid = b.productid and c.categoryid = b.categoryid
				group by c.categoryid
				order by c.categoryid";
					
// Line Data
$p->chart_type = "bar";

// Common Options
$p->title = "Category Sales";
$p->xlabel = "Category";
$p->ylabel = "Sales";

$out = $p->render('c1');

This code will generate a database driven bar chart directly from your database.

See screenshot:

Variety of charts are available in this framework, yet simplicity intact.

[Shyam Jaiswal]

nice site for a developer

[Ajay Bhalala]

Hi,

It's very nice.

[RankFuse]

nice solution. I'm starting to run all dashboards on Cyfe. It's cheap and easy, plus it looks good and I can share it.

[fizzwizz]

What a code, always wanna be a programer :D

[azghanvi1]

Just to update followers: Chart4PHP v2.0 is released with a lot of new features and simplicity. More details: http://www.chartphp.com/charts-4-php-v2-0-released/