Thanks to Picolio 
http://www.weather-display.com/downloadfiles/WeatherGraphs.zip
its just a start
if you can exapnd on them, share please
Chris, the forum admin, has it working on his server
Sorry Brian…I’ve been a little distracted with other things to get back to you about these.
For others trying this…make sure that you have the gd library extensions installed and enabled in PHP. This is supposed to be enabled by default, but it wasn’t in my version for some reason. Also you’ll need to install the jpgraph PHP utility.
Otherwise, check what you’re getting from your database and how it’s indexed. I think by default it reads every record…which if you’re doing 10 seconds updates will take some time. There is a significant delay on my server between asking for the data and it being plotted. I think that’s just down to the number of records to be read. My database has approx 800,000 records in it, so an unindexed read won’t be fast and will clobber server performance.
Finally, I’ve got a problem reading the arial.ttf X11 font on my server for the ‘period’ graph. I’m still not sure why it’s not picking the file up, because the file it wants is in the correct place.
yup, thats why i put it up on the forum, as I know your busy
and so we can get help from others
there must be a way for it to know only to use the last part of the database to get the records from??
The SQL query to get data from the database and the PHP code can be modified to grab whatever data people want to display. However, database indexes are important. If the data table has 1 million records with no indexes defined and you say ‘Get all data for November 2004’, the database server has to read every single record in the database to check if it matches the test. So 1 million record reads are required.
If you have an index on the date, the same query would very quickly use the index to access just the specific records that are required, ignoring the hundreds of thousands of records that don’t match. I’ve not looked to see how the query in this code works, but I suspect that it’s not reading records via an index. I assume that most people will want to select data based on date ranges, so a date index is probably a useful addition. When I get a chance I’ll try that and see how much difference it makes to the speed of the code.
I’ve now fixed my font problem and created a graph showing temparature. If you’d like to see the graphs, they’re at:
http://www.weather-watch.com/wx_graphs/graphhumid.php
http://www.weather-watch.com/wx_graphs/graphtemp.php
I’ll do some more tweaking, especially to see if I can speed up response times and then post the code. I’ll also see if I can get a version that plots multiple values on the same graph. Converting from Humidity to Temp was easy enough, so if we can generate some examples of other types of graph it should be fairly easy for people to create their own favourite graphs.
Another example of what can be done with this. This http://www.weather-watch.com/wx_graphs/graphtest.php is a graph with two plotted lines (temp and humidity) with two y-axes displayed (showing temp and humidity respectively) and also in a larger size (600*400).
What is the length of time for the SQL query vs the time it takes to generate the image?
In other words, what is taking the time between submitting the date range and the image showing up.
wow, great work!
the time span works really well!
this would be excellent for the liks of fruit growers, insurance companies, etc!
I don’t know yet. I’ve been getting my head round the way the code works before I try to debug it. I think part of the problem might be selecting records from the database using a query that hasn’t got an index to work on.
I was directed here and this is looking like what I was trying to do. My next step was to create checkboxes for each item that could be graphed. Then the person viewing the page could choose whatever was of interest: humidity, temperature (indoor or outdoor), rainfall, wind speed / direction, etc.
Any thoughts about adding that to this page? Would it be possible to post the code fo the graphtest.php with the two charts?
Thanks,
Sorry, I forgot to post these…
graphtest.php…
<?PHP
include("graphconf.php");
if ($_POST["month"]==$_POST["month2"] && $_POST["day"]==$_POST["day2"] && $_POST["year"]==$_POST["year2"])
{
$_POST["month2"] = null;
$_POST["day2"] = null;
$_POST["year2"] = null;
}
if ($_POST["date2"] && $_POST["year"] && $_POST["month"] && $_POST["day"] && $_POST["year2"] && $_POST["month2"] && $_POST["day2"]){
$extra = "?t=".$_POST["year"]."-".$_POST["month"]."-".$_POST["day"]."&t2=".$_POST["year2"]."-".$_POST["month2"]."-".$_POST["day2"];
} elseif (!$_POST["date2"] && $_POST["date"] && $_POST["year"] && $_POST["month"] && $_POST["day"]) {
$extra = "?t=".$_POST["year"]."-".$_POST["month"]."-".$_POST["day"];
}
$title = "#7E9CB8";
$row = "#E6EFFE";
?>
<table valign=center border="0" cellpadding="0" cellspacing="0">
<tr valign=top align=center><td colspan="4"><?php echo "<img src=\"".$url."/graph_test.php".$extra."\" alt=\"Loading. Please Wait...\" border=\"0\" width=\"600\" height=\"400\">"; ?></td></tr>
<tr valign=top align=center><td colspan="4" bgcolor="<?=$title?>" style="border: 0px; border-style: solid; border-color: #000000; border-left-width: 1px; border-right-width: 1px;"><font color="#ffffff"><b>Select Date(s) to graph temperature & humidity for:</b></font></td></tr>
<tr><td bgcolor="<?=$row?>" style="border: 0px; border-style: solid; border-color: #000000; border-left-width: 1px; border-right-width: 1px; border-bottom-width: 1px;">
<table align="center" border="0" cellpadding="0" cellspacing="0">
<form method="post" action="<?=$url?>/graphtest.php">
<tr>
<td bgcolor="<?=$row?>">
Start Date:
<select name="month">
<?php
if (!$_POST["month"])
{
$curmonth = date("n");
} else {
$curmonth = $_POST["month"];
}
$monthname = array("", "January", "February", "March", "April", "May", "June", "July", "August", "September", "October", "November", "December");
$i=1;
while ($i<=12){
if ($curmonth==$i){
echo "<option SELECTED value=\"".$i."\">".$monthname[$i]."</option>";
} else {
echo "<option value=\"".$i."\">".$monthname[$i]."</option>";
}
++$i;
}
?>
</select>
</td><td bgcolor="<?=$row?>">
<select name="day">
<?php
if (!$_POST["day"])
{
$curday = date("j");
} else {
$curday = $_POST["day"];
}
$i=1;
while ($i<=31){
if ($curday==$i){
echo "<option SELECTED value=\"".$i."\">".$i."</option>";
} else {
echo "<option value=\"".$i."\">".$i."</option>";
}
++$i;
}
?>
</select>
</td><td bgcolor="<?=$row?>">
<select name="year">
<?php
$curyear = date("Y");
if (!$_POST["year"]){
$year = $curyear;
} else {
$year = $_POST["year"];
}
$i=2004;
while ($i<=$curyear){
if ($year==$i){
echo "<option SELECTED value=\"".$i."\">".$i."</option>";
} else {
echo "<option value=\"".$i."\">".$i."</option>";
}
++$i;
}
?>
</select>
</td><td bgcolor="<?=$row?>">
<input type="SUBMIT" name="date" value="View Single Day">
</td>
</tr>
<tr>
<td bgcolor="<?=$row?>">
End Date:
<select name="month2">
<?php
if (!$_POST["month2"])
{
$curmonth = date("n");
} else {
$curmonth = $_POST["month2"];
}
$monthname = array("", "January", "February", "March", "April", "May", "June", "July", "August", "September", "October", "November", "December");
$i=1;
while ($i<=12){
if ($curmonth==$i){
echo "<option SELECTED value=\"".$i."\">".$monthname[$i]."</option>";
} else {
echo "<option value=\"".$i."\">".$monthname[$i]."</option>";
}
++$i;
}
?>
</select>
</td><td bgcolor="<?=$row?>">
<select name="day2">
<?php
if (!$_POST["day2"])
{
$curday = date("j");
} else {
$curday = $_POST["day2"];
}
$i=1;
while ($i<=31){
if ($curday==$i){
echo "<option SELECTED value=\"".$i."\">".$i."</option>";
} else {
echo "<option value=\"".$i."\">".$i."</option>";
}
++$i;
}
?>
</select>
</td><td bgcolor="<?=$row?>">
<select name="year2">
<?php
$curyear = date("Y");
if (!$_POST["year2"]){
$year = $curyear;
} else {
$year = $_POST["year2"];
}
$i=2004;
while ($i<=$curyear){
if ($year==$i){
echo "<option SELECTED value=\"".$i."\">".$i."</option>";
} else {
echo "<option value=\"".$i."\">".$i."</option>";
}
++$i;
}
?>
</select>
</td><td bgcolor="<?=$row?>">
<input type="SUBMIT" name="date2" value="View Date Span">
</td>
</tr>
</form>
</table>
</td></tr>
</table>
graph_test.php
<?php
include ("graphconf.php");
include ($path_to_jp."jpgraph.php");
include ($path_to_jp."jpgraph_line.php");
if ($_GET[t] && !$_GET[t2])
{
$day = $_GET[t];
$where ="date='$day' ORDER BY hour";
$SQL = "SELECT temperature, outdoor_humidity, hour, date FROM ".$table." WHERE ".$where;
}
if (!$_GET[t])
{
$where ="DAYOFMONTH(date) = DAYOFMONTH(CURRENT_DATE) AND MONTH(date) = MONTH(CURRENT_DATE) AND YEAR(date) = YEAR(CURRENT_DATE) ORDER BY hour";
$SQL = "SELECT temperature, outdoor_humidity, hour, date FROM ".$table." WHERE ".$where;
}
if ($_GET[t] && $_GET[t2])
{
$day = $_GET[t];
$day2 = $_GET[t2];
$where = "date BETWEEN '$day' AND '$day2' GROUP BY date, hour ORDER BY date";
$SQL = "SELECT AVG(temperature) AS temperature, AVG(outdoor_humidity) AS outdoor_humidity, date FROM ".$table." WHERE ".$where;
}
mysql_connect($host, $user, $pass) or die("Cannot connect to MySQL!");
mysql_select_db($db) or die("MySQL database not found!");
$RESULT = mysql_query($SQL);
while ($myrow=mysql_fetch_array($RESULT)) {
$temp = $myrow["temperature"];
$humid = $myrow["outdoor_humidity"];
$l1datay[] = $temp;
$l2datay[] = $humid;
if (!$day2) {
$datax[] = $myrow["hour"];
}
else {
$datax[] = $myrow["date"];
}
}
mysql_close();
// Create the graph.
$graph = new Graph(600,400,"auto");
$graph->SetScale("textlin");
$graph->SetY2Scale("lin");
$graph->xaxis->SetTickLabels($datax);
$graph->ygrid->Show(true,true);
$graph->xgrid->Show(true,false);
if (!$day2) {
$graph->SetMargin(50,35,20,40);
$graph->xaxis->SetTextTickInterval(60);
$graph->xaxis->SetTextLabelInterval(1);
$graph->xaxis->title->Set("Hours");
}
else {
$graph->SetMargin(50,35,20,65);
$graph->xaxis->SetFont(FF_ARIAL,FS_NORMAL,8);
$graph->xaxis->SetTextTickInterval(24);
$graph->xaxis->SetTextLabelInterval(1);
$graph->xaxis->SetLabelAngle(-90);
}
$graph->yaxis->SetColor("red");
$graph->y2axis->SetColor("blue");
$graph->setMarginColor("#FFFFFF");
$graph->img->SetAntiAliasing();
// Create the temp line plot
$l1plot=new LinePlot($l1datay);
$l1plot->SetColor("red");
$l1plot->SetWeight(2);
//Create the humidity line plot
$l2plot=new LinePlot($l2datay);
$l2plot->SetColor("blue");
$l2plot->SetWeight(2);
// Add the plots to the graph
$graph->Add($l1plot);
$graph->AddY2($l2plot);
// Add a title to the graph
$graph->title->Set("Temperature (degC) and Humidity (%)");
$graph->title->SetFont(FF_FONT1,FS_BOLD);
// Display the graph
$graph->Stroke();
?>
Thanks for the VERY prompt response. I’ve got this working on mine now at http://weather.musaland.com/graphtest.php I have made a few, very minor, changes. I added the conversion to
Based on the scripts published here I have developed one a bit more to allow you to select the data to be graphed and optionally to provide a start and end hour to be graphed as well (even on a date span). If any one is interested I can either send them, or post them here. The actual graphing script can be used directly to create image graphs by calling it with the relevant parameters so you could imbed an dynamic graph image in a website. It probably could do with a bit more error checking later!
I have it working on my personal web server which if it is up can be accessed as http://62.49.22.231/graphall.php and will display data from my MySQL server. Please try it but I can’t test it from the internet so if you have trouble please let me know. It is likely to be down from about 23:00 to 07:30 GMT daily by the way!
Please try it but I can't test it from the internet so if you have trouble please let me know.
Hello from way down under in NZ.I got to the site OK, but when the page is loading it is attempting to load a graphic (the graph?) from your private 192.168.x.x address, rather than the internet address. Looks like a reference is using the private IP.
I can see the date selection boxes ok though.
Oops… should work now. That’s the problem on my local lan I cant easily use my IP address since I use NAT on my router any access from me appears as my IP address, so I have to use my local LAN IP to access the server!
Just checked it out and it works great. Nice job!
Could that be used on a wx page or would it need modifications? I was thinking would be nice to have a seperate page for querying the database for historical data/records. I am trying to clean up my main page and have buttons to get to the other data available on my site…
Oops.... should work now.
Works great. The only comment I would make is that the graphs are just slightly too big to fit on a browser page without scroll bars on my 1024x768 screen. In other words I have to scroll up and down to see the data and date range boxes. Is there a way to reduce them to say 80% of what you have set?
Otherwise I too would be very interested in using your scripts.
great work there!!!
works really well
and really shows the power of using the mysql abiliity
I would have to say that I am VERY interested and would recommend just posting the script here. Otherwise you could PM me with the scripts.
Thanks
I’ll clean it up just a tad with a few more comments and post the scripts here. Probasbly tommorrow now as it is bed time here
As to image size it is an option in the config script. I use 1280x1024 so 800x600 for me is fine but I know the majority it is a bit big.
Update I just made it a bit smaller so it might be better on 1024x768 screens.
With the scripts (there are 3, one for the config, one for the html and the image creation) as written you could call the image creation script directly from an img html tag as long as you use the correct options on the call, or you could use the html script to produce the graphs as demonstrated.