I have the following script which isn't working. Basically my logic has melted and I've been looking at it too long.
Basically, I have a hierarchy of MySQL tables all in the format of ID (auto-increment int) and CAT (CHAR(30)).
Id's not really important here, but the value of cat determines the name of the dependent table.
I'm trying to read through the hierarchy of tables to delete a table, and all its dependent tables. It's all going wrong.
Anyway if you're mind is good for a mental challenge, here it is (debug lines'n'all):
include "../includes/database.php";
function subtables($cat,$subtable,$maintaintable,$parent)
{
$subsql = "SELECT cat FROM $subtable";
$sub = MYSQL_QUERY($subsql) OR DIE ("Unable to find the index table: $subtable");
$subempty = MYSQL_NUMROWS($sub);
IF ($subempty == 0):
echo "DROP TABLE $subtable HAHA<BR>";
$delsql = "DELETE FROM $parent WHERE cat=$subtable, so it's this sucker! HA HA";
echo "$delsql<BR>";
ELSE:
echo "<SCRIPT LANGUAGE=\"JavaScript\">var agree=confirm(\"The table $subtable is not empty. Do you wish to continue? FIRST IN FUNCTION\")
if (agree)document.write(\"\");else history.go(-1);// End --></SCRIPT>";
WHILE ($subempty > 0):
$subcounter=0;
WHILE ($subcounter < $subempty):
$parent = $subtable;
$subsubtable = MYSQL_RESULT ($sub, $subcounter);
$p = $maintaintable;
subtables($cat,$subsubtable,$maintaintable,$parent);
$subcounter++;
ENDWHILE;
//DROP TABLE
IF ($parent != $subtable):
echo "DROP TABLE $subtable HERE<BR>";
$delsql = "DELETE FROM $parent WHERE cat=$subtable, so it's this sucker! HERE";
echo "$delsql<HR>";
ELSE:
// FIND THE PARENT
$parentsql = "SELECT cat FROM $parent WHERE cat='$maintaintable'";
$execparent = "MYSQL_QUERY($parentsql) OR DIE ("Failed to search for parent<BR>");
$foundparent = MYSQL_NUMROWS($execparent);
IF ($foundparent > 0):
echo "DROP TABLE $subtable HERE<BR>";
$delsql = "DELETE FROM $maintaintable WHERE cat=$subtable, so it's this sucker! HERE";
echo "$delsql<HR>";
ELSE:
for ($searching=0; $searching < $foundparent; $searching++)
{
$lookingat = MYSQL_RESULT ($execparent, $searching);
IF ($lookingat == $subtable
ENDIF;
$predropsql = "SELECT cat FROM $subtable";
$predrop = MYSQL_QUERY($subsql) OR DIE ("Unable to find the index table: $subtable");
$preempty = 0;
$preempty = MYSQL_NUMROWS($predrop);
IF ($preempty == 0):
$dropsql = "DROP TABLE $subtable";
$delsql = "DELETE FROM $maintaintable WHERE cat=$subtable!!!";
echo "$dropsql<BR>";
echo "$delsql<HR>";
ENDIF;
$subempty--;
ENDWHILE;
ENDIF;
}
$originaltable = $cat;
$parent = $cat;
$table = $cat;
global $maintaintable;
$checksql = "SELECT cat FROM $table";
$check = MYSQL_QUERY($checksql) OR DIE ("Unable to find the index table: $table");
$empty = MYSQL_NUMROWS($check);
echo "There are $empty types of $table<BR>";
$counter = 0;
// For each of the top level entries
IF ($empty > 0):
echo "<SCRIPT LANGUAGE=\"JavaScript\">var agree=confirm(\"The table $table is not empty. Do you wish to continue? OUT OF FUNCTION\")
if (agree)document.write(\"\");else history.go(-1);// End --></SCRIPT>";
WHILE ($counter < $empty):
$subtable = MYSQL_RESULT ($check, $counter);
$maintaintable = $subtable;
echo "<B>subtables($cat,$subtable,$maintaintable,$parent)</B><HR>";
subtables($cat,$subtable,$maintaintable, $parent);
$counter++;
ENDWHILE;
ELSE:
$therewasneveranycontent = true;
ENDIF;
IF ($therewasneveranycontent):
echo "$table only contains products<BR>";
ELSE:
echo "<HR>DROP $originaltable<BR>";
ENDIF;
?>
Cheers,
Dave
