This lab further explores the interface between PHP and SQLite. It assumes that you have completed the labs on both canned queries and custom queries, although these are not strictly necessary. This lab will continue to use the martial arts database used in those previous labs.
One important note is that in order to make any changes to the database contents, the web server (apache) must have permission to write to the database file. You will therefore need to change the permissions on martial.db3 to 666. It is not good to have a writeable file inside the web file tree, so you should place it in a different directory as we did with the guestbook file. If you fail to make this change, an otherwise correct program will seem to fail for no apparent reason.
Pieces of the Puzzle
Updating a database via a web interface is somewhat more complicated than what we have tried to do so far, for several reasons. Foremost among these is that several pieces of the site must work together in order to accomplish the update. For (hopefully) obvious reasons, it is not a good idea to simply give the site visitor access to make full SQL operations. The web interface must therefore mediate the user's actions, making it easier to perform desired operations while simultaneously preventing malicious acts. Typically, the following three tasks will be handled by different areas of the site:
- Identifying the Update
The site must provide some mechanism for the user to indicate what sort of update is desired. Typically this will be via a form, accessible via a main listing. However, in many cases the contents of this form will have to be produced by PHP scripting that takes into account the current contents of the database. For example, a page that allows one to enter a new student affiliation may wish to query the database to determine which dojos are options for membership (and may also do a second query to find out which ones the person is already registered at). The results could be placed into the option lines of a popup menu.
Designing a site that is easy to use and attractive can be a challenge. One approach is to provide a separate form for every possible item of information that could be changed. However, a site designed in this manner will eb difficult to maintain, and probably also confusing to visitors. It is probably better to present fewer forms with more options, organized according to large thematic areas. Thus for example in the martial arts database, it makes sense to put the form to update the name and belt level of a person on the same page as the forms to add or remove dojo associations and teaching responsibilities. Each type of update is placed into a separate form to keep its data separate from the others.
If you are familiar with server-side scripting languages such as Javascript, another technique that can be very powerful is to include hidden update forms within the general browsing page, with a button or other control to make them visible when desired. This makes the database interaction fairly compact but is more complicated to achieve since the scripts on both ends of the web connection must work together to achieve the goal.
When the user submits a form asking to update the database, some piece of PHP script must detect that form data and perform the associated request. Typically this will begin by determining the type of request being made, then gathering the information required to carry it out. (Presumably, all of this has been provided with the submitted form data.) Once all the required information has been assembled, putting it together into a SQL command to achieve the desired effect is usually fairly straightforward.
In a web environment, our PHP scripts only execute in response to a user page request. Thus whenever the user submits a form to update a page, after carrying out the update, the php file must send some sort of HTML content back to the user. Often it will make sense for this to be the same page just viewed, with whatever changes were made now reflected in the new display. Such a page is called reentrant: the page submits the update to itself, processes it, and then redisplays. Presumably the first time the page was viewed there was no update to process, so it must also be prepared for this situation.
An alternative to a reentrant page is a central processing page that takes input from a number of forms and makes whatever changes are indicated. Also supplied with the update information is a destination page URL. The processing script makes the desired updates to the database and then forwards the browser to the specified destination page. If the destination page is the same one that submitted the change request, it may seem to be a reentrant page because the action of the processing page will not necessarily be visible to the visitor. But it can equally well send the visitor to some other page. This might make sense, for example, when the update specified was to delete the record that had been viewed. In this case, the visitor might be brought back to the main listing of records.
An Example
To illustrate this process, you will design a page that will allow a visitor to edit the records of people in the martial arts database. To begin with, we will design a simple reentrant page called editPerson.php. You can add a link to this page from the showPerson.php page you wrote in the lab on custom queries. You can base its behavior on showPerson.php; like the model it can take the id of the person to edit as an addendum to the URL: editPerson.php?id=11.
Begin by designing the form. Add a text input for the name and a popup menu for the belt level. You should query the database to figure out the default values that should be filled in or selected for each of these. In order to make the menu creation simpler, I provide the function below, which takes an array of options and some other information and emits the HTML for the menu.
// Emits tags to create a popup menu
// The first array gives the options that will be visible to the visitor
// The second array gives the values that will be returned on the form
// The third argument is the name of the select element to be generated
// The last (optional) element is the number or option label of the default
function selectMenu($options,$values,$name,$selection=-1) {
echo "<select name=\"$name\">\n";
for ($i = 0; $i < count($options); $i++) {
$opt = $options[$i];
$val = $values[$i];
//echo "<p>$i $opt $selection</p>";
if (($i===$selection)||($options[$i]===$selection)) {
$selected = " selected=\"selected\"";
} else {
$selected = "";
}
echo "<option value=\"$val\"$selected>$opt</option>\n";
}
echo "</select>\n";
}
Usage:
$belts = Array("White","Yellow","Green","Blue","Brown","Black");
selectMenu($belts,$belts,"belt");
Don't forget to include a Commit button (or equivalent) to submit the form data for processing.
Next you need to write the reentrant script that will detect form data and make updates if necessary. The array_key_exists function applied to the $_POST superglobal will be handy here. Use it to determine whether the Commit button you created has been clicked. If so, extract the new name and belt level from the form data. Then assemble a query that will update the database.
Since you have designed this as a reentrant page, the part that displays something else is already written. Put the update check at the top of the page. The remainder should display the entry to be edited again -- only this time the defaults will reflect whatever changes were made. Don't forget to include some sort of link back to the main listing somewhere on this page, or you will never be able to leave it!
You might decide that you don't want the edit page to display again after the Commit button is clicked. If you would rather have the user sent somewhere else, such as back to the main person listing, you can do so as long as no HTML content has yet been emitted by your script. The following piece of PHP will cause the browser to change to the new URL provided (in this case, listPeople.php).
header("Location: listPeople.php");
Going Further
If you have the time, it would be nice to add a little more to this page. The next thing to add is a listing of the person's dojo memberships, with a button to delete the membership appearing next to it. Each such button should be in its own form, and include a hidden form input that provides the id of the dojo membership that is to be dropped.
If you can delete something, you should probably also provide a way to put it back. After the dojo membership listing, add a form with controls that allow new memberships to be added. This should take the form of another popup array; you can query the database for the possible dojos available and then use the selectMenu function provided above.
If you wish, you can also provide similar functionality to add and delete teaching sessions; these have the added wrinkle that the user needs to be able to edit the time field for each. Finally, if you add the option to delete and create new person records, you will have made the simple sample database almost entirely editable. The only piece left uneditable is the set of dojos and their information. However, it is unlikely that you will be able to finish all this during the lab session. Just focus on getting the first example to work, and move on to the others if you have time.