While Cascade Delete is your best option here then I think there IS a way to go about it, it's far from glamorous, actually what I'm about to code is pretty bad practice, but go easy, I'm new
PHP Code:
<?php
$subQuery = "SELECT * FROM subcategories WHERE MainID=1001";
$subResult = mysql_query($subQuery);
while($subArray = mysql_fetch_array($subResult)) {
$qDelFromProd = "DELETE * FROM Products WHERE SubID=".$subArray['SubID'];
$rDelFromProd = mysql_query($qDelFromProd);
}
$qDelFromSub = "DELETE * FROM subcategories WHERE MainID=1001";
$rDelFromSub = mysql_query($qDelFromSub);
?>
Or something similar, I'm not 100% sure if that code will work I haven't got the time to check it! But the idea behind it is:
You have the MainID, so get every record in the subcategories table where the MainID matches that ID, store these in an array $subArray.
Then for every item in $subArray, delete from the Products table every record that has the same SubID as the current item in the array.
Then you delete every record in the subcategories table that matches the MainID.
If I'm not mistaken then that means every SubCategory of that particular category will be deleted and every product in all of the guilty subcategories will also be deleted.
Now I'm not a great PHP coder, so I'm waiting for someone to come and prove me wrong, but I guess a great way to learn is to be told where you're going wrong!
(See why Cascade Delete is better?
)