Compare dates using database to log good dates

Latest beta of the code for the compare.php page:

link to a live page.that uses a version (non WD) of this code.

1. You'll need to 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. The way html gets rid of the tabs is a pain, but here's the latest php code for the compare.php page:

<html>
<head>
<title>Compare Temperatures Beta (WD Version)</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: (WD Version)</font>
<br>
<font size="2" >Do not click on month names yet as an error will be produced</font>
<br />
</p>
<div align="center">
<?php
$link=mysql_connect ("Your_Server", "Login", "Password") or die ('I cannot connect to the database because: ' . mysql_error());
mysql_select_db ("Your_Database"); //fill in your info here

$today_year = date("Y" ); // gives us the year may be helpful in the future with more than 2 years
$today_month = date("m"); // gives us the month
$today_day = date("d" ); // gives us the day
$day=1; // some startin variables, $day is the day of the month
$month = 1; // and $month is the month of the year
echo "<div align=\"center\">"; // center it
echo "<table width=\"80%\" border=\"1\">"; // start a table
while ( $month <= 12){ // so after incementation fo $month later, if $month is < 12 do following code
if ($month == 1) {echo "<tr>";} // start a for January
if ($month == 5 || $month == 9) {echo "</tr><tr>";} //start a new row for may and september
echo "<td width=\"25%\">"; // width set-up
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>"; //this will generate calendar month names
echo "</font></div>"; //end center month headers
echo "<div align=\"center\"><font size=\"3\">"; //center the day text links
while ( $day <= 31){ // so as long as the day we're looking at is < 31, do following code
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; // this is a nice format to store dates as they are unique and in ordinal
$sql = 'SELECT `status`' // set-up the query for the status of the date
. ' FROM comparedates'
. ' WHERE (`date` = '."$date_to_query".')';
$result = mysql_query($sql); // run this query on the MySQL database above
$queryresult = mysql_fetch_array($result); // confusing php stuff. put result in an array variable we can access

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++; // increment 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++; //increment 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 //here's the code for the portion of the page that displays the graph
$targetday = $_GET['day']; //get which day was selected from the web url after selection of a date link
$targetmonth = $_GET['month']; //get which month too
if (is_null($targetday) || is_null($targetmonth)) { //as if we just came to the page, nothing is selected
echo "<br><div align=\"center\"><font size=\"4\">Choose a link from above to see a graph of that date.</font></div>"; // so print a message
} // end if
else { //otherwise if there are good values for day and month in the url . .
echo "<br>"; // page break
print '<img src="compgraph.php?day='.$targetday.'&month='.$targetmonth.'">'; //get the image from this file which is the graphing file
} // end else
?>
<?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
// this is "the big slow query"
$six = 0; // declare some variables - $six is the status of the 2006 data
$seven = 0; // $seven is the status of the 2007 data
$status = 0; // we'll use this asthe indicator
$link=mysql_connect ("Your_Server", "Login", "Password") or die ('I cannot connect to the database because: ' . mysql_error());
mysql_select_db ("Your_Database"); //fill in your info here

//"It[WD] has two fields date as YYYY-MM-DD and time as HH:MM:SS" we need leading zeros forthe wd datestamp:
$date_to_test_wd_2006 = "2006-".date(m, mktime(0,0,0,$month))."-".date(d, mktime(0,0,0,0,$day)); //this should be wd time of the day in question 2006
$date_to_test_wd_2007 = "2007-".date(m, mktime(0,0,0,$month))."-".date(d, mktime(0,0,0,0,$day)); //this should be wd time of the day in question 2007
//!!!!!!someone needs to test this and make sure that it gives the the correctly formatted datestamp with leading 0s!!!!!!!!!!!!
// echo "query datestamps: ".$date_to_test_wd_2007." and ".$date_to_test_wd_2006."<br>"; //use to test code (get rid of opening //s)

//-----------------
//run some queries:
//-----------------
//check 2006 first
$sql = 'SELECT COUNT(`temperature`)' //query statement
. ' FROM Your_Weather_Table' //fill in title of table
. ' WHERE (LEFT(`date`,10) = "'."$date_to_test_vws_2006".'")'; //do we really need (LEFT(`date`,10) instead of just `date`?
//echo $sql ."\n"; // used to test code

$result = mysql_query($sql, $link); // run query
$num = mysql_fetch_array($result); // should give us how many records match
if ($num[0] > 0) {$six = .5;} // so we have some data!!, you could change the 0 to another number to check for completeness
else {$six = 0;} // no good data from 2006

//check 2007 same comments apply as with 2006 query
$sql = 'SELECT COUNT(`temperature`)'
. ' FROM Your_Weather_Table'
. ' WHERE (LEFT(`date`,10) = "'."$date_to_test_vws_2007".'")';
$result = mysql_query($sql);
$num = mysql_fetch_array($result);
if ($num[0] > 0) {$seven = .5;} // good 2007 data
else {$seven = 0;} // no good 2007 data

$status = $six + $seven; //1 means good data, <1 means bad data
if ($status == .5) {$status = 0;} //incomplete data (only 2006 or 2007 has good data) is as good as no data, keeps the "bool" format of status intact
$date_to_insert = ($month*100)+$day; //keep consistent with our comparedates table date format
$sql = "INSERT INTO comparedates (date,status) VALUES ('$date_to_insert','$status')"; //query statement to add the info for the date tothe comparedates table
mysql_query($sql, $link); //do it, add the line to the comparedates table
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.your_site.com">Back
to Main Page</a></font></p>
</div>
</body>
</html>

Now all you need (other than woorking jpgraph installed) is the compgraph.php file with correct queries for WD. We will try to postthat soon. (You have to change the date stuff in the compgraph.php file(on the other page here) to WD)