Delete records from multiple tables? (PHP, MySQL)

shoaibmunir · 06-23-2009, 02:59 PM

I have to delete records in multiple table i have only main category id "1001" and i want to delete all sub categories and products.

Database Structure

Main Category Table:-

MainID = 1001
MainName = HP

Sub Category Table:-

MainID = 1001
SubID = 2001
SubName = laptops

Products Table:-

SubID = 2001
ProID = 3001
ProName = HP G60t series

_______________

any idea?

Wildhoney · 06-23-2009, 03:53 PM

You could configure the relationships and then use cascading delete.

shoaibmunir · 06-29-2009, 11:00 AM

is it not possible without relationships?????

Thank's for your reply

ScottRiley · 06-29-2009, 09:56 PM

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?

)

shoaibmunir · 07-15-2009, 09:30 PM

Quote:

Originally Posted by ScottRiley

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?

)

Thank’s its working fine.i have already developed same code for delete records. I just want to more stretch my code its very long i'm feeling its not professional work

any advise!