I have been populating my MySQL database for a while now and am beginning to look into actually doing something with that information. I have installed JPGraph and am working on doing some realtime graphing. However, I am running into a problem with the data and time fields. Mainly that most PHP functions rely on having the UNIX timestamp to create a time format and the time field in the database doesn’t even show am/pm, much less having a simgle timestamp field. Without tha am/pm indicator, I can even use the separate date and time fields to create a single timestamp value.
I see that the station_id and station_name usually contain the time including the am/pm indicator, but they are both prefaced by a dash ‘-’, making these fields dificult to use. Has anyone else figured out an easy way to calculate a single timestamp value for each data row?
First, I completely missed those separate day, month, year, etc fields. So using those I was able to create an expression that would give me the data I needed. I also found that when you import the INF data file, the time is in 24 hour format. Go figure.
I through together a quick page pulling from many different tutorials which shows the temperature graph for a given date. The code for the page follows:
<?php
include ("./jpgraph/jpgraph.php");
include ("./jpgraph/jpgraph_line.php");
function TimeCallback($aVal) {
return Date('H:i:s',$aVal);
}
$db = mysql_connect("server","user","password") or die(mysql_error());
mysql_select_db("Weather",$db) or die(mysql_error());
$sql = mysql_query("SELECT * FROM wx_data WHERE date='2004-11-30'") or die(mysql_error());
while($row = mysql_fetch_array($sql))
{
$outtemp[] = ($row[6]*9/5)+32;
$timestamp[] = (strtotime(sprintf("%04d/%02d/%02d %02d:%02d:%02d",$row[39],$row[38],$row[37],$row[31],$row[32],$row[33])));
}
$n = count($timestamp);
$graph = new Graph(800,600,"auto");
$graph->SetScale("intlin",0,0,$timestamp[0],$timestamp[$n-1]);
$graph->img->SetMargin(50,20,10,70);
$graph->title->Set('Date: '.date('Y-m-d',$timestamp[0]));
$graph->xaxis->SetTextTickInterval(10,0);
$graph->xaxis->SetLabelFormatCallback('TimeCallback');
$graph->xaxis->SetLabelAngle(90);
$tempplot = new LinePlot($outtemp,$timestamp);
$tempplot->SetFillColor("lightgreen");
$graph->Add($tempplot);
$graph->Stroke();
?>
Now I just need to figure out how to make a form produce this page so I can pick what is plotted and for what date range. Should be relatively straight forward though.
Not sure if this is the right thread to ask, but would there be any chance of a separate forum for using MySQL with WD? I’m just starting to work with the MySQL side of things and it would be nice to have a place to swap SQL statements, ideas etc.
It might also be relevant to include PHP in this as well - I think most people are using it and there are already some useful PHP snippets floating around here.
The ‘Creating Weather Web Sites’ http://discourse.weather-watch.com/c/23 board is fairly quiet and reasonably well suited to this kind of discussion. If the amount of MySQL or PHP discussion gets too much in there then I can always create a child board for specific tools.
