While data will most often be used as a tool to assist in making decisions, it sometimes also serves as a motivator. Live dashboards can allow your client to react quickly to any changes in their KPIs, or can assist a sales team in seeing how close they are to their daily targets.
Stick them in fullscreen mode on a wall display or a TV, and they will be there to offer this transparency non stop.
There are quite a few pieces of software out there that allow you to create live, automatically updating, automatically refreshing dashboards, but most of them cost more than it’s worth, and some are quite glitchy. I will not go into these tools now, but will rather offer a very fast and easy alternative.
When looking for this, my first requirement was that the refresh is easy to accomplish and smooth. HTML offers that, so I wanted something that can be wrapped in HTML.
HTML also offers the advantage of being flexible in regards to where you put it: as standalone on a dedicated web page, or embedded in an admin frontend or dashboard panel.
Google has a nice free Javascript API for creating charts, compatible with data pulled from almost any source. You can find more details about the look and feel of it here.
My final result was this, for a daily chart updated every 3 minutes:
Google does not provide any connectors for MySql or other common Sql, and most often that’s where live data will be.
I tried looking around for some resources that will pull the sql data into the chart, but my search was not very fruitful. Google API wants the data in a very specific format, so I took the liberty of writing a little PHP to do the job.
If you are not interested in how this works, just scroll all the way down and copy paste the full code into Notepad or TextEdit and save it as PHP. If you want to edit the code, I use Notepad++ as it highlights the operators.
1. First part: connecting to the database
|
1 2 3 4 5 6 7 8 9 10 11 |
<?php //define host, database name, user and pass: $db_host = 'YOUR Host IP/URL:PORT' $db_database = 'YOUR_DATABASE'; $db_user = 'USERNAME'; $db_password = 'Password'; //connect: $db = mysql_connect($db_host, $db_user, $db_password); mysql_select_db($db_database); ?> |
2. Second part: transforming the data in the right format. We do this with an echo.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 |
<?php // My sql table has the columns Date, Margin, Target, Last month Margin, so I want the lines to //contain summed up values for each day. Except target, which is the monthly target on each row, //to create a horizontal line $sqlQuery = "SELECT fulldate, round((SELECT SUM(last_month_margin) FROM dummy_value_daily d2 WHERE d1.fulldate>=d2.fulldate),2) AS last_month_margin , round(Target,2) AS Target, round((SELECT SUM(margin) FROM dummy_value_daily d2 WHERE d1.fulldate>=d2.fulldate),2) AS margin FROM dummy_value_daily d1 group by 1"; $sqlResult = mysql_query($sqlQuery); while ($row = mysql_fetch_assoc($sqlResult)) { //echo to put it in the right format echo " data.addRow(['{$row['fulldate']}', {v: {$row['last_month_margin']}, f: '€ {$row['last_month_margin']}' }, {v: {$row['Target']}, f: '€ {$row['Target']}' }, {v: {$row['margin']}, f: '€ {$row['margin']}' } ]); "; } ?> |
3. Final part: creating the final PHP with the Google API. You can find all the documentation for Google Charts API here
Note the refresh is set to 180 seconds with meta http-equiv="refresh" content="180" The php is run, query pulls data, and only after that is successful, the chart refreshes, so the update will be very smooth to the eye.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 |
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Strict//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd"> <html xmlns="http://www.w3.org/1999/xhtml"> <head> <meta http-equiv="refresh" content="180" > <meta http-equiv="content-type" content="text/html; charset=utf-8"/> <title> Google Visualization API Sample </title> <script type="text/javascript" src="http://www.google.com/jsapi"></script> <script type="text/javascript"> google.load('visualization', '1', {packages: ['corechart']}); </script> <script type="text/javascript"> function drawVisualization() { // Create and populate the data table. var data = new google.visualization.DataTable(); data.addColumn('string', 'date_minute'); data.addColumn('number', 'Last Month'); data.addColumn('number', 'Target'); data.addColumn('number', 'Margin'); <?php $db_host = 'your_host'; $db_database = 'your_Database'; $db_user = 'your_user'; $db_password = 'your_password'; $db = mysql_connect($db_host, $db_user, $db_password); mysql_select_db($db_database); $sqlQuery = "SELECT right(fulldate,2) as fulldate, round((SELECT SUM(last_month_margin) FROM dummy_value_daily d2 WHERE d1.fulldate>=d2.fulldate),2) AS last_month_margin , round(Target,2) AS Target, round((SELECT SUM(margin) FROM dummy_value_daily d2 WHERE d1.fulldate>=d2.fulldate),2) AS margin FROM dummy_value_daily d1 group by 1"; $sqlResult = mysql_query($sqlQuery); while ($row = mysql_fetch_assoc($sqlResult)) { echo " data.addRow(['{$row['fulldate']}', {v: {$row['last_month_margin']}, f: '€ {$row['last_month_margin']}' }, {v: {$row['Target']}, f: '€ {$row['Target']}' }, {v: {$row['margin']}, f: '€ {$row['margin']}' } ]); "; } ?> // Create and draw the visualization. new google.visualization.LineChart(document.getElementById('visualization')). draw(data, {curveType: "none", title: "Monthly Margin", titleTextStyle: {color: "orange"}, width: 1600, height: 400, //vAxis: {maxValue: 10}, vAxis: {minValue: 0}, vAxis: {title: 'Euro'}, vAxis: {baseline: 0}, vAxis: {gridlines: {count: 10} }, vAxis: {title: "Euro", titleTextStyle: {color: "orange"}}, hAxis: {title: "Day", titleTextStyle: {color: "orange"}}, interpolateNulls: 1 } ); } google.setOnLoadCallback(drawVisualization); </script> </head> <body style="font-family: Arial;border: 0 none;"> <div id="visualization" style="width: 500px; height: 400px;"></div> </body> </html> |
4. Running the PHP file
To run the PHP you need a dev server, a web server, or someone who can upload it somewhere for you. I will cover how to install a lightweight dev server in a separate post. I recommend EasyPHP dev server if this is all you will ever do with it, alternatively XAMPP is a better option allowing you to publish to web as well. Guide for installing and configuring easyphp here.
5. Result:
So I hope you enjoyed this guide, and that you will find this useful. One final note: Google API has all kinds of charts, and it’s easy to switch between types. You can change this chart to a column chart by changing just one word in the html. Just give it a shot 🙂
I did not, how many tables are, I tried to do as in ALL and is not working, please explain.
Hola! I’ve been reading your weblog for a while now and finally got the bravery to go
ahead and give you a shout out from Humble Tx! Just wanted
to mention keep up the good work!
can u plz give me the raw sql database file of this program
Having read this I thought it was very enlightening. I appreciate you finding the time and
effort to put this short article together. I once again find myself
spending a significant amount of time both reading and commenting.
But so what, it was still worthwhile!
HI, this article has been very usefull to me. I then tried to copy and paste to a php file and have it working but it did not. The reason was that copying and pasting code that is views as normal text changed all the ” and ‘ and i had to retype them all.
Another thing : you accidentally left this peice of code vAxis: {title: ‘Euro’}, that is useless because it gets overwritten from this one vAxis: {title: “Euro”, titleTextStyle: {color: “orange”}},
If i can suggest you, use this nice plugin for formatting code correctly and allowing people to copy and paste the code without having to correct all the code : http://www.marosomarco.eu/wp-admin/plugin-install.php?tab=plugin-information&plugin=crayon-syntax-highlighter&TB_iframe=true&width=600&height=550
Thanks for the valid information. Have a look at my post about this : http://www.marosomarco.eu/2016/01/06/creare-grafici-con-google-charts-api-e-mysql/
Hi,
My google chart should look like this http://www.onnetsourcing.com/vivera/chart.html .
Only my issue is below is the data edited manually and chart looks fine.
var dataSet = [
[‘Date’,’1st Floor’, ‘2nd Floor’, ‘3rd Floor’, ‘4th Floor’, ‘5th Floor’, ‘6th Floor’, ‘7th Floor’, ‘Tarrace Floor’],
[‘A Block’, 1, 1, 1, 0, 0, 0, 0, 0],
[‘B Block’, 1, 1, 1, 1, 1, 1, 1, 1],
[‘C Block’, 1, 1, 1, 1, 1, 1, 1, 1],
[‘D Block’, 1, 1, 1, 1, 1, 1, 1, 1],
[‘E Block’, 1, 1, 0, 0, 0, 0, 0, 0],
[‘F Block’, 1, 1, 0, 0, 0, 0, 0, 0],
];
But for me it should take the data from mysql tables. Please help me for the script. I have tried many scripts and unable to do so.
My data base tables to take the data are as below:
1st Floor, 2nd Floor (Table name: projecttaskname)
1,0 (Table name: projecttaskstatus )
A Block, B Block (Table name: cf_873)
My Objective: We have total 7 Blocks(towers). If 1st floor completed graph should go up by one. Similarly for all 7 blocks. I can manually enter the data but I need automatically taken from mysql database. Kindly help me on this.
Regards,
Krishna
Appreciate this post. Will give it a try.