[RESOLVED] Deleting multiple database entries across different tables

[shure2]

Hi,

I have an sql statement that deletes a company, their products and then the products qualities and details (for 4 tables in total).

The statement will delete all companies products, details and qualities if there are entries related to the companies product, but I have the case where sometimes a company will not have products but I still want to delete them (this wont work for my current statement as it seems to need related entries in all tables)

Can anyone help me translate this statement into something that will do this please:

<?php
    require "session_logincheck.php";
    require "connect.php";

$compID = $_GET['compid'];

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

$result = @mysql_query($query, $connection) or die ("Unable to perform query<br>$query".mysql_error());


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

[Weedpacket]

If you establish a foreign key relationship between a company and its products, and another between a product and its qualities and details, you can declare both key relationships as ON DELETE CASCADE. Then your delete statement only needs to be

Delete from company where compid='$compID'

Because of the foreign key relating companies and products, the database will automatically delete those records from the products table that have a prodcomid matching $compID; and because of the foreign keys relating products with qualities and details, when those product records are deleted, the corresponding records from the quality and detail tables will automatically be deleted as well.

Basically, when you specify a foreign key, the database will do whatever is necessary to prevent products having prodcomids that don't match any compid.