Best way to delete from multiple tables in 1 SQL query

[Arc]

Hello, I have 4 tables that all have a corresponding ID field called CategoryID. When I want to delete a category I also want to delete every item in the other tables that also has this Category ID but since you can't have multiple tables in a delete statement what would be the best way to do this?

Is the only way to use 4 different queries?

An example would be

$Sql = "DELETE FROM tblCategories,tblProducts,tblProductQuestions,tblProductAnswers WHERE CategoryID = $item";

Ofcourse that doesn't work but that's the idea i'm shooting for.

Thanks!😃

[drawmack]

I don't think you can delete multiple tables in a single query. But you could do this:

$aryTables = array('Table1','Table2','Table3','Table4');
$aryKeysFields = array('Table1' => 'Key1','Table2'=>'Key2','Table3'=>'Key3','Table4'=>'Key4');

foreach($aryTables as $curTable) {
   $sql = "SELECT COUNT(" . $aryKeyFields[$curTable] . ") FROM $curTable WHERE " .  $aryKeyFields[$curTable] . "=$item";
   $numToDelete = mysql_num_rows(mysql_query($sql),0,0);
   if($numToDelete < 0) {
      $sql = "DELETE FROM $curTable WHERE " . $aryKeyFields[$curTable] . "=" . $item";
      if(mysql_query($sql)) {
         echo "$numToDelete records deleted from $curTable";
      } else {
         echo "$numToDelete records mathed in table $curTable, but could not be deleted: " . mysql_error() . "<br />";
      } //end if
   } else {
      echo "No matching records found in $curTable";
   } //end if
} //end foreach

Now if you want this really robust you'll store the table and field names in the database with a third field that allows you to specify the parent table and then you can modify this code to be a cascading delete.

[Arc]

Yah that's deleting from each table 1 query at a time, I was wondering if it was possible to do it with 1 query, but I guess not.

Thanks!😃