Deleting multiple database entries across different tables

[shure2]

Hi,

 

I'm trying to delete a company, which will then delete all of its products, which will then delete all the product qualities and product details.

 

The tables I have are company, product, productdetail, productquality.

 

I could get the company and its product to delete because the 'compid' is stored in both tables, but only the 'prodid' is stored in the productquality and productdetail so somehow I need to get the right prodid into a variable so I can use it to delete entries from the productquality and productdetail tables. i dont have any table relationships at the moment

 

Below is the code I have:

 

<?php
    require "session_logincheck.php";
    require "connect.php";
    
    $compID = $_GET['compid'];
   
    $productID = "select prodid from product where prodcompid = '$compID'";
    
    $query =  "delete from company where compid = '$compID'";
    $query1 =  "delete from product where prodcompid = '$compID'";
    $query2 =  "delete from productdetail where prodid = '$result0'";
    $query3 =  "delete from productquality where prodid = '$result0'";

    $result0 = @mysql_query($productID);
    $result = @mysql_query($query, $connection) or die ("Unable to perform query<br>$query".mysql_error());
    $result1 = @mysql_query($query1, $connection) or die ("Unable to perform query<br>$query1".mysql_error());
    $result2 = @mysql_query($query2, $connection) or die ("Unable to perform query<br>$query2".mysql_error());
    $result3 = @mysql_query($query3, $connection) or die ("Unable to perform query<br>$query3".mysql_error());

    
    header("Location: company_page.php");
    exit();
?>

 

[ignace]

DELETE c, p, pd, pq
FROM company c
JOIN product p ON c.compid = p.prodcompid
JOIN productdetail pd ON p.id = pd.prodid
JOIN productquality pq ON p.id = pq.prodid
WHERE c.compid = '$compID'

[shure2]

(message deleted)

[ignace]

$query =  "DELETE c, p, pd, pq
    FROM company c
    JOIN product p ON c.compid = p.prodcompid
    JOIN productdetail pd ON prod.id = pd.prodid
    JOIN productquality pq ON prod.id = pq.prodid
    WHERE compid = '$compID'";

 

That query is wrong as there is no table with the name prod as for your InnoDB it might give problems. Create a SELECT query that selects all the data that needs to be deleted then transform it in a DELETE query using my code as a base

[shure2]

/quote]

That query is wrong as there is no table with the name prod as for your InnoDB it might give problems. Create a SELECT query that selects all the data that needs to be deleted then transform it in a DELETE query using my code as a base

 

sorry yes I spotted this and corrected it so it works, however I find that if I don't have productqualities and productdetails relating to products and then a product relating to a company nothing will deleted (I would like it do that even if there were no products or productdetails/product qualities the company would still be removed.

 

thank you for your patience, really appreciate it, I can feel how close I am to cracking this now!

 

 

[ignace]

try LEFT JOIN

[shure2]

perfect, thanks!