This lab extends the query lab, augmenting the interface with an "add person" page. We will continue to use the same database. Since we don't yet have full editing functionality, remember that you can always use the command-line MySQL interface to edit any mistakes, or simply remove records if the list of people begins to get too long.
We will need to incorporate a little more HTML for this project. Specifically, we will include some form elements, which are designed to gather data from the user. These inputs will be sent to a PHP script which will process them and emit a SQL command to add the new record to the database.
An Input Form
Make yourself a copy of addperson.php. If you examine the source you will see that it contains no PHP commands at all -- it could just as easily have a .html extension. However, it does contain a few new HTML tags that we have not encountered before. One is form, which groups the input elements and directs their results to a target destination when the form is submitted -- in this case, the destination is do_add.php. Also worthy of note is the chosen submission method. In this case we have chosen POST; the other alternative is GET. Four other tags create the form input elements -- these are input for the text box, select and option for the popup menu, and button for the submit button.
We will adopt a simple design where display, data collection, and updates are handled by three separate but interlinked pages. The roster listing from the previous lab will serve as display. The addperson.php page collects the data. Finally, we will write do_add.php to execute the change and then redirect the browser back to the roster display.
Reading Form Data in PHP
Data from a form may be passed by either of two special global variables, named $_POST or $_GET. As mentioned, we will use the former. Both are associative arrays containing values for all reporting form elements, stored under the element's name as index. We care about two pieces of information: the new person's name and their belt level. Examination of addperson.php shows that the input elements for these are rather uncreatively named name and belt. This means that their values are stored as $_POST['name'] and $_POST['belt'], respectively.
It might be tempting to whip up a SQL query string using these values and submit it to the database. But the most straighforward approach would leave us vulnerable to SQL injection. Whenever we use external data in a query, we need to use a prepared statement. When we add the user inputs to the prepared statement, they are automatically sanitized so that they won't allow SQL injection.
Prepared Statements
There are a number of ways to set up prepared statements with PDO, and this lab will show you just one. Fortunately it should be reliable for all your needs. The first step is to write a string containing the SQL command you intend to issue, except that everywhere a piece of user data will be included you use a question mark instead.
$query = "INSERT INTO People(name,belt,id) VALUES (?,?,?);";
The next steps are to transform the string into a prepared statment, and bind values to those missing spots.
$stmt= $dbh->prepare($query); $stmt->bindParam(1,$_POST['name'],PDO::PARAM_STR,100)
The example above shows the creation of the prepared statment and the binding of the first parameter (the name). The arguments to the bindParam method are, in order: the number of the parameter you are setting (counting starts from 1, not 0), the value you wish to assign, the type of this value, and (for strings only) the maximum length. Other possible values for the third argument are PDO::PARAM_INT, PDO::PARAM_BOOL, and PDO::PARAM_NULL.
You should bind values to the second and third parameters as well. Once you have done that, the command is ready to be carried out using $stmt->execute().
Note that each of the steps above could potentially generate an error. Error states are indicated by returning a boolean value; it is true when there is no error and false when an error has occurred. If an error has occurred, we can find out more by looking at $stmt->error. We can place each command in the test of an if statement to handle any errors that might arise.
if (!$stmt->bindParam(1,$_POST['name'],PDO::PARAM_STR,100)) {
echo "Error while binding parameter: " . $stmt.error;
}
if (!$stmt->execute()) {
echo "Error while executing command: " . $stmt.error;
}
You may have noticed that our form didn't include an id number for the new record. That's because the id numbers are really just an internal convenience -- we don't want the user to have to worry about them. If we had created our table with the AUTO_INCREMENT attribute for this column, we wouldn't have to specify a value at all, and the database software would automatically figure out the next value to use. With a bit of work we can do something similar by issuing a query.
$result = $dbh->query("SELECT MAX(id)+1 FROM People;");
$id = $result->fetch();
$id = $id[0];
After we have added the new tuple to the database, the last thing we should do in the file is to direct the browser back to the display page. You can do this with the header function.
header('Location: belts.php');
Don't add this to your file until everything is fully debugged, or you won't see any of the error messages from your page. Speaking of debugging, the bindParam method will return false if there is any problem. You can display the error message with this line:
echo 'Error binding params: ' . $stmt->error;
To Do
- Finish the pages needed to add a person.
- If the number of people becomes large, it may get difficult to find a particular record. Create a page (or set of pages) that will collect a name from the user, and then look up and display their belt level. Because the user provides the name, you should use a prepared statement to avoid SQL injection, even though this action is not supposed to modify the database at all. Can you make your search query handle slight misspellings and/or incomplete information (e.g., last name or first name only)? What should you do if more than one record is returned?
- If you wish, and you have time, you can modify the search results page so that it offers the option of changing the person's belt level and/or their name.