By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
437,913 Members | 1,305 Online
Bytes IT Community
Submit an Article
Got Smarts?
Share your bits of IT knowledge by writing an article on Bytes.

Creating a simple Bar Chart with SQL Query

P: 2
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:

Expand|Select|Wrap|Line Numbers
  1. select c.categoryname, sum(a.quantity) as Sales
  2.                 from products b, `order details` a, categories c
  3.                 where a.productid = b.productid and c.categoryid = b.categoryid
  4.                 group by c.categoryid
  5.                 order by c.categoryid
  6.  
This would result in following data:
Expand|Select|Wrap|Line Numbers
  1. "Beverages"        "9532"
  2. "Condiments"        "5298"
  3. "Confections"        "7906"
  4. "Dairy Products"    "9149"
  5. "Grains/Cereals"    "4562"
  6. "Meat/Poultry"        "4199"
  7. "Produce"        "2990"
  8. "Seafood"        "7681"
  9.  

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

Expand|Select|Wrap|Line Numbers
  1. $p = new chartphp();
  2.  
  3. $p->data_sql = "select c.categoryname, sum(a.quantity) as Sales
  4.                 from products b, `order details` a, categories c
  5.                 where a.productid = b.productid and c.categoryid = b.categoryid
  6.                 group by c.categoryid
  7.                 order by c.categoryid";
  8.  
  9. // Line Data
  10. $p->chart_type = "bar";
  11.  
  12. // Common Options
  13. $p->title = "Category Sales";
  14. $p->xlabel = "Category";
  15. $p->ylabel = "Sales";
  16.  
  17. $out = $p->render('c1');
  18.  
This code will generate a database driven bar chart directly from your database.

See screenshot: bar-db-chart.jpg

Variety of charts are available in this framework, yet simplicity intact.
Apr 1 '15 #1
Share this Article
Share on Google+
5 Comments


P: 1
nice site for a developer
Jun 16 '15 #2

Ajay Bhalala
100+
P: 119
Hi,

It's very nice.
Jul 1 '15 #3

RankFuse
P: 1
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.
Sep 23 '15 #4

fizzwizz
P: 1
What a code, always wanna be a programer :D
Nov 21 '16 #5

P: 2
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/
Oct 24 '17 #6