Updating data

In the previous chapter, you have learned how to insert data into a database table. The important part is to handle optional values properly and implement sound form validation. Updating a record in a database is quite similar. The only challenge is to provide the user with the initial values of the edited record. Again, no new technologies are needed, it is just another combination of what you have learned already.

Getting Started

We’ll start by modifying the script for inserting a new person from the previous chapter. PHP Script person-update-1.php:

<?php

require 'include/start.php';

$message = '';
$personId = 1;

if (!empty($_POST['save'])) {
    // user clicked on the save button
    if (empty($_POST['first_name']) || empty($_POST['last_name']) || empty($_POST['nickname'])) {
        $message = 'Please fill in both names and nickname';
    } elseif (empty($_POST['gender']) || ($_POST['gender'] != 'male' && $_POST['gender'] != 'female')) {
        $message = 'Gender must be either "male" or "female"';
    } else {
        // everything filled
        try {
            $stmt = $db->prepare(
                "UPDATE person SET first_name = :first_name, last_name = :last_name, 
				nickname = :nickname, birth_day = :birth_day, gender = :gender, height = :height
				WHERE id_person = :id_person"
            );
            $stmt->bindValue(':id_person', $personId);
            $stmt->bindValue(':first_name', $_POST['first_name']);
            $stmt->bindValue(':last_name', $_POST['last_name']);
            $stmt->bindValue(':nickname', $_POST['nickname']);
            $stmt->bindValue(':gender', $_POST['gender']);

            if (empty($_POST['birth_day'])) {
                $stmt->bindValue(':birth_day', null);
            } else {
                $stmt->bindValue(':birth_day', $_POST['birth_day']);
            }

            if (empty($_POST['height']) || empty(intval($_POST['height']))) {
                $stmt->bindValue(':height', null);
            } else {
                $stmt->bindValue(':height', intval($_POST['height']));
            }
            $stmt->execute();
            $message = "Person updated";
        } catch (PDOException $e) {
            $message = "Failed to update person (" . $e->getMessage() . ")";
        }
    }
}

$tplVars['message'] = $message;
$latte->render('templates/person-update-1.latte', $tplVars);

Template person-update-1.latte:

{extends layout.latte}

{block content}
{if $message}
    <p>{$message}</p>
{/if}
<form method="post">
    <table>
        <tr>
            <td><label for="first_name">First name</label></td>
            <td><input type="text" name="first_name" id="first_name" required></td>
        </tr>
        <tr>
            <td><label for="last_name">Last name</label></td>
            <td><input type="text" name="last_name" id="last_name" required></td>
        </tr>
        <tr>
            <td><label for="nickname">Nickname</label></td>
            <td><input type="text" name="nickname" id="nickname" required></td>
        </tr>
        <tr>
            <td><label for="birth_day">Date of Birth</label></td>
            <td><input type="date" name="birth_day" id="birth_day"></td>
        </tr>
        <tr>
            <td>Gender</td>
            <td>
                <label>Male<input type="radio" name="gender" value="male" required></label>
                <label>Female<input type="radio" name="gender" value="female" required></label>
            </td>
        </tr>
        <tr>
            <td><label for="height">Height</label></td>
            <td><input type="number" name="height" id="height"></td>
        </tr>
        <tr>
            <td colspan="2">
                <button type="submit" name="save" value="save">Update Person</button>
            </td>
        </tr>
    </table>
</form>
{/block}

The only thing changed in the template so far is the button description (seeing an opportunity?). The PHP script is changed slightly more – I have changed INSERT to UPDATE and added the WHERE condition and therefore also another id_person parameter to identify what person should be updated.

Supply Values

First we need to obtain the values of the selected person from a database. Then we need to supply the existing values into the form – put them in the value attribute of each of the form controls. Lets’ still assume that we have the ID of the selected person in the $idPerson variable. We’ll get back to that later.

File person-update-2.php:

<?php

require 'include/start.php';

$message = '';
$personId = 1;

if (!empty($_POST['save'])) {
    // user clicked on the save button
    if (empty($_POST['first_name']) || empty($_POST['last_name']) || empty($_POST['nickname'])) {
        $message = 'Please fill in both names and nickname';
    } elseif (empty($_POST['gender']) || ($_POST['gender'] != 'male' && $_POST['gender'] != 'female')) {
        $message = 'Gender must be either "male" or "female"';
    } else {
        // everything filled
        try {
            $stmt = $db->prepare(
                "UPDATE person SET first_name = :first_name, last_name = :last_name, 
				nickname = :nickname, birth_day = :birth_day, gender = :gender, height = :height
				WHERE id_person = :id_person"
            );
            $stmt->bindValue(':id_person', $personId);
            $stmt->bindValue(':first_name', $_POST['first_name']);
            $stmt->bindValue(':last_name', $_POST['last_name']);
            $stmt->bindValue(':nickname', $_POST['nickname']);
            $stmt->bindValue(':gender', $_POST['gender']);

            if (empty($_POST['birth_day'])) {
                $stmt->bindValue(':birth_day', null);
            } else {
                $stmt->bindValue(':birth_day', $_POST['birth_day']);
            }

            if (empty($_POST['height']) || empty(intval($_POST['height']))) {
                $stmt->bindValue(':height', null);
            } else {
                $stmt->bindValue(':height', intval($_POST['height']));
            }
            $stmt->execute();
            $message = "Person updated";
        } catch (PDOException $e) {
            $message = "Failed to update person (" . $e->getMessage() . ")";
        }
    }
}

try {
    $stmt = $db->prepare("SELECT * FROM person WHERE id_person = :id_person");
    $stmt->bindValue(':id_person', $personId);
    $stmt->execute();
    $tplVars['person'] = $stmt->fetch();
    if (!$tplVars['person']) {
        exit("Cannot find person with ID: $personId");
    }
} catch (PDOException $e) {
    exit("Cannot get person " . $e->getMessage());
}

$tplVars['message'] = $message;
$latte->render('templates/person-update-2.latte', $tplVars);

Note the use of the condition:

if (!$tplVars['person']) {
    exit("Cannot find person with ID: $personId");
}

This is necessary, in case the person with the given ID would not exist. In that case the fetch() method returns false. Which means that querying for $person['first_name'] would produce a warning about an undefined index. To simplify the whole thing, we just terminate the entire script with exit. This is a bit harsh, but effective. In the template, we need to use the values of the $person variable to pre-fill the form. Note that on the radiobutton (or <select>), you have to use the selected attribute.

File person-update-2.latte:

{extends layout.latte}

{block content}
{if $message}
    <p>{$message}</p>
{/if}
<form method="post">
<table>
    <tr>
        <td><label for="first_name">First name</label></td>
        <td><input type="text" name="first_name" id="first_name" required value="{$person['first_name']}"></td>
    </tr>
    <tr>
        <td><label for="last_name">Last name</label></td>
        <td><input type="text" name="last_name" id="last_name" required value="{$person['last_name']}"></td>
    </tr>
    <tr>
        <td><label for="nickname">Nickname</label></td>
        <td><input type="text" name="nickname" id="nickname" required value="{$person['nickname']}"></td>
    </tr>
    <tr>
        <td><label for="birth_day">Date of Birth</label></td>
        <td><input type="date" name="birth_day" id="birth_day" value="{$person['birth_day']}"></td>
    </tr>
    <tr>
        <td>Gender</td>
        <td><label>Male
                <input type="radio" name="gender" value="male" required
                       {if $person['gender'] == 'male'}checked{/if}>
            </label>
            <label>Female
                <input type="radio" name="gender" value="female" required
                       {if $person['gender'] == 'female'}checked{/if}>
            </label>
        </td>
    </tr>
    <tr>
        <td><label for="height">Height</label></td>
        <td><input type="number" name="height" id="height" value="{$person['height']}"></td>
    </tr>
    <tr>
        <td colspan=" 2">
            <button type="submit" name="save" value="save">Update Person</button>
        </td>
    </tr>
</table>
{/block}

Now the script works and updates the person with the ID in the $personId variable. All we need now is to obtain the personId value from somewhere.

Obtaining Person ID

This is a question of the entire application design. How will the end user get to the page for updating a person? There are many possible solutions, but one of the easiest and still well usable is to link it from a list of persons.

Let’s update a list of persons to link each person to the update form, all we need is to add another field to the table in person-list.latte file:

{extends layout.latte}

{block content}
<form method="get">
    <label>Search for first name, last name or nickname:
        <input type="text" name="keyword">
    </label>
    <button type="submit" name="search" value="search" required>Search</button>
</form>
<p>You searched for '{$keyword}'</p>
<table>
    <tr>
        <th>First name</th>
        <th>Last name</th>
        <th>Nickname</th>
        <th>Age</th>
        <th></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><a href="person-update-3.php?id={$person['id_person']}">Edit</a></td>
        </tr>
    {/foreach}
</table>
{/block}

Don’t forget to add a <th> too, if you have added a new table column. Also verify that you have id_person among the list of selected columns from the database in person-list.php:

<?php

require 'include/start.php';

$tplVars['pageTitle'] = 'Persons List';
if (!empty($_GET['search'])) {
    if (!empty($_GET['keyword'])) {
        $keyword = $_GET['keyword'];
    } else {
        $keyword = '';
    }
} else {
    $keyword = '';
}

try {
    if ($keyword) {
        $stmt = $db->prepare('
	        SELECT first_name, last_name, nickname, AGE(birth_day) AS age, id_person
	        FROM person
	        WHERE (first_name ILIKE :keyword) OR
	              (last_name ILIKE :keyword) OR
	              (nickname ILIKE :keyword)
	        ORDER BY last_name, first_name
	    ');
        $stmt->bindValue('keyword', '%' . $keyword . '%');
        $stmt->execute();
    } else {
        $stmt = $db->query('
	        SELECT first_name, last_name, nickname, AGE(birth_day) AS age, id_person FROM person 
	        ORDER BY last_name, first_name
	    ');
    }
} catch (PDOException $e) {
    exit("I cannot execute the query: " . $e->getMessage());
}

$tplVars['keyword'] = $keyword;
$tplVars['persons'] = $stmt->fetchAll();
$latte->render('templates/person-list.latte', $tplVars);

Now the table with persons contains a link next to each person and the link points to person-update.php?id=XXX where XXX is the ID of the corresponding person. Now all you need is to pickup the ID passed in the URL address in the person-update.php script.

File person-update-3.php:

<?php

require 'include/start.php';

$message = '';
if (!empty($_GET['id'])) {
    $personId = $_GET['id'];
} else {
    exit("Parameter 'id' is missing.");
}

if (!empty($_POST['save'])) {
    // user clicked on the save button
    if (empty($_POST['first_name']) || empty($_POST['last_name']) || empty($_POST['nickname'])) {
        $message = 'Please fill in both names and nickname';
    } elseif (empty($_POST['gender']) || ($_POST['gender'] != 'male' && $_POST['gender'] != 'female')) {
        $message = 'Gender must be either "male" or "female"';
    } else {
        // everything filled
        try {
            $stmt = $db->prepare(
                "UPDATE person SET first_name = :first_name, last_name = :last_name, 
				nickname = :nickname, birth_day = :birth_day, gender = :gender, height = :height
				WHERE id_person = :id_person"
            );
            $stmt->bindValue(':id_person', $personId);
            $stmt->bindValue(':first_name', $_POST['first_name']);
            $stmt->bindValue(':last_name', $_POST['last_name']);
            $stmt->bindValue(':nickname', $_POST['nickname']);
            $stmt->bindValue(':gender', $_POST['gender']);

            if (empty($_POST['birth_day'])) {
                $stmt->bindValue(':birth_day', null);
            } else {
                $stmt->bindValue(':birth_day', $_POST['birth_day']);
            }

            if (empty($_POST['height']) || empty(intval($_POST['height']))) {
                $stmt->bindValue(':height', null);
            } else {
                $stmt->bindValue(':height', intval($_POST['height']));
            }
            $stmt->execute();
            $message = "Person updated";
        } catch (PDOException $e) {
            $message = "Failed to update person (" . $e->getMessage() . ")";
        }
    }
}

try {
    $stmt = $db->prepare("SELECT * FROM person WHERE id_person = :id_person");
    $stmt->bindValue(':id_person', $personId);
    $stmt->execute();
    $tplVars['person'] = $stmt->fetch();
    if (!$tplVars['person']) {
        exit("Cannot find person with ID: $personId");
    }
} catch (PDOException $e) {
    exit("Cannot get person " . $e->getMessage());
}

$tplVars['message'] = $message;
$latte->render('templates/person-update-2.latte', $tplVars);

You need to check whether the parameter id has been provided to the script, because nothing prevents anyone from manually visiting the script URL without the parameter. Otherwise there are no changes to the script or the template. The parameter id must be obtained from the $_GET variable, because it is passed in URL (not through form).

Notice again, how I get the solution in gradual steps. First I modify the existing script to update a person hardcoded in the script. When this works, I add a SELECT statement and update the template to pre-fill the form. Last I solve the problem of selecting the right person by modifying the person list template.

Task – Reuse the template

You have probably noticed that the templates for adding and updating a person are almost the same. They probably will be so similar, because even if we add other properties (database columns) for persons, it is very likely that they will have to be added to both forms. Hint: you will need an include command in template.

PHP script for inserting a person:

<?php

require 'include/start.php';

$message = '';
if (!empty($_POST['save'])) {
    // user clicked on the save button
    if (empty($_POST['first_name']) || empty($_POST['last_name']) || empty($_POST['nickname'])) {
        $message = 'Please fill in both names and nickname';
    } elseif (empty($_POST['gender']) || ($_POST['gender'] != 'male' && $_POST['gender'] != 'female')) {
        $message = 'Gender must be either "male" or "female"';
    } else {
        // everything filled
        try {
            $stmt = $db->prepare(
                "INSERT INTO person (first_name, last_name, nickname, birth_day, gender, height) 
                VALUES (:first_name, :last_name, :nickname, :birth_day, :gender, :height)"
            );
            $stmt->bindValue(':first_name', $_POST['first_name']);
            $stmt->bindValue(':last_name', $_POST['last_name']);
            $stmt->bindValue(':nickname', $_POST['nickname']);
            $stmt->bindValue(':gender', $_POST['gender']);

            if (empty($_POST['birth_day'])) {
                $stmt->bindValue(':birth_day', null);
            } else {
                $stmt->bindValue(':birth_day', $_POST['birth_day']);
            }

            if (empty($_POST['height']) || empty(intval($_POST['height']))) {
                $stmt->bindValue(':height', null);
            } else {
                $stmt->bindValue(':height', intval($_POST['height']));
            }
            $stmt->execute();
            $message = "Person inserted";
        } catch (PDOException $e) {
            $message = "Failed to insert person (" . $e->getMessage() . ")";
        }
    }
}

$tplVars['operation'] = "Insert Person";
$tplVars['message'] = $message;
$latte->render('templates/person-insert-4.latte', $tplVars);

PHP script for updating a person:

<?php

require 'include/start.php';

$message = '';
if (!empty($_GET['id'])) {
    $personId = $_GET['id'];
} else {
    exit("Parameter 'id' is missing.");
}

if (!empty($_POST['save'])) {
    // user clicked on the save button
    if (empty($_POST['first_name']) || empty($_POST['last_name']) || empty($_POST['nickname'])) {
        $message = 'Please fill in both names and nickname';
    } elseif (empty($_POST['gender']) || ($_POST['gender'] != 'male' && $_POST['gender'] != 'female')) {
        $message = 'Gender must be either "male" or "female"';
    } else {
        // everything filled
        try {
            $stmt = $db->prepare(
                "UPDATE person SET first_name = :first_name, last_name = :last_name, 
				nickname = :nickname, birth_day = :birth_day, gender = :gender, height = :height
				WHERE id_person = :id_person"
            );
            $stmt->bindValue(':id_person', $personId);
            $stmt->bindValue(':first_name', $_POST['first_name']);
            $stmt->bindValue(':last_name', $_POST['last_name']);
            $stmt->bindValue(':nickname', $_POST['nickname']);
            $stmt->bindValue(':gender', $_POST['gender']);

            if (empty($_POST['birth_day'])) {
                $stmt->bindValue(':birth_day', null);
            } else {
                $stmt->bindValue(':birth_day', $_POST['birth_day']);
            }

            if (empty($_POST['height']) || empty(intval($_POST['height']))) {
                $stmt->bindValue(':height', null);
            } else {
                $stmt->bindValue(':height', intval($_POST['height']));
            }
            $stmt->execute();
            $message = "Person updated";
        } catch (PDOException $e) {
            $message = "Failed to update person (" . $e->getMessage() . ")";
        }
    }
}

try {
    $stmt = $db->prepare("SELECT * FROM person WHERE id_person = :id_person");
    $stmt->bindValue(':id_person', $personId);
    $stmt->execute();
    $tplVars['person'] = $stmt->fetch();
    if (!$tplVars['person']) {
        exit("Cannot find person with ID: $personId");
    }
} catch (PDOException $e) {
    exit("Cannot get person " . $e->getMessage());
}

$tplVars['operation'] = 'Update Person';
$tplVars['message'] = $message;
$latte->render('templates/person-update-4.latte', $tplVars);

Latte template script for inserting a person (person-insert-4.latte):

{extends layout.latte}

{block content}
{if $message}
    <p>{$message}</p>
{/if}
<h1>Add a new person</h1>
{include person-form.latte}
{/block}

Latte template script for updating a person (person-update-4.latte):

{extends layout.latte}

{block content}
{if $message}
    <p>{$message}</p>
{/if}
<h1>Update Person Details</h1>
{include person-form.latte}
{/block}

And a person-form.latte:

{block form}
<form method="post">
<table>
    <tr>
        <td><label for="first_name">First name</label></td>
        <td><input type="text" name="first_name" id="first_name" required value="{$person['first_name']}"></td>
    </tr>
    <tr>
        <td><label for="last_name">Last name</label></td>
        <td><input type="text" name="last_name" id="last_name" required value="{$person['last_name']}"></td>
    </tr>
    <tr>
        <td><label for="nickname">Nickname</label></td>
        <td><input type="text" name="nickname" id="nickname" required value="{$person['nickname']}"></td>
    </tr>
    <tr>
        <td><label for="birth_day">Date of Birth</label></td>
        <td><input type="date" name="birth_day" id="birth_day" value="{$person['birth_day']}"></td>
    </tr>
    <tr>
        <td>Gender</td>
        <td>
            <label>Male
                <input type="radio" name="gender" value="male" required
                       {if $person['gender'] == 'male'}checked{/if}>
            </label>
            <label>Female
                <input type="radio" name="gender" value="female" required
                       {if $person['gender'] == 'female'}checked{/if}>
            </label>
        </td>
    </tr>
    <tr>
        <td><label for="height">Height</label></td>
        <td><input type="number" name="height" id="height" value="{$person['height']}"></td>
    </tr>
    <tr>
        <td colspan=" 2">
            <button type="submit" name="save" value="save">{$operation}</button>
        </td>
    </tr>
</table>
{/block}

As you can see, templates allow you to reuse common blocks of HTML code (using the include statement) and remove repeating code.

Summary

In this chapter you have learned how to update data in the database. This is technically no different to selecting or inserting data, it is just a combination of all the approaches you have learned in previous chapters. I have also demonstrated how to reuse code using templates.

New Concepts and Terms

  • supply values to form controls
  • pass values between scripts
  • include templates

Control question

  • What difference is between form input elements for update and add form?
  • What does UPDATE query do without WHERE clause?