tables keep corrupting after script runs
Posted by:
Ryan Huff
Date: August 21, 2005 11:29AM
<?php
require("funcs.inc.php");
//************************************* MISC DATABASE EXPIRE ROUTINE *************************************
$thedate = date("m/d/Y");
misc_connect();
$sql = mysql_query("SELECT * FROM expire");
while ($row = mysql_fetch_array($sql))
{
//assemble the literal values
$tmonth = substr($row['exdate'],0,2);
$smonth = substr($thedate,0,2);
$tday = substr($row['exdate'],3,-5);
$sday = substr($thedate,3,-5);
$tyear = substr($row['exdate'],6);
$syear = substr($thedate,6);
if ($tmonth <= $smonth && $tday <= $sday && $tyear <= $syear OR $tyear < $syear)
{
//do the images table work
$images = mysql_query("SELECT * FROM images WHERE parent='$row[record]'");
while ($imgrow = mysql_fetch_array($images)) { if (file_exists($imgrow['path'])) { unlink($imgrow['path']); } }
mysql_query("DELETE FROM images WHERE parent='$row[record]'");
//do the header table work
$header = mysql_query("DELETE FROM header WHERE record='$row[record]'");
//cleanup the expire table now
mysql_query("DELETE FROM expire WHERE record='$row[record]'");
}
}
//*********************************** END MISC DATABASE EXPIRE ROUTINE ***********************************
//*********************************** VEHICLES DATABASE EXPIRE ROUTINE ***********************************
$thedate = date("m/d/Y");
vehicles_connect();
$sql = mysql_query("SELECT * FROM expire");
while ($row = mysql_fetch_array($sql))
{
//assemble the literal values
$tmonth = substr($row['exdate'],0,2);
$smonth = substr($thedate,0,2);
$tday = substr($row['exdate'],3,-5);
$sday = substr($thedate,3,-5);
$tyear = substr($row['exdate'],6);
$syear = substr($thedate,6);
if ($tmonth <= $smonth && $tday <= $sday && $tyear <= $syear OR $tyear < $syear)
{
//do the images table work
$images = mysql_query("SELECT * FROM images WHERE parent='$row[record]'");
while ($imgrow = mysql_fetch_array($images)) { if (file_exists($imgrow['path'])) { unlink($imgrow['path']); } }
mysql_query("DELETE FROM images WHERE parent='$row[record]'");
//do the header table work
$header = mysql_query("DELETE FROM header WHERE record='$row[record]'");
//cleanup the expire table now
mysql_query("DELETE FROM expire WHERE record='$row[record]'");
}
}
//********************************* END VEHICLES DATABASE EXPIRE ROUTINE *********************************
?>
The above script works fine and as expected with one issue. Everytime it runs, wether it deletes all records or just one it will report tables in the database needing repair because it lost 20 - 40 octets per record on each table that a query ran on.
This is how it works....
database fido_misc has tables "expire", "record" & "images". PHP cycles a while loop on table "expire" and for each record will take the stored date in "expire" and use substr() to break it down and do a literal numeric month, day year comparison. If the "expire" date is < or = the current system date it will look for & delete all related records in "header" and "images" of the same database, then will delete the original SELECTED record from "expire". Then the while loop will start over until all records in expire have been evaluated.
The bottom half of code does the same thing, only for database fido_vehicles.
- Redmon Washington is birthed from the loins of Satan.
"The CryptMaster"