Compare dates using database to log good dates

OK, I've decided to set up a table of days with adequate data and those without adequate data for my 2006 and 2007 weather data to compare graphically. This is how I'm doing it: Also: link to new live page.

1. Set up the MySQL data table. I'm using phpMyAdmin to do this. I made a table named comparedates with two columns: date (int) and status (bool or tinyint):

CREATE TABLE `fill in your database name here`.`comparedates` (
`date` INT NOT NULL ,
`status` BOOL NOT NULL
) ENGINE = MYISAM
That was simple enough. Also, you'll want to designate an index (date)

2. Lets write some code. I'm going to use a lot of my old code because I liked the way my calendar looked.

<html>
<head>
<title>Compare Temperatures Beta</title>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
</head>

<body>
<p align="center"><font size="4" face="Arial, Helvetica, sans-serif">Graphically
Compare Temperatures from 2006 with 2007 for a given date:</font><br>
</p>
<div align="center">
<?php
$link=mysql_connect ("localhost", "xxx", "xxx") or die ('I cannot connect to the database because: ' . mysql_error());
mysql_select_db ("xxx"); //fill in your info here

$today_year = date("Y" ,time() - 7200); // gives us the year (my server is 2 timezones away) may be helpful in the future with more than 2 years
$today_month = date("m" ,time() - 7200); // gives us the month (my server is 2 timezones away)
$today_day = date("d" ,time() - 7200); // gives us the day (my server is 2 timezones away)
$day=1;
$month = 1;
$six = 0;
$seven = 0;
echo "<div align=\"center\">";
echo "<table width=\"80%\" border=\"1\">";
while ( $month <= 12){
if ($month == 1) {echo "<tr>";}
if ($month == 5 || $month == 9) {echo "</tr><tr>";}
echo "<td width=\"25%\">";
echo "<div align=\"center\"><font size=\"3\">"; //center the month headers
echo "<a href=\"compare.php?day=0&month=".$month."\">".date(F, mktime(0,0,0,$month)).":</a>";
echo "</font></div>"; //end center month headers
echo "<div align=\"center\"><font size=\"3\">"; //center the day text links
while ( $day <= 31){
if (($month*100)+$day <= ($today_month*100)+$today_day){ //we want to make sure we're not checking into the future
$date_to_query = ($month*100)+$day;
$sql = 'SELECT `status`'
. ' FROM comparedates'
. ' WHERE (`date` = '."$date_to_query".')';
$result = mysql_query($sql);
//$nrows = mysql_num_rows($result);
$queryresult = mysql_fetch_array($result);

if ($queryresult[0] == "") { //we don't know about that day, call the assign_status_to_a_date($day, $month) function
$queryresult[0] = assign_status_to_a_date($day, $month); //hooray, now we know
}
if ($queryresult[0] == "1"){ //we have good data
print ' ';
echo "<a href=\"compare.php?day=".$day."&month=".$month."\">".$day."</a>"; //print with a link
}
else { echo " ".$day;} //we know we have bad data, print with no link
} //end if (($month <= $today_month) && ($day <= $today_day))
else { echo " ".$day; } //echo "That date is in the future. Check back later<br>";
$day++;
if (($month == 4 || $month == 6 || $month == 9 || $month == 11) && $day == 31) { $day=32;} //adjust for days in month
if ($month == 2 && $day == 29) { $day=32;} //don't forget february!!
} //end day while
$month++;
$day = 1; //reset day counter
echo "</font></div>"; //end center day links
echo "</td>";
} //end month while
mysql_close ($link);
echo "</tr>";
echo "</table>";
echo "</div>";
?>
<?php
$targetday = $_GET['day'];
$targetmonth = $_GET['month'];
if (is_null($targetday) || is_null($targetmonth)) {
echo "<br><div align=\"center\"><font size=\"4\">Choose a link from above to see a graph of that date.</font></div>";
}
else {
echo "<br>";
print '<img src="compgraph.php?day='.$targetday.'&month='.$targetmonth.'">';
}
?>
<?php
function assign_status_to_a_date($day, $month) { //we get here when no examination of the date for the years has been done and stored in the comparedates table

$link=mysql_connect ("xxx", "xxx", "xxx") or die ('I cannot connect to the database because: ' . mysql_error());
mysql_select_db ("xxx"); //fill in your info in these two lines

# VWS date format is: YYYYMMDDHHMM, see below for some WD code
$date_to_test_vws_2006 = 200600000000 + ($month*1000000) + ($day*10000); //this should be VWS time of midnight of the day in question 2006
$date_to_test_vws_2006_end = 200600000000 + ($month*1000000) + ($day*10000) + 2359; //this should be VWS time of 23:59:59 of the day in question 2006
$date_to_test_vws_2007 = 200700000000 + ($month*1000000) + ($day*10000); //this should be VWS time of midnight of the day in question 2007
$date_to_test_vws_2007_end = 200700000000 + ($month*1000000) + ($day*10000) + 2359; //this should be VWS time of 23:59:59 of the day in question 2007
//-----------------
//run some queries:
//-----------------
//check 2006 first
$sql = 'SELECT COUNT(`OutdoorTemperature`)'
. ' FROM weatherdata'
. ' WHERE (`RecDate` > '."$date_to_test_vws_2006".')'
. ' AND (`RecDate` < '."$date_to_test_vws_2006_end".')';

$result = mysql_query($sql, $link);
$num = mysql_fetch_array($result);
if ($num[0] > 0) {$six = .5;}
else {$six = 0;}

//check 2007
$sql = 'SELECT COUNT(`OutdoorTemperature`)'
. ' FROM weatherdata'
. ' WHERE (`RecDate` > '."$date_to_test_vws_2007".')'
. ' AND (`RecDate` < '."$date_to_test_vws_2007_end".')';
$result = mysql_query($sql);
$num = mysql_fetch_array($result);
if ($num[0] > 0) {$seven = .5;}
else {$seven = 0;}

$status = $six + $seven; //1 = good data 1 != bad data
if ($status == .5) {$status = 0;} //incomplete data is as good as no data
$date_to_insert = ($month*100)+$day; //consistent with our comparedates table date format
$sql = "INSERT INTO comparedates (date,status) VALUES ('$date_to_insert','$status')";
mysql_query($sql, $link);
return $status; //as well as adding the data and the status to our table, we want to return the status value to the calendar
} //end function
?>
<p><font size="3" face="Arial, Helvetica, sans-serif"><a href="http://www.yourmainpagehere.htm">Back
to Main Page</a></font></p>
</div>
</body>
</html>

Works great and loads lightning fast. Perhaps the first few times you run it, if you have a big database, it will time out your php processor, but every time that happens, the comparedates table is getting more and more data and soon it will only we the most recent day or two that you need to query when the page loads.. I'll have to work on the WD timestamp when I get a chance

Just a really quick stab at the WD date format. The fact that the timestamp is in two columns makes the date query even easier (I think):.

# VWS date format is: YYYYMMDDHHMM, because I don't know the WD time stamp format, you'll have to figure this out for WD
$date_to_test_vws_2006 = 200600000000 + ($month*1000000) + ($day*10000); //this should be VWS time of midnight of the day in question 2006
$date_to_test_vws_2006_end = 200600000000 + ($month*1000000) + ($day*10000) + 2359; //this should be VWS time of 23:59:59 of the day in question 2006
$date_to_test_vws_2007 = 200700000000 + ($month*1000000) + ($day*10000); //this should be VWS time of midnight of the day in question 2007
$date_to_test_vws_2007_end = 200700000000 + ($month*1000000) + ($day*10000) + 2359; //this should be VWS time of 23:59:59 of the day in question 2007

Get rid of these lines above and replace them with the next lines. Yes, I know there's probably a better way to get the 0's before the month and day, but let's not get too complicated right now.

/*"It[WD] has two fields date as YYYY-MM-DD and time as HH:MM:SS"*/
if ($month < 10 && $day < 10){
$date_to_test_wd_2006 = "2006-0".$month."-0".$day;
$date_to_test_wd_2007 = "2007-0".$month."-0".$day;
}
else if ($month < 10 && $day >9){
$date_to_test_wd_2006 = "2006-0".$month."-".$day;
$date_to_test_wd_2007 = "2007-0".$month."-".$day;
}
else if ($month > 9 && $day < 10){
$date_to_test_wd_2006 = "2006-".$month."-0".$day;
$date_to_test_wd_2007 = "2007-".$month."-0".$day;
}
else { //($month > 9 && $day >9)
$date_to_test_wd_2006 = "2006-".$month."-".$day;
$date_to_test_wd_2007 = "2007-".$month."-".$day;
}

And then make changes to the query statements:

//-----------------
//run some queries:
//-----------------
//check 2006 first !!!! fill in whatever WD calls the date into OutdoorTemperature
$sql = 'SELECT COUNT(`OutdoorTemperature`)'
. ' FROM weatherdata'
. ' WHERE (`RecDate` = '."$date_to_test_wd_2006".')';

leave these the same:
$result = mysql_query($sql, $link);
$num = mysql_fetch_array($result);
if ($num[0] > 0) {$six = .5;}
else {$six = 0;}
if ($month < 6) {$six = 0;}
if ($day > 8 && $month == 8) {$six = 0;}
if ($day < 9 && $month == 9) {$six = 0;}
if ($day > 7 && $day < 14 && $month == 6) {$six = 0;}
if ($day > 15 && $day < 31 && $month == 6) {$six = 0;}
if ($day > 29 && $day < 32 && $month == 7) {$six = 0;}

and change the 2007 query statement to:

//check 2007 //same thing, fill in OutdoorTemperature with whatever WD calls it
$sql = 'SELECT COUNT(`OutdoorTemperature`)'
. ' FROM weatherdata'
. ' WHERE (`RecDate` = '."$date_to_test_wd_2007".')';

The rest should be the same. Let me know if this works. Oh yeah, you have to change the date stuff in the compgraph.php file(on the other page here) to WD too. Gotta run, Chris has figured this out though - he has a nice comparison graphing page that's live.