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.
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.
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.
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.
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.
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.