Updating data

Getting Started
Supply Values
Obtaining Person ID
Task – Reuse the template
Summary
- New Concepts and Terms
- Control question

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 routes.php:

<?php

$app->get('/update-person', function(Request $request, Response $response, $args) {
    $this->view->render($response, 'person-update.latte');
})->setName('update-person');

$app->post('/update-person', function(Request $request, Response $response, $args) {
    //hardcoded ID of person
    $personId = 1;
    $data = $request->getParsedBody();
    if (empty($data['first_name']) || empty($data['last_name']) || empty($data['nickname'])) {
        $tplVars['message'] = 'Please fill in both names and nickname';
    } elseif (empty($data['gender']) || ($data['gender'] != 'male' && $data['gender'] != 'female')) {
        $tplVars['message'] = 'Gender must be either "male" or "female"';
    } else {
        // everything filled
        try {
            $stmt = $this->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', $data['first_name']);
            $stmt->bindValue(':last_name', $data['last_name']);
            $stmt->bindValue(':nickname', $data['nickname']);
            $stmt->bindValue(':gender', $data['gender']);

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

            if (empty($data['height']) || empty(intval($data['height']))) {
                $stmt->bindValue(':height', null);
            } else {
                $stmt->bindValue(':height', intval($data['height']));
            }
            $stmt->execute();
            $tplVars['message'] = "Person updated";
        } catch (PDOException $e) {
            $this->logger->error($e->getMessage());
            $tplVars['message'] = "Failed to update person (" . $e->getMessage() . ")";
        }
    }
    $this->view->render($response, 'person-update.latte', $tplVars);
});

Template person-update.latte:

{extends layout.latte}

{block title}Person update{/block}

{block body}
{if isset($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 routes.php:

<?php

$app->get('/update-person', function(Request $request, Response $response, $args) {
    //hardcoded ID of person
    $personId = 1;
    try {
        $stmt = $this->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) {
        $this->logger->error($e->getMessage());
        exit("Cannot get person " . $e->getMessage());
    }
    $this->view->render($response, 'person-update.latte', $tplVars);
})->setName('update-person');

$app->post('/update-person', function(Request $request, Response $response, $args) {
    //...
});

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

{extends layout.latte}

{block title}Person update{/block}

{block body}
{if isset($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 title}List of persons{/block}

{block body}
<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="{link update-person}?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. The id parameter will be accessible via $request->getQueryParam('id') method.

Now the table with persons contains a link next to each person and the link points to update-person?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.

See how I used the named route in macro {link update-person}. This is much easier than reasoning about correct URL for href attribute – let the framework generate the link for you.

File routes.php:

<?php

$app->get('/update-person', function(Request $request, Response $response, $args) {
    $personId = $request->getQueryParam('id');
    if (empty($personId)) {
        exit("Parameter 'id' is missing.");
    }
    try {
        $stmt = $this->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) {
        $this->logger->error($e->getMessage());
        exit("Cannot get person " . $e->getMessage());
    }
    $this->view->render($response, 'person-update.latte', $tplVars);
})->setName('update-person');

$app->post('/update-person', function(Request $request, Response $response, $args) {
    //...
});

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 $request object via getQueryParam() method, 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 can also define the route with {id} placeholder using $app->get('/update-person/{id}', ...), in such case, the link macro in the href attribute of <a> tag would look like this: <a href="{link update-person ['id' => $person['id_person']}">Edit</a> and you can use the $args associative array to retrieve ID of person $id = $args['id']. The URL itself will have this form: /update-person/XXX instead of update-person?id=XXX.

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.

You have to solve one last problem, when a the insert or update action fails, the values from input fields are lost. This is very annoying for the user. I already prepared the mechanism to display initial values of the update form, you can use this to pass submitted values to the form in POST routes simply by $tplVars['person'] = $data;.

PHP script for inserting a person:

<?php

$app->get('/add-person', function(Request $request, Response $response, $args) {
    //we have to prefill some default values to avoid warnings
    $tplVars['person'] = [
        'first_name' => '',
        'last_name' => '',
        'nickname' => '',
        'birth_day' => '',
        'gender' => 'male',
        'height' => ''
    ];
    $this->view->render($response, 'person-add.latte', $tplVars);
});

$app->post('/add-person', function (Request $request, Response $response, $args) {
    $data = $request->getParsedBody();
    if (empty($data['first_name']) || empty($data['last_name']) || empty($data['nickname'])) {
        $tplVars['message'] = 'Please fill in both names and nickname';
    } elseif (empty($data['gender']) || ($data['gender'] != 'male' && $data['gender'] != 'female')) {
        $tplVars['message'] = 'Gender must be either "male" or "female"';
    } else {
        // everything filled
        try {
            $stmt = $this->db->prepare(
                "INSERT INTO person (first_name, last_name, nickname, birth_day, height, gender) 
                VALUES (:first_name, :last_name, :nickname, :birth_day, :height, :gender)"
            );
            $stmt->bindValue(':first_name', $data['first_name']);
            $stmt->bindValue(':last_name', $data['last_name']);
            $stmt->bindValue(':nickname', $data['nickname']);
            $stmt->bindValue(':gender', $data['gender']);

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

            if (empty($_POST['height']) || empty(intval($data['height']))) {
                $stmt->bindValue(':height', null);
            } else {
                $stmt->bindValue(':height', intval($data['height']));
            }
            $stmt->execute();
            $tplVars['message'] = "Person added";
        } catch (PDOException $e) {
            $this->logger->error($e->getMessage());
            $tplVars['message'] = "Failed to insert person (" . $e->getMessage() . ")";
        }
    }
    $tplVars['person'] = $data;
    $this->view->render($response, 'person-add.latte', $tplVars);
});

PHP script for updating a person:

<?php

$app->get('/update-person', function(Request $request, Response $response, $args) {
    $personId = $request->getQueryParam('id');
    if (empty($personId)) {
        exit("Parameter 'id' is missing.");
    }
    try {
        $stmt = $this->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());
    }
    $this->view->render($response, 'person-update.latte', $tplVars);
})->setName('update-person');

$app->post('/update-person', function(Request $request, Response $response, $args) {
    $personId = $request->getQueryParam('id');
    if (empty($personId)) {
        exit("Parameter 'id' is missing.");
    }
    $data = $request->getParsedBody();
    if (empty($data['first_name']) || empty($data['last_name']) || empty($data['nickname'])) {
        $tplVars['message'] = 'Please fill in both names and nickname';
    } elseif (empty($data['gender']) || ($data['gender'] != 'male' && $data['gender'] != 'female')) {
        $tplVars['message'] = 'Gender must be either "male" or "female"';
    } else {
        // everything filled
        try {
            $stmt = $this->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', $data['first_name']);
            $stmt->bindValue(':last_name', $data['last_name']);
            $stmt->bindValue(':nickname', $data['nickname']);
            $stmt->bindValue(':gender', $data['gender']);

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

            if (empty($data['height']) || empty(intval($data['height']))) {
                $stmt->bindValue(':height', null);
            } else {
                $stmt->bindValue(':height', intval($data['height']));
            }
            $stmt->execute();
            $tplVars['message'] = "Person updated";
        } catch (PDOException $e) {
            $this->logger->error($e->getMessage());
            $tplVars['message'] = "Failed to update person (" . $e->getMessage() . ")";
        }
    }
    $tplVars['person'] = $data;
    $this->view->render($response, 'person-update.latte', $tplVars);
});

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

{extends layout.latte}

{block title}Person insert{/block}

{block body}
{if isset($message)}
    <p>{$message}</p>
{/if}
<h1>Add a new person</h1>
{include person-form.latte, operation => 'Insert person'}
{/block}

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

{extends layout.latte}

{block title}Person update{/block}

{block operation}Update person{/block}

{block body}
{if isset($message)}
    <p>{$message}</p>
{/if}
<h1>Update Person Details</h1>
{include person-form.latte, operation => 'Update person'}
{/block}

And a person-form.latte:

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

As you can see, templates allow you to reuse common blocks of HTML code (using the include statement), customise it with blocks 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?