The prior lab on queries covers pages displaying the results of canned queries -- the only way for the results to change is if the database contents change. A more exciting type of query page modifies its query based upon user input, as read from form data or cookies. There are many options for how this could work, and we will only look at one simple example in this lab.
Individual Listings
Continuing to work with the martial arts database, it would be nice to be able to view all the information about a single person. This includes their name, belt level, dojo memberships, and teaching schedule if any -- information that is scattered across several tables. We will create a page that will display all of these pieces of data for any selected person. We will also create an index page with a list of all the names in the database, that will link to the corresponding display.
Since our goal is to have a single page that displays the records for an arbitrary person, we must decide how that person is to be specified. For this particular application it makes sense to encode the person's id number in the url, just as form data are encoded under the GET method. Thus a sample url for our new page might look like showPerson.php?id=12. We don't mind exposing the id number in the URL for this application; it may even be an advantage because the link to a particular person's page becomes a URL that can be shared.
Here is the outline of what our showPerson.php page should do:
- Check that the $_GET array contains a value associated with the key id
- Perform a query to the database matching the id number given
- If the query does not return a single tuple, report an error, else
- Report the name and belt level
- Perform a second query to find and display the name of any dojo where this person is a student
- Perform a third query to find and display the name of any dojo where this person teaches, along with the schedule details
The results may be presented very simply. They may look something like this:
Name: Jill Jovial Belt level: Brown Studies at Blue Moon Fight Club Teaches at Blue Moon Fight Club, TTh 8:00 p.m. Teaches at 13 Monkeys Dojo, MWF 9:00 p.m. Back to listing
If the original query on the id returned nothing, it may be that the id was malformed or out of the proper range. In this case, the simplest thing to do would be to report an error. A slightly friendlier response would be to present a search box allowing the user to enter the name they are looking for. However, for purposes of this lab an error message will do.
The Index of Names
Individual listings aren't much use if the web site user has no way to access them, particularly if they use an internal id system that is arbitrary and unfamiliar. Any good interface requires a way for users to find the listings they want using the data they are likely to have access to, such as a person's name. One way to do this is via a search interface, where the user enters some information and is presented with a list of matches from the database to choose among for further display. However, if the database population is small (as in our example) then there is no need for an initial search -- we can simply present the entire database contents to the user.
The index page will use a simple form of canned query: select all tuples from the People table. Then display, in list format, all the names. The only complication is that each name must be surrounded by a link to showPerson.php with the id value for that row of the table. This is fairly simple to do; you can use an echo statement with variables filled in at various crucial points in the template. In the end, you should produce HTML that looks something like the following for each person:
<li><a href="showPerson.php?id=11">Jill Jovial</a></li>