Deleting from a table is simple enough. If, for example, we wanted to remove all the comments from a single user and we already know that user’s id we could run this command (employing the EasyPDO library in PHP):
<?php
$db->ExecuteSQL("DELETE FROM comments WHERE user_id=?","i",$id);
?>
Now imagine we only have the user’s email and that the correlation between a user’s email and id is stored elsewhere (such as the USERS
table). Our task now has the additional step of having to use the user’s email to look up their id before we can delete their comments:
<?php
$id = $db->FetchValue("SELECT id FROM users WHERE email=?","s",$email);
$db->ExecuteSQL("DELETE FROM comments WHERE user_id=?","i",$id);
?>
We could do it this way (the code works, after all) but redundancy like that annoys me. Defining a variable just to hand it back to the system seems lazy and indulgent. It forces me to make sure I’m not already using that namespace elsewhere (or deal with it when the system that was working fine suddenly goes toes-up).
Instead of all that static let’s just use JOIN
:
<?php
$db->ExecuteSQL("DELETE c FROM comments c LEFT JOIN users u ON u.id = c.user_id WHERE u.email=?","s",$email);
?>