//
// Mainline
//
// check to see if process file already exists.
// if it already exists then email an error report and blow it off
if(file_exists($processfl))
{
  $errorstr .="Maintenance tried to start while another instance was running\n\n";
  mail_it($errorstr,0,0,1, $fromaddress, $toaddress);
  die("Process already running\n\n");
}
//
// ok, another instance is not running so it is ok to run
echo "creating process file<br>\n";
//
// create the process file
// first you open it
$fp=fopen($processfl,'a');
if($fp)
{
  // then you close it
  fclose($fp);
}
// Check to see if file created ok.  I know I know I could just
// look at $fp.  But there is always the possibillity that the 
// file handler came back ok but the file did not get created
// this covers that.
// if the file did not get created then email an error report and blow it off
if(!file_exists($processfl))
{
    $errorstr .="Maintenance could not create the process file\n\n";
    mail_it($errorstr,0,0,1, $fromaddress, $toaddress);
    die("Could Not Create Process File\n\n");
}
else
{
    echo "created the process file<br>\n";
}
// lets hook up to MYSql
// If we dont get a good connection we will
// mail an error report and blow it off
$db = mysql_connect($dbhost, $dbuser, $dbpass);
if(!$db)
{
  $errorstr .="Could not connect to MYSQL\n\n";
  $errorstr .="MySql error was " . mysql_error() . "\n\n";
  mail_it($errorstr,0,0,1, $fromaddress, $toaddress);
  die("Could not connect to mysql\n\n");
}
//
// Now we select the database that we are going to check
$selected=mysql_select_db($dbname);
//
// ok, we are good to go
//
// Turn off the server timeout in case of a long winded check or repair operation
set_time_limit(0);
//
// Yeah I know, I am combining appearance and logic here.  Bite me.
// This is old school baby, think CGI ;p
// Lets format a table to make our output all need and pretty
// we want a four (4) column table because the commands we will 
// use return multiple lines of 4 column output. 
$outstr .= "<table border=1>";
$outstr .= "<tr>";
$outstr .= "<td align=center><b>Table Name</b></td>";
$outstr .= "<td align=center><b>Operation</b></td>";
$outstr .= "<td align=center><b>Message Type</b></td>";
$outstr .= "<td align=center><b>Message</b></td>";                
$outstr .= "</tr>";
$outstr .= "<tr><td colspan=4 align=center><b>Database " . trim($dbname) . "</b></td></tr>\n";
//
// We first do a show tables query to get a list of the tables in the database
$sql_str="SHOW TABLES";
$tblresult=mysql_query($sql_str, $db);
if($tblresult)
{
  // ok the query was good, it didnt error
  // now we want to loop through all the tables
  while($tblrow=mysql_fetch_array($tblresult))
  {
    // for each table we do a check tables query
    $sql_str="Check Table " . $tblrow[0];
    $chkresult=mysql_query($sql_str, $db);
    if(!$chkresult)
    {
      // error in the query, check your syntax ;p
      $outstr .= "<tr><td colspan=4 align=center><font color=red><b>Problem checking table " . $tblrow[0] . "</b></font></td></tr>\n";
    }
    else
    {
      // the check table query completed and returned output
      // we need to loop through the output.  
      // Check table will return multiple lines
      // if there is an error in the table
      //
      // clear out our last status holder
      $last_status="";
      //
      // loop through the results
      while($chkrow=mysql_fetch_array($chkresult))
      {
        // format the ouput nice and pretty
        // put the result in the 4 columns of our table
        // column 0 = Table Name
        // column 1 = Operation (either Check or Repair)
        // column 3 = Message Type (status or error)
        // column 4 = Status - we want this to always say OK
        $outstr .= "<tr>";
        $outstr .= "<td>" . $chkrow[0] . "</td><td  align=center>" . $chkrow[1] . "</td><td  align=center>" . $chkrow[2] . "</td><td  align=center>" . $chkrow[3] . "</td>\n";
        $outstr .= "</tr>";
        //        
        // we want to capture the very last status returned so we just keep 
        // overlaying our last status holder
        $last_status=$chkrow[3];
      }
      // Once we are done looping through the check table results the 
      // last status returned is in our last status holder.  we want this to be OK
      if(strtoupper($last_status)<>"OK")
      {
        // OOps, status was not OK so we need to try a repair
        // first we increment our counter to keep track of things
        $needrepair++;
        // Then lets throw a line in our output to indicate we are repairing something 
        $outstr .= "<tr><td colspan=4 align=center><font color=red><b>Attempting to repair table " . $tblrow[0] . "</b></font></td></tr>";
        //
        // Now we run a Repair table query against the table
        $sql_str="Repair Table " . $tblrow[0];
        $rprresult=mysql_query($sql_str, $db);
        if(!$rprresult)
        {
          // error in the sql.  Check your syntax.
          $outstr .= "<tr><td colspan=4 align=center><font color=red><b>Problem repairing table " . $tblrow[0] . "</b></font></td></tr>\n";
        }
        else
        {
          // Ok the repair table completed so we have to loop through the result
          // set just like we did on the check table
          // in fact this could be modularized to do both operations 
          // from one function.  But I'm lazy right now and this is already written
          // clear out our last status holder
          $last_status="";
          while($rprrow=mysql_fetch_array($rprresult))
          {
            // format the ouput nice and pretty
            // put the result in the 4 columns of our table
            // column 0 = Table Name
            // column 1 = Operation (either Check or Repair)
            // column 3 = Message Type (status or error)
            // column 4 = Status - we want this to always say OK
            $outstr .= "<tr>";
            $outstr .= "<td>" . $rprrow[0] . "</td><td  align=center>" . $rprrow[1] . "</td><td  align=center>" . $rprrow[2] . "</td><td  align=center>" . $rprrow[3] . "</td>\n";
            $outstr .= "</tr>";
            $last_status=$rprrow[3];
          }
          if(strtoupper($last_status)<>"OK")
          {
             // Repair failed.  Report it in our output string
             $outstr .= "<tr><td colspan=4 align=center><font color=red><b>Repair Failed " . $tblrow[0] . "</b></font></td></tr>";
          } 
          else
          {
             // The repair was successful
             // increment our repaired counter
             $repaired++;
             // write our success message to our output string
             $outstr .= "<tr><td colspan=4 align=center>Repair Successful " . $tblrow[0] . "</td></tr>";              
          }
        }
      } 
    }
  }
}
//
// Ok all done now
// so lets close our table
$outstr .= "</table><br><br>\n";
//
// lets close the database connection
if($db)
{
  mysql_close($db);
}
//
// delete the process file to indicate the run is complete
unlink($processfl);
//
// finish off our report with an ending time
$outstr.="End of run " . date("M d Y H:i:s", time()) . "<br>\n";
//
// now lets mail the report
mail_it($outstr, $needrepair, $repaired, 0, $fromaddress, $toaddress);
//
// then put it up on the screen or send to a dump file if running from cron
echo $outstr;
//
// End of mainline
//
// Start of user defined funstions
//
// UDF mail_it mails either our pretty report or an error message
// to whomevers address you put in the to address
// pass ins are the email message itself - either our report or an error
// The needed repair counter
// The repaired counter
// and a switch telling us whether this is an error report or not $err=0 is a report, 1 is an eror
// The from address
// The to address
function mail_it($emailmessage, $needed_repair, $were_repaired, $err, $from, $to)
{
  // initialize our email subject line
  $emailsubj="";
  if($err==1)
  {
    // if we have an error lets indicate it in the subject line
    $emailsubj="Maintenance ERROR " . date("M d Y H:i:s", time());
    //
    // we tack the current time to the email message here so we know when the error occurred
    $emailmessage .=date("M d Y H:i:s", time()) . "\n\n";
  }  
  else
  {
    // There is no error so this is a report lets give a fitting subject line
    $emailsubj="Database Maintenance Run"; 
    //
    // Now, i want to indicate on the subject line if the report needs to be
    // looked at or not.  I am going to dfo this by comparing my counters
    if(intval($needed_repair)-intval($were_repaired)>0)
    {
      // If the number of tables needing repair exceeds the tables repaired
      // then some were not repaired.  Bummer.  So lets say that on the 
      // subject line
      $emailsubj .=" - Some Tables Could Not Be Repaired - Please Review";
    }
    else
    {
      if(intval($needed_repair)>0)
      {
        // If we are here then there were some that needed repair
        // but all the repairs worked.  Life is good.
        // Lets say so on the subject line
        $emailsubj .=" - Repairs Occurred Successfully";
      }
      else
      {
        // if we are here then no repairs were needed
        // Life is great.  Again lets indicate it on the subject line
        $emailsubj .=" - All Is Ok";
      }
    }  
  } 
  //
  // set up theheaders for an html mail        
  $headers  = "MIME-Version: 1.0\r\n";
  $headers .= "Content-type: text/html; charset=iso-8859-1\r\n";
  $headers .="From: " . $from . "\r\n";
  //
  // and let's mail the sucker
  mail ( $to, $emailsubj, $emailmessage, $headers);
}

// End of program
?>