deleteauthor.php will allow us to remove an author from the database given its ID. As we have seen before, this is frighteningly easy to do, but there is added complexity here. Remember that our Jokes table has an AID column that indicates the author responsible for a given joke. When we remove an author from the database, we must also get rid of any references to that author in other tables. If we didn't, then we might have jokes left in the database that were associated with a nonexistent author.
This is one weakness of MySQL—it won't automatically clean up orphaned data related to an entry that you delete. More advanced database management systems can actually keep track of which entries are related to which, and make sure that the set of relationships represented in the database (the referential integrity of the database) is always maintained. However, MySQL was designed to forego such niceties in order to achieve significant performance gains, so the task of cleaning up orphaned entries falls to our PHP script.
We have two possible ways to handle this situation:
When we delete an author, also delete any jokes attributed to the author.
When we delete an author, set the AID of any jokes attributed to the author to NULL, to indicate that they have no author.
Since most authors would not like us using their jokes without giving them credit, we'll opt for the first option. This also saves us from having to handle jokes with NULL values in their AID column when we display our library of jokes.
<!-- deleteauthor.php -->
<l>
<head>
<title> Delete Author </title>
</head>
<body>
<?php
$cnx = mysql_connect('localhost','root','mypasswd');
mysql_select_db('jokes');
// Delete all jokes belonging to the author
// along with the entry for the author.
$id = $_GET['id'];
$ok1 = @mysql_query("DELETE FROM Jokes WHERE AID='$id'");
$ok2 = @mysql_query("DELETE FROM Authors WHERE ID='$id'");
if ($ok1 and $ok2) {
echo('<p>Author deleted successfully!</p>');
} else {
echo('<p>Error deleting author from database!<br />'.
'Error: ' . mysql_error() . '</p>');
}
?>
<p><a href=">Return to Authors list</a></p>
</body>
<l>