Inserting data

In previous chapters, you have learned how to work with HTML forms. Now we will add a little bit more of a code to insert data into the database. There is no new technology necessary for this, all you need is SQL INSERT and working with HTML forms. Yet there are some things, which are worth deeper explanation.

Getting started

We will create a page for inserting new persons in the database. Let’s start with the HTML form.

templates/person-add.latte:

{extends layout.latte}

{block title}Add person{/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"></td>
		</tr>
		<tr>
			<td><label for="last_name">Last name</label></td>
			<td><input type="text" name="last_name" id="last_name"></td>
		</tr>
		<tr>
			<td><label for="nickname">Nickname</label></td>
			<td><input type="text" name="nickname" id="nickname"></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 colspan="2"><button type="submit" name="save">Add Person</button></td>
		</tr>
	</table>
{/block}

src/routes.php:

<?php

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

As you can see, I put the form in a table so that it is nicely arranged. If you don’t like it, use another template! This is the good thing about templates – they separate the HTML code from the PHP code, you change one without worrying about the other. If you use the same form control names you can use the bellow PHP script regardless of what your form looks like!

Notice, that I used a $message variable in the template. It will come in handy.

Inserting Data

It is important to use a correct HTTP method. Since inserting a person is a data changing action, I am using the POST method (<form method='post'>). This means that we will find the form data with $request->getParsedBody() method when the user submits the form. In pure PHP it would be in $_POST variable.

First we need to check that the user submitted the form. If yes, then we need to validate the input from the user. All three fields are required (they are mandatory in the person table) and must be validated on the server (in the PHP script) because the client side validation is insufficient. If the user input is valid, then we can send an INSERT query to the database, to insert the data. We will need a prepared statement to pass in the values.

<?php

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

$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';
    } else {
        // everything filled
        try {
            $stmt = $this->db->prepare(
                "INSERT INTO person (first_name, last_name, nickname, birth_day) 
                VALUES (:first_name, :last_name, :nickname, :birth_day)"
            );
            $stmt->bindValue(':first_name', $data['first_name']);
            $stmt->bindValue(':last_name', $data['last_name']);
            $stmt->bindValue(':nickname', $data['nickname']);
            if (empty($data['birth_day'])) {
                $stmt->bindValue(':birth_day', null);
            } else {
                $stmt->bindValue(':birth_day', $data['birth_day']);
            }
            $stmt->execute();
            $tplVars['message'] = "Person added";
        } catch (PDOException $e) {
            $this->logger->error($e->getMessage());
            $tplVars['message'] = "Failed to insert person (" . $e->getMessage() . ")";
        }
    }
    $this->view->render($response, 'person-add.latte', $tplVars);
});

Note that I didn’t use exit in the catch statement. Failure to insert data into the database is a non-fatal error – i.e. the application can continue and display an error to the user and let him correct the error. So I have simply assigned the error to the $message variable and then passed that to the template in $tplVars['message'] = $message;.

There are actually different types of SQL errors, you can check the value returned by $e->getCode() method and display appropriate error message. For example the duplicate record error is 23505 (unique violation) and the error in date format has code 22007. Check out the list of error codes for PostgreSQL or MariaDB/MySQL.

Try the above script and verify that the form validation works fine. If you put the required attribute to the form controls, either remove it for the test, or use developer tools to do so temporarily.

The part of the PHP script which requires deeper explanation is probably this:

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

You can use shorter ternary operator to save some space in your source code: $stmt->bindValue(':birth_day', empty($data['birth_day']) ? null : $data['birth_day']);.

In the person table in the database. The column birth_day allows NULLs, i.e. its value is not required. If the user does not fill the date input element, the PHP script will receive an empty string. The database server will fail to insert this, because the empty string is neither a valid date, nor a NULL (the database server is more concerned about data types than PHP). Therefore we need to supply manually the null value in case the birth_day is not filled. Luckily, the PHP null is nicely compatible with the database NULL. Again it is very important that you understand what values originate from where and what variables are connected:

Code schema -- Script for inserting data

Task – Extend the form

Now extend the form by adding other columns from the person table – gender and height. Use proper form controls for the values. Check whether each column is required and handle NULLs correctly if necessary.

{extends layout.latte}

{block title}Add person{/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">Add Person</button></td>
		</tr>
	</table>
{/block}
<?php

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

$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() . ")";
        }
    }
    $this->view->render($response, 'person-add.latte', $tplVars);
});

The radio buttons may be replaced by the <select> control. The condition (empty($data['gender']) || ($data['gender'] != 'male' && $data['gender'] != 'female')) could be also written as (empty($data['gender']) || !in_array($data['gender'], ['male', 'female'])). The condition (empty($data['height']) || empty(intval($data['height']))) first checks that the value $data['height'] is defined and non-empty. Then it checks if the value converted to an integer (using the intval function) is still not empty (i.e. non-zero). In both conditions the order of conditional expressions is important. It must always start with the check for an empty $data field due to partial boolean evaluation.

Summary

In this chapter you have learned how to inset data from a HTML form into a database table. As usual there are multiple options how you can implement the application logic – especially the value validation (e.g. you could trigger an error if height is not a number instead of ignoring it). When inserting data to the database, you need to be aware of what values are optional and handle the optional values correctly.

Notice one annoying behaviour – when the process of adding a person fails, the values a user inserted into the fields are gone. We will take care of it in the next step.

New Concepts and Terms

  • Optional values
  • NULL

Control question

  • Which SQL error codes are worth catching?
  • What are the possible reasons that an application fails to insert data?