JPGraph from SQL data

[mcrossley]

Wehey! I have finally mastered enough PHP to start to extract the data from MySQL that has been populated from the Day file.

A simple start, just the year to date temperatures (my data only starts on the 13th Jan though )
EDIT: Now a 'final version': http://weather.wilmslowastro.com/graphs_historic.php

Now to produce some more graphs and add them to a web page....

Mark

EDIT: PS - I know my data logger hung at 5:30 this morning, it will have to wait until I get home this evening Bloomin' XP!

[daj]

Excellent Mark, well done.

I want to move onto JPGraphs too but two weekends ago when I set aside some quality time their site was down for the weekend so I could not get the documentation Something to do with their hosting server being upgraded!

[nitrx]

I like those graphs only don't know how to generate te I saw a script http://www.weather-watch.com/smf/index. ... 784.0.html onthis forum (you can dowload it it's attached in the first topic I think an example of selctable graps is here http://www.hasslosa.se/weather/wd/wx6.php I think the data are colleceted from weather display in a mysql database (i'm not going to read the manual of Jgraps too much English )

[mcrossley]

If anyone else wants a quick start with JpGraphs here is my PHP code for the 'Year to date Temperatures' chart above...

Be warned though that there many be some 'issues' with this that I haven't seen yet!

Code: Select all

<?php
require_once ("jpgraph/jpgraph.php");
require_once ("jpgraph/jpgraph_line.php");
require_once ("jpgraph/jpgraph_date.php");
include ('../private/db_ro_details.php');

// Connect to the database
$con=mysql_connect($dbhost,$dbuser,$dbpassword);
if (!$con) { echo "failed to connect to the database server"; exit(); }

if (!mysql_select_db($database,$con)) { echo "Failed to connect to the database on the server"; exit();}

#
# The db query
#
$query = "SELECT UNIX_TIMESTAMP(LogDate) AS LogDate, MaxTemp, MinTemp, AvgTemp FROM DayData WHERE LogDate >= DATE_FORMAT(CURDATE(), '%Y-01-01') ORDER BY LogDate ASC";

$result = mysql_query($query);
if (!$result) {printf ("ERROR - Bad Select Statement"); exit;}

// import the rows and put the data into arrays
$cpt=0;
while($row = mysql_fetch_row($result))
{	      
	$valMax[$cpt]=$row[1];
	$valMin[$cpt]=$row[2];
	$valAvg[$cpt]=$row[3];
	$title[$cpt]=$row[0];
	$cpt++;
}


#
#  Graph data with labels
#
$graph = new Graph(700,300,"auto");
$graph->img->SetMargin(40,40,40,50);

//$graph->SetBackgroundImage("ship2.png",2);
// Adjust brightness and contrast for background image
// must be between -1 <= x <= 1, (0,0)=original image
//$graph->AdjBackgroundImage(0.5,-0.8);
//$graph->img->SetAntiAliasing();

// Use auto Date graph with integer steps
$graph->SetScale("datint");

// Set X-Axis date format to DD-MON
$graph->xaxis->scale->SetDateFormat("d-M");

// Set the angle for the x-axis labels to 90 degrees
$graph->xaxis->SetLabelAngle(90);

// Set labels to start on 1st of the Month
$graph->xaxis->scale->SetTimeAlign( MONTHADJ_1 );

// Set date labels at the bottom
$graph->xaxis->SetPos('min');

$graph->SetShadow();
$graph->title->Set("Year to Date Temperatures");
//$graph->title->SetFont(FF_FONT1,FS_BOLD);
//$graph->xaxis->title->Set('Date');
$graph->yaxis->title->Set('Celcius');

// Adjust the legend position
$graph->legend->SetLayout(LEGEND_HOR);
//$graph->legend->SetPos(0.4,0.95,"right","top");

// Add 5% grace to top and bottom of plot
$graph->yscale->SetGrace(5,5);
#
# Now plot data
#
$p1 = new LinePlot($valMax, $title);
//$p1->mark->SetType(MARK_FILLEDCIRCLE);
//$p1->mark->SetFillColor("red");
//$p1->mark->SetWidth(4);
$p1->SetColor("red");
$p1->SetLegend('Max');
//$p1->SetCenter();
$graph->Add($p1);

$p2 = new LinePlot($valMin, $title);
//$p2->mark->SetType(MARK_FILLEDCIRCLE);
//$p2->mark->SetFillColor("red");
//$p2->mark->SetWidth(4);
$p2->SetColor("blue");
$p2->SetLegend('Min');
//$p2->SetCenter();
$graph->Add($p2);

$p3 = new LinePlot($valAvg, $title);
//$p3->mark->SetType(MARK_FILLEDCIRCLE);
//$p3->mark->SetFillColor("red");
//$p3->mark->SetWidth(4);
$p3->SetColor("green");
$p3->SetLegend('Avg');
//$p3->SetCenter();
$graph->Add($p3);

// render the graph
$graph->Stroke();

?>

[nitrx]

include ('../private/db_ro_details.php');

means

[mcrossley]

Ah, I just keep the database details in a seperate file which isn't accessible from the web service for a bit of security.

The contents of that file is simply...

Code: Select all

<?php
// The server host name or number running your MySQL database
// usually 127.0.0.1 or localhost will suffice
$dbhost 	= "dbhostname";
//
// The username used to log-in to your database server
$dbuser 	="username";
//
// The password used to log-in to your database server
$dbpassword	="password";
//
// The name of the MySQL database we will store the tables in
$database	="dbname";
?>

[daj]

I'm taking a guess that it will be setting these variables to your own settings

$dbhost,
$dbuser
$dbpassword
$database

You would normally have them in another file

[mcrossley]

I also thought it may be a good idea to keep a collection of 'useful' SQL select statements for use with the data tables.

Here is another that collects the temperature readings for the 'previous' calendar month:

Code: Select all

SELECT LogDate, MaxTemp, MinTemp FROM DayData WHERE LogDate BETWEEN DATE_FORMAT(CURDATE() - INTERVAL 1 MONTH, '%Y-%m-01') AND LAST_DAY(CURDATE() - INTERVAL 1 MONTH)

Change the first "Interval 1" to "Interval 3" for the last quarter etc.

[nitrx]

Oke thanks clear to me

[nitrx]

Works great http://www.apeldoorn.tk/weer/grafiek/

Had to look up for the table my name is different

$query = "SELECT UNIX_TIMESTAMP(LogDate) AS LogDate, MaxTemp, MinTemp, AvgTemp FROM DayData WHERE LogDate >= DATE_FORMAT(CURDATE(), '%Y-01-01') ORDER BY LogDate ASC";

Only have to verify the data

[mcrossley]

That's great confirms that it starts on the 1st Jan too . I need to figure out how to move the legend into the title area, I can get it in the x-axis label area but not above the graph area for some reason. JpGraphs looks pretty comprehensive, shame the Windrose graph is only available in the pay-for version

[nitrx]

I removed the legend out because it came across some lines I did just a wild guess

// Adjust the legend position
//$graph->legend->SetLayout(LEGEND_HOR);
$graph->legend->SetPos(0,0,"right","top");

BTW the graphs must be included in a .html or php page because IE tries to download them, anyway I'm jus experimenting

[TNETWeather]

Ah, I just keep the database details in a seperate file which isn't accessible from the web service for a bit of security.

Even then if your MySQL server is offline, you can get an error which gives away your settings.

If you put a @ before the mysql_connect, it will suppress the error message if there is one.

Like:

Code: Select all

$con=@mysql_connect($dbhost,$dbuser,$dbpassword);

From: http://php.net/manual/en/function.mysql-connect.php

Note: You can suppress the error message on failure by prepending a @ to the function name.

[mcrossley]

Top tip, thanks for that.

[nitrx]

Any idea how to change the query

$query = "SELECT UNIX_TIMESTAMP(LogDate) AS LogDate, MaxTemp, MinTemp, AvgTemp FROM DayData WHERE LogDate >= DATE_FORMAT(CURDATE(), '%Y-01-01') ORDER BY LogDate ASC";

? to something to get the data between two dates by example 19-12-2009 and 01-01-2010 I can't figure it out ,some tries where

$query = "SELECT UNIX_TIMESTAMP(LogDate) AS LogDate, MaxTemp, MinTemp, AvgTemp FROM DayData WHERE LogDate BETWEEN '19-12-2009 AND '01-01-2010' ORDER BY LogDate ASC";

As far I could find the DATE is mysql is treated in another way by PHP .. I think the DATE is a confusing parameter

With this query it's eaysier to collect monthly graphs