1. Home
2. Walkthrough
3. Database – Delete

Deleting data

• Uniquness
• Getting started
  • Redirect After POST
  • Task – Make a delete button
• Next Steps
  • Deleting Referenced Records
  • Task – Configure Foreign Keys
• Summary
  • New Concepts and Terms
  • Control question

This section is dedicated to teach you how to delete database records. Deleting something from a database is permanent and cannot be undone unless you have a database backup. Be careful about what you delete, though do not be afraid to experiment with your project since you do have a backup for it.

Uniquness

The SQL DELETE command is already familiar to you. One thing you should think of is how to determine which rows to delete. The most common action is deletion of a single row. Each table should have a primary key which identifies each row with a unique value or a set of values. Therefore, you should use it to delete rows.

Be especially careful about compound keys. For example, you cannot delete a single person record by entering its first_name and last_name. There is unique key on first_name,last_name and nickname columns. This means that the combination of first_name,last_name and nickname are guaranteed to be unique, but first_name and last_name are not guaranteed to be unique. These two attributes of a person can be shared among many records in database. Notice the use of words “guaranteed” and “can be”. It may well happen that your database contains only a single person with a particular first_name and last_name. But you cannot rely on such assumptions in your application code, because you don’t know what data will be in the database in future. To create a reliable application, you must use the database keys correctly!

It is also worth noting that before you allow users of your application to modify or even delete information, you should first take steps to authenticate and authorize them.

Getting started

Deleting data from database is technically very similar to inserting or updating data. You need to create a script which will take some parameter with a value to identify the record (e.g. a person). The script will then execute a SQL DELETE query to remove that record from database.

Lets create a PHP script which will handle the deletion of a person when a suitable parameter is supplied. This parameter should be the primary key of the row you want to delete – only one number. PHP script with DELETE SQL command:

<?php

require 'include/start.php';

if(!empty($_POST["id_person"])) {
    try {
        $stmt = $db->prepare("DELETE FROM person WHERE id_person = :idp");
        $stmt->bindValue(":idp", $_POST["id_person"]);
        $stmt->execute();
    } catch(PDOException $e) {
        exit($e->getMessage());
    }
}

$tplVars["pageTitle"] = "Delete a person";
$latte->render('templates/delete.latte', $tplVars);

That’s pretty much all. To test this, you can create a simple form with <input type="number">. Latte template with form:

{extends layout.latte}

{block content}
<h1>{$pageTitle}</h1>

<form action="delete.php" method="post">
    <label>ID of a person</label>
    <input type="number" name="id_person" />
    <br />

    <input type="submit" value="Delete a person" />
</form>
{/block}

The above shows an important development practice. If you are working on a new feature, you don’t have to start with modifying exiting code and produce a muddle of experiments and working code. You should always try the feature stand-alone. Only when it works and you know how it works, integrate it into the application. Possibly throwing parts of it along the way (the above Latte template won’t make it into the application).

Redirect After POST

If you submit the above form and reload the page in the browser (hit F5), you will receive a message from web browser similar to this:

In the form, I have used method="post" which means that the form is submitted using HTTP POST method. The HTTP POST method should be used to represent user actions (e.g. deleting a person). Reloading the page will send the same HTTP request – i.e. it will repeat the action (delete the person again), which is what the browser is asking about. To avoid this annoyance, you have to redirect after POST:

<?php

require 'include/start.php';

if(!empty($_POST["id_person"])) {
    try {
        $stmt = $db->prepare("DELETE FROM person WHERE id_person = :idp");
        $stmt->bindValue(":idp", $_POST["id_person"]);
        $stmt->execute();

        //redirect to prevent accidental reload with same id_person value
        header("Location: delete.php");
        exit();
    } catch(PDOException $e) {
        exit($e->getMessage());
    }
}

$tplVars["pageTitle"] = "Delete a person";
$latte->render('templates/delete.latte', $tplVars);

In the above script, I added the line header("Location: delete.php");. This calls the PHP header() function which sends a HTTP header. The Location header is used to inform the browser that the page has moved (redirected) to a new location. In this case, the new location is the same as the old location (delete.php), but the browser still moves to the new address. During this the POST data from the form is lost, because the HTTP GET method is used. This means that when the user actually sees the page, the browser will be looking at the second load of that page and it will know nothing about the submitted form.

The schematic below illustrates this in a sequence of steps:

This ‘trick’ should be used for all forms representing actions (submitted with the POST method), including insert and update forms.

Here you can see an important “feature” of web applications. The end user sees deleting of a person as a single action. From the application point of view, it involves three executions of a script to fulfill this action.

Task – Make a delete button

The above script works but it is not very useful. Users of your application do not understand primary keys and they do not want to remember some ID value which they have to type into a form. They want to see a list of persons and a nice delete button which they just click.

Extend your script which lists all persons with a delete button. Take the HTML form you have used in the previous example, and change the <input type="number" ...> type="hidden". Then put that form in each row of the users list. Pass the value of id_person column as a value of the hidden form field. Remember to extend the SELECT SQL command to retrieve the id_person column. Also remember to redirect back to list of persons after deletion.

{extends layout.latte}

{block content}
<table>
    <tr>
        <th>First name</th>
        <th>Last name</th>
        <th>Nickname</th>
        <th>Age</th>
        <th>Delete</th>
    </tr>
    {foreach $persons as $person}
    <tr>
        <td>{$person['first_name']}</td>
        <td>{$person['last_name']}</td>
        <td>{$person['nickname']}</td>
        <td>{$person['age']}</td>
        <td>
            <form action="delete.php" method="post">
                <input type="hidden" name="id_person" value="{$person['id_person']}"/>
                <input type="submit" value="Delete"/>
            </form>
        </td>
    </tr>
    {/foreach}
</table>
{/block}

Next Steps

Take a look at the JavaScript article to extend your form with a confirmation popup. It is a good idea to let the user confirm important information deletion because this action cannot be undone.

Deleting Referenced Records

There are foreign keys between the person table and other tables (the person table is referenced from the records in the relation or the contact table). When you take a look at the Foreign keys section of the contact table details in the Adminer, you can see that there is NO ACTION defined under the ON DELETE event:

This means that if you try to delete a person record, the database server has no defined action to do with contacts related to it – the database does not know what to do with person’s contacts when that person record ceases to exist. Because of the foreign key constraint, the id_person column in the contact table cannot store anything else than values from the id_person column in the person table. As a result, the database has to reject your DELETE command.

Of course, it is not meaningful to keep contact entries which do not belong to anyone. We should therefore set that ON DELETE behavior to CASCADE (use “Alter” button on right side):

The CASCADE ensures that the DELETE command will delete the record with all records depending on it. You should change this in every table which references person table, otherwise you won’t be able to delete persons with entries in those tables.

In other cases you might prefer to break the reference instead of deleting related entries. It is the case of the location – person relationship. When you want to delete an address which is referenced by a person record, you would rather set the foreign key deletion behavior for the id_location column in the person table to SET NULL instead of CASCADE. This setting would preserve a person record and set its id_location column to NULL (from now on, you will not know where she lives anymore). To be able to do this, the id_location column must support storing a NULL value.

Task – Configure Foreign Keys

Now configure the foreign keys in your database so that you can delete records as needed.

Summary

Now you now how to delete records from a database. I have also showed you how to redirect after POST to avoid action confirmation. You should also understand how the foreign keys guard the consistency of the data and that you need to think what to do with the dependent records – whether to delete them along or leave them in the database while removing the link to the deleted record. For a deeper explanation see the corresponding article. You can also take a look at the chapter about login to limit access to this functionality.

New Concepts and Terms

• Delete
• Hidden input
• Foreign keys
• Redirect
• CASCADE
• SET NULL

Control question

• What does a DELETE query do without WHERE clause?
• Should deletion of records be performed vie a POST or GET method?
• Do we need to confirm delete action?
• Is the redirect after deletion necessary, can you delete something twice?