Nice to see this post as the top result of a Google search for ‘graph mysql data’.
Feb 2017 Update.
If you are using a groov AR1, Node-RED is now included and so you can use that to get the data from the PAC into the MySQL database, skipping the PHP insert scripts. (Steps 2-5 in the list below).
Jan 2016 Update
- jpGraph is still a viable package. It was sold around 2014 / 2015, but still seems to be in active development.
- groov now has up to a weeks worth of graphing built in so we use that now rather than jpGraph.
- This method still works as a proof of concept for pushing data into mySQL from a controller. (Just stop at step 6
- You can do the reverse. The OptoScript calls a (different) PHP script. That script does a select on the DB/Table and provides the data back to the Opto controller. I don’t provide the code here to do that, but it’s simply the reverse of this example.
May 2014. Please consider using OptoDataLink to insert controller data into a database.
The method described here is a pretty big hack, its a great proof of concept, but (perhaps) not much more. Please consider using the right tool for the job.
Major (massive) edit one month after the original post;
We have decided that we are going to completely overhaul internetio.com and so all of the code and examples are going to be removed, so I added it here after a lot of editing and cleaning up.
Just like most of the code here in the forums, the following is not officially supported by Opto 22.
It has worked for me on about 15 different computers over the past few years, I even got it working on a Raspberry Pi computer running Wheezy ARM kernel… That said, you will need to be comfortable working command line in Linux to get this working the way I outline here.
Your millage may vary, but, that said, lets know how you get on.
All this started because I was asked by a user how to get a graph showing data from an Opto controller on a web page.
I figured the best way to answer to answer that was to just do a brain dump on the topic.
Its not trivial, but its pretty cool to get all the bits joined together and see your Opto data plot.
The following has been tested with Ubuntu 10.04 server only. It should work on other Ubuntu versions. I cant speak for other flavors of Linux (Im a Debian / Ubuntu guy from way back).
You will need to start with a LAMP install. This is important. Be sure that you have a full LAMP install before you start!
Here is a picture of the process.
The steps we are going to take are as follows:
- Get MySQL set up to take our data.
- Make the PHP script that inserts our data into MySQL.
- Test the PHP script from a web browser.
- Write the OptoScript to call the PHP script.
- Check the data is flowing into MySQL from Opto via the PHP script.
- Install the graphing package and make the graph.
- View the graph on the web.
So, roll up your sleeve’s and here we go!
-
Setup MySQL. I suggest using phpmyadmin
From a command line on your Linux box, enter the following:
sudo apt-get install phpmyadmin
Log in from your browser:
http://yourserver.com/phpmyadmin
User name is usually root and the password is what ever you set up your MySQL to be.
Create a new user and their password.
Create a new db.
Create a new table.
Be sure and give your user the right to select, insert, update, delete, create and drop on the db. -
Make the PHP script to insert the data.
Create a file in your /var/www that you will call from the optoscript every time you want to insert data.
Here is the code we used:
<?php
// Make a MySQL Connection
$con = mysql_connect("localhost","user","pass");
if (!$con)
{
die('Could not connect: ' . mysql_error());
}
mysql_select_db("optodata",$con);
$value1 = ($_GET['kw']);
mysql_query("INSERT INTO kw (kw) VALUES($value1)");
//two minutes of data (120 points), the extra 7 is for a bit of padding. Probably dont need to cut it so fine.
mysql_query("DELETE FROM kw WHERE timeinsert < (NOW() - INTERVAL 127 SECOND) ");
// not needed, but nice to see the ok come back. You can check for it in optoscript if you like.
echo "ok";
mysql_close($con);
?>
Note this script does something a little different…
What we found when we just inserted the data is that the db got bigger and bigger (duh), and so what happen is that our graph got slower and slower.
The reason for this is that I could not figure out how to do a partial select of the table data and then build the graph from that from within the graph code.
I had to select the whole table, and then grab the last X entries and build my graph from that.
So the work around we came up with is in the PHP code above, when we do the insert, we kill off the oldest inserted data, this way you end up with a first in, last out db.
A cyclic buffer that does not grow any bigger and always has the newest data at the bottom.
This is important, because if you have the newest data at the top, the graph is backwards.
NOTE: If you are doing the insert for other than jpgraph resons, you will need to tweak this code so you do not lose your data.
-
Test the script from a browser.
Fire up your favorite browser (Chrome right?) and tickle the PHP script:
[B]http://url.com/path/to/dbinsert_kw_1min.php?kw=22.2[/B]
You should see ‘ok’ come back at you.
If you check with phpmyadmin, you should see 22.2 with a current time stamp in your db.
This is just inserting one bit of data, but of course you can insert many many many bits of data.
Most Linux builds will let you append over 100 bits of data to a URL, so you can really shift some data around using this method. -
Write the OptoScript to call the tested and working PHP script.
Cut, paste and edit the following code in an OptoScript box in PAC Control.
Download it and debug it.
// Open communications with the web server.
// You need the IP address of the Apache server and what port its listening on.
// "php_mysql_comm_handle" is the name of the communication handle that Opto Control will use.
// By setting it here, we can change it on the fly if we need to talk to more than one database at some stage. (Thinking back up server if the master goes down).
// You could use a URL here rather than a fixed IP address, it could also be a
// remote server over the internet.
SetCommunicationHandleValue("tcp:192.168.1.2:80", php_mysql_comm_handle);
// consider doing the above in its own block
// This next block of code gets run as often as you want to insert data into the db.
// Be sensible! Once a second is about as quick as you probably want to do it on a local
// server, slower over the web.
// First convert the data you want to send into a string.
FloatToString(femu_true_power, 5, 2, semu_true_power);
//If there is any leading or trailing white space, it will break the URL, so trim them off.
TrimString(semu_true_power, 3);
// build the string we are going to send.
php_sql_url_string = "POST http://url.com/path/to/dbinsert_kw_1min.php?kw=" + semu_true_power;
php_sql_url_string += chr(13); //append CR
php_sql_url_string += chr(10); //append LF
// Send the URL to the other computer.
// (Note, no error checking in ver .01).
php_com_result = OpenOutgoingCommunication(php_mysql_comm_handle);
php_com_result = TransmitString(php_sql_url_string, php_mysql_comm_handle);
php_com_result = CloseCommunication(php_mysql_comm_handle);
-
Check the OptoScript is working.
Use phpmyadmin again to check that your data is getting inserted ok.
Make sure that its limiting itself to the right length table, that its not growing unchecked. -
Install the graphing package and make the graph.
First step of this is to install jpgraph.
To do this, download it from the link.
I put it in /var/www/jpgraph.
Unzip it with sudo tar zxvf jpgraph-3.5.0b.tar.bz2
You will need to change the file name if you get a different version.
Once you extract it into a directory with the file version, move it so its easier to deal with;
sudo mv /var/www/jpgraph/jpgraph-3.5.0b1/* /var/www/jpgraph/
Clean things up;
sudo rmdir /var/www/jpgraph/jpgraph-3.5.0b1/
Next you need to get the fonts that jpgraph uses;
sudo apt-get install msttcorefonts
Now you need to tell jpgraph were to find those fonts;
sudo nano /var/www/jpgraph/src/jpg-config.inc.php
Once in that file, change the following line;
// define(‘TTF_DIR’,‘/usr/share/fonts/truetype/’);
into;
define(‘TTF_DIR’,’/usr/share/fonts/truetype/msttcorefonts/');
[Note, just watch the case of the ‘truetype’ here, some installs of Linux have it at ‘TrueType’.
Do a ‘sudo find / -name arialbd.ttf’ to see the path of this important font].
That’s it, you should be up and running. Test it by opening a browser here;
[B]http://yourwebserver.url/jpgraph/src/Examples/testsuit.php[/B]
If you get a bunch of blue error boxes saying something about the function imageantialias not being available in your PHP, try the following gak;
sudo nano /var/www/jpgraph/src/gd_image.inc.php
Search for the function ‘SetAntiAliasing’.
Comment it out like thus;
// JpGraphError::RaiseL(25128);//(‘The function imageantialias() is not available in your PHP installation. Use the GD version that comes with PHP and not the standalone version.’)
Yes. Its an ugly hack. We are just turning off antialiasing in jpgraph.
Once jpgraph is up and running, you should see a bunch of different graphs you can do by looking at that testsuite.php
If you get a lot of blank or broken graphs, try changing the permissions of all the files to 777 to see if that fixes it. On one install I had to change the owner of the /var/www/jpgraph -R to www-data to get it all working.
If you still have trouble, trying making the graph below and un-comment that first line that shows all errors, its been really really helpful in tracking down ‘blank graph’ errors.
Its a pretty cool but very complicated graphing package.
Sadly its not being maintained any more and I while I would love to move to another (far simpler) package, I started out with this thing and so its the one I ‘know’ best.
Now that jpgraph is working, time to build a graph with our data.
Here is some sample jpgraph code to get you started.
Cut and paste this code into a file on your server in your web path.
Something like /var/www/graph.php
<?php
include ("/var/www/jpgraph/jpgraph.php");
include ("/var/www/jpgraph/jpgraph_line.php");
require_once("/var/www/jpgraph/jpgraph_date.php");
//error line below is VERY helpful tracking down blank graph errors.
//error_reporting(E_ALL); ini_set('display_errors', '1');
mysql_connect("localhost", "user", "pass");
mysql_select_db ("optodb");
$datay = array();
$query=("SELECT * FROM opto_power ORDER BY datetimeinserted DESC LIMIT 10080"); //5760 for a day@ 15 second insert rate.
$result=mysql_query($query);
$rowcount = mysql_num_rows($result);
if ($rowcount == 10080){
// if the DB has enough data to make a complete graph fill array and go
while($row = mysql_fetch_array($result)) {
$datay[] = $row['kw'];
}
}else{
// if the DB does not have enough data to make a complete graph
// add what's in DB so far to the array and fill the rest with 0 values
while($row = mysql_fetch_array($result)){
$datay[] = $row['kw'];
}
}
//var_dump($datay);
// Setup the graph
DEFINE('NDATAPOINTS',10080); //5760 for a day@15 second insert rate
DEFINE('SAMPLERATE',60); // Must match insert rate in OptoScript.!! eg. 15 second sample rate for the day graph, every 60 seconds for week.
$start = time();
$end = $start+NDATAPOINTS*SAMPLERATE;
$xdata = array();
for( $i=0; $i < NDATAPOINTS; ++$i ) {
$xdata[$i] = $start - $i*SAMPLERATE;
}
$graph = new Graph(640,320);
$graph->SetMargin(40,40,25,80);
$graph->title->Set('Opto Power - 1 week');
$graph->title->SetFont(FF_ARIAL,FS_BOLD,12);
$graph->SetScale("datlin",0,0);
//$graph->xaxis->scale->SetTimeAlign( SECADJ_1 ); // try getting more data than you need to fill the graph to fill the gap at the end.
//$graph->xaxis->scale->ticks->Set(5,10); //un-comment force the time scale.
// Create the line
$p1 = new LinePlot($datay,$xdata);
$p1->SetColor("blue");
$graph->xaxis->SetFont(FF_ARIAL,FS_NORMAL,8);
$graph->yaxis->SetFont(FF_ARIAL,FS_NORMAL,8);
$graph->xaxis->SetLabelAngle(45);
// Set the fill color partly transparent
$p1->SetColor("blue");
$p1->SetFillGradient('red','green');
// Add lineplot to the graph
$graph->Add($p1);
// Output line
$graph->Stroke();
?>
The tricky things are the interval and the time scale across the bottom.
Sorry, but your on your own.
Read the jpgraph docs and look at the samples you saw from running the testsuite.php
If you dont get any graph at all, you should get an error message, use that to guide Google to help you.
Also try commenting out the var_dump and check the data you are getting back from the select statement, perhaps that’s where its choking.
Here is some sample code for a multiline graph with out the fancy select statement.
If you don’t have enough data in the table, you will get an error message.
<?php
include ("/var/www/jpgraph/jpgraph.php");
include ("/var/www/jpgraph/jpgraph_line.php");
mysql_connect("localhost", "user", "pass");
mysql_select_db ("demo1");
$datay = array();
$datay2 = array();
$datay3 = array();
$query=("SELECT * FROM demo1pid");
$result=mysql_query($query);
while($row = mysql_fetch_array($result)) {
$datay[] = $row['temp'];
$datay2[] = $row['sp'];
$datay3[] = $row['fan'];
}
// Setup the graph
$graph = new Graph(300,125);
$graph->SetMargin(40,80,15,12);
$graph->SetMarginColor('white');
$graph->SetScale('intint');
$graph->title->Set('SNAP-PAC-R1 PID Loop');
$graph->title->SetFont(FF_FONT1,FS_BOLD);
$p1 = new LinePlot($datay);
$graph->Add($p1);
$p1->SetWeight(2);
$graph->yaxis->scale->SetAutoMin(70);
//$graph->yaxis->scale->SetAutoMax(100);
$graph->SetYScale(0,'lin');
$p2 = new LinePlot($datay2);
$p2->SetColor('blue');
$graph->AddY(0,$p2);
$graph->ynaxis[0]->SetColor('blue');
$graph->SetYScale(1,'lin',0,100);
$p3 = new LinePlot($datay3);
$p3->SetColor('red');
$graph->AddY(1,$p3);
$p3->SetWeight(2);
$graph->ynaxis[1]->SetColor('red');
$graph->ynaxis[0]->scale->SetAutoMin(70);
//$graph->ynaxis[0]->scale->SetAutoMax(100);
$graph->xaxis->title->Set("time");
$graph->yaxis->title->Set("temp/sp/fan");
$graph->footer->left->Set(' temp=black sp=blue fan=red');
// Set the legends for the plots
//$p1->SetLegend("Plot 1");
//$p2->SetLegend("Plot 2");
//$p3->SetLegend("Plot 3");
// Adjust the legend position
//$graph->legend->SetLayout(LEGEND_HOR);
//$graph->legend->Pos(0.4,0.99,"center","bottom");
// Output line
$graph->Stroke();
?>
- To test the graph, hit it with a browser:
http://yourserver.com/graph.php
It should show up instantly.
Bask in its beauty.
Hit F5 to refresh the screen and see the data scroll across the page.
If you want to have it auto refresh in a web page, you will need to learn some javascript and ajax.
Sorry, I dont have any code for that.
Its on the web. Guide you Google shall.
March 2016. Edit. You can put these graphs into groov and have them auto update really simply.
Check out my blog post for how to do this;
Note that in groov you can actually link the Video Gadget straight to the PHP script since it returns a jpg.
I have been using this ‘data to graph’ method for a few years now, groov makes it super simple to display simple sets of MySQL data as a graph and auto updates them as long as you are comfortable with the OptoScript and PHP to make it happen. It seems very stable and reliable.