[RESOLVED] deleting associated records during a parent deletion process?

[schwim2]

Hi there everyone!

I need to delete abandoned cart ID's(older than 30 days) and while I'm doing that, I need to also delete and cart contents that may exist for the abandoned cart ID that is being deleted)

Usually, I would do:

SELECT FROM carts WHERE > 30 days
WHILE{
    $cart_id = get this cart ID
    DELETE FROM cart_contents WHERE cart_id = $cart_id
    DELETE FROM carts WHERE id = $cart_id LIMIT 1
}

But I've begun wondering if there's a more streamlined way of doing this. Could I modify the single delete query of the carts to also delete any cart_contents where 'cart_id' matches the cart's 'id' being deleted?

What I mean is, is there an easier way to do this:

	$q_delcarts = "DELETE FROM carts WHERE modified < '$thirtydays'";
	$r_delcarts = mysqli_query ($link, $q_delcarts) or die('Catastrophic failure [Super secret code ftr834]');

While doing this for each record that gets deleted above?

	$q_delcartconts = "DELETE FROM cart_contents WHERE token = '$token_from_carts_record_being_deleted_above'";
	$r_delcartconts = mysqli_query ($link, $q_delcartconts) or die('Catastrophic failure [Super secret code ftr4829]');

Or do I just need to keep doing:

QUERY ~> LOOP (DELETE CHILDREN ~> DELETE PARENT)?

Thanks for your time!

[cluelessPHP]

Could delete on a join I guess

OH and don't use variables in sql it's bad use placeholders

[Derokorian]

If you set up foreign keys, you can set it up so that ON DELETE CASCADE which means that if I delete a record, any record with a foreign key pointing to the deleted record will also be deleted.

More on that here https://www.techonthenet.com/oracle/foreign_keys/foreign_delete.php

[schwim2]

Hi there guys and thanks very much for your help and suggestions!

I think I stumbled upon a working solution with the assistance from a helpful SE thread:

	$q_delcarts = "DELETE a, b from carts a left join cart_contents b on a.token = b.token WHERE a.lastseen < '$thirtydays'";
$r_delcarts = mysqli_query ($link, $q_delcarts) or die('Catastrophic failure [Super secret code ftr834]');

which, through my limited testing, seems to be working smoothly and it saved me a ton of lines compared to my normal solution, so I'll mark it a win 🙂

Thanks!