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:
Expand|Select|Wrap|Line Numbers
- 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
Expand|Select|Wrap|Line Numbers
- "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:
- We create a chart object
- Set data SQL query
- Set Chart properties and Labels
- Get chart output
Expand|Select|Wrap|Line Numbers
- $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');
See screenshot: bar-db-chart.jpg
Variety of charts are available in this framework, yet simplicity intact.