Removing Items in a Database

Periodically you will need to remove information from a database. The DELETE command allows you to remove a row, or multiple rows, at a time within the database.

Designer's Note

Quite often we are tempted to delete records from a database, however, quite often we shouldn't. Consider a retail website that no longer sells a particular item because it is no longer being manufactured. If the item is deleted from the database, then running history reports to find out what items you used to sell, or had sold may be inaccurate. Often it is just as good to have an active field which is used to determine if the record should be displayed to the masses or not. This can be a difficult database design decision to make. Make sure you think about all the consequences before choosing to delete a row.

Deleting a record follows the general form:

DELETE FROM table_name
WHERE some_column=some_value

Deletion Warning

Because you can delete multiple rows simultaneously, it is imparative that you check your commands carefully.

Leaving the WHERE clause out, will delete all the rows.

Verify Your User's Intentions

Ever deleted something, you wish you hadn't? Reduce the chance of your user having that feeling by providing a verification step before they delete something.

It is not that uncommon to have a delete button on any one of several sections of a data driven website. It will lead to a page that looks like this:

<?php
include('dbconnect.php');
$sql = 'SELECT `id`, `name` FROM guestbook WHERE `id`=" .SQLValueString($_GET['id'], 'int');
$result = mysql_query($sql, $db_connection) or die('Error: ' .mysql_error());

if($row = mysql_fetch_assoc($result)) {
  if(isset($_POST['submit'])) {
     $sql = 'DELETE FROM guestbook WHERE id=' .SQLValueString($_POST['id'], 'int');
     $result = mysql_query($sql, $db_connection) or die('Error: ' .mysql_error());
     echo '<p class="notice">Database Row Deleted</p>';
  } else {
     echo '<p>Are you sure you want to permenantly remove the entry from '
      .stripslashes($row['name']) .'?'
       .'<form method="post"><input type="hidden" name="id" value="'
      .SQLValueString($_GET['id'], 'int') .'" />'
       .'<input name="submit" type="submit" value="Remove Record\'s Information" />'
       .'</form>';
  }
} else {
   echo '<h1 class="error">ERROR</h1><p>No record found to be able to remove. Return to list.</p>';
}
?>

The SQL Code Explained

DELETE FROM guestbook WHERE id= - This is going to remove the row from the database, based upon what was passed in via the form. We get the id for the form's hidden field. This reduces the chances of someone randomly entering an id in the address bar, and deleting the record.

The PHP Code Explained

The PHP code is fairly familiar. There are a couple of minor changes as we've progressed.

If you notice, we check to see if a record exists, then we check to see if the form has been submitted. This way we can't attempt to remove a record which doesn't exist. This required an if statement, within another if statement. We call this nested if statements.

Before we allow a user to remove the row however, we do provide a verification of removal. During this process, we show them at least part of the information which they will remove, so they know if it is correct or not.