Putting PHP to Work

The remaining part of this chapter tackles the goals set out in Chapter 1 that we have yet to accomplish:

  • For the grade-keeping project, we need to write a script that allows us to enter and edit test and quiz scores.

  • For visitors to the Historical League Web site, we want to develop an online quiz about U.S. presidents and to make it interactive so that the questions can be generated on-the-fly.

  • We also want to allow Historical League members to edit their directory entries online. This will keep the information up to date and reduce the amount of entry editing that must be done by the League secretary.

Each of these scripts generates multiple Web pages and communicates from one invocation of the script to the next by means of information embedded in the pages it creates. If you're not familiar with the concept of inter-page communication, you might want to read the "Writing Multiple-Purpose Pages" section in Chapter 7.

Entering Student Scores

In this section, we'll turn our attention to the grade-keeping project and write a score_entry.php script for managing test and quiz scores. The Web directory for the project is named gp under the Apache document tree root, which corresponds to this URL for our site:

http://www.snake.net/gp/

The directory is thus far unpopulated, so visitors requesting that URL may receive only a "Page not found" error or an empty directory listing page. To rectify that problem, create a short script named index.php and place it in the gp directory to serve as the project's home page. The following script suffices for now. It contains two links. One is to the score_browse.pl script we wrote in Chapter 7 that pertains to the grade-keeping project. The other is to the score_entry.php script that we're about to write:

<?php 
# Grade-Keeping Project home page

include "sampdb.php";

$title = "Grade-Keeping Project";
html_begin ($title, $title);
?>
<p>
<a href="/cgi-bin/score_browse.pl">View</a> test and quiz scores
</p>
<p>
<a href="score_entry.pl">Enter or edit</a> test and quiz scores
</p>

<?php
html_end ();
?>

Let's consider how to design and implement the score_entry.php script that will let us enter a set of test or quiz scores or edit existing sets of scores. Entry capability will be useful whenever we have a new set of scores to add to the database. Editing capability is necessary for changing scores later, for example, to handle scores of students who take a test or quiz later than the rest of the class due to absence for illness or other reason (or, perish the thought, to correct errors should we happen to enter a score incorrectly). The conceptual outline of the score entry script is as follows:

  • The initial page presents a list of known grade events and allows you to choose one or to indicate that you want to create a new event.

  • If you choose to create a new event, the script presents a page that allows you to specify the date and type of event (test or quiz). After it adds the event to the database, the script redisplays the event list page, which at that point will include the new event.

  • If you choose an existing event from the list, the script presents a score-entry page showing the event ID, date, and type, a table that lists each student in the class, and a Submit button. Each row in the table shows one student's name and current score for the event. For new events, all scores will be blank. For existing events, the scores will be those you entered at some earlier time. You can fill in or change the scores and then select the Submit button. The script will then enter the scores into the score table or revise existing scores.

Before implementing the score_entry.php script, we must take a slight detour to discuss how input parameters work in PHP. The script needs to perform several different actions, which means that it must pass a status value from page to page so that the script can tell what it's supposed to do each time it's invoked. One way to do this is to pass parameters at the end of the URL. For example, we can add a parameter named action to the script URL as follows:

http://www.snake.net/gp/score_entry.php?action=value

Parameter values may also come from the contents of a form submitted by the user. Each field in the form that is returned by the user's browser as part of a form submission will have a name and a value.

PHP makes input parameters available to scripts through special arrays. Parameters encoded at the end of a URL and sent as a GET request are placed in the $HTTP_GET_VARS global array. For parameters received in a POST request (such as the contents of a form that has a method attribute value of POST), the parameters are placed in the $HTTP_POST_VARS global array. These arrays are associative, with elements keyed to the parameter names. For example, an action parameter sent in the URL becomes available to a PHP script as the value of $HTTP_GET_VARS["action"]. If a form contains a field named address and the form is submitted via a POST request, the value becomes available as $HTTP_POST_VARS["address"].

Parameter values are available for fields in forms, too. Suppose a form contains fields named name and address. When a user submits the form, the Web server invokes a script to process the form's contents. If the form is submitted as a GET request, the script can determine what values were entered into the form by checking the values of the $HTTP_GET_VARS["name"] and $HTTP_GET_VARS["address"] variables. If the form is submitted as a POST request, the variables will be in $HTTP_POST_VARS["name"] and $HTTP_POST_VARS["address"]. For forms that contain a lot of fields, it can be inconvenient to give them all unique names. PHP makes it easy to pass arrays in and out of forms. If you use field names such as x[0], x[1], and so on, PHP will store them in $HTTP_GET_VARS["x"] or $HTTP_POST_VARS["x"], which will be an array. If you assign the array value to a variable $x, the array elements are available as $x[0], $x[1], and so on.

In most cases, you won't care whether a parameter was submitted via GET or POST, so we can write a utility routine (script_param()), that takes a parameter name and checks both arrays to find the parameter value. If the parameter is not present, the routine returns an unset value:

function script_param ($name) 
{
global $HTTP_GET_VARS, $HTTP_POST_VARS;

    unset ($val);
    if (isset ($HTTP_GET_VARS[$name]))
        $val = $HTTP_GET_VARS[$name];
    else if (isset ($HTTP_POST_VARS[$name]))
        $val = $HTTP_POST_VARS[$name];
    # return @$val rather than $val to prevent "undefined value"
    # messages in case $val is unset and warnings are enabled
    return (@$val);
}

Note that the script_param() function explicitly declares the arrays to be global using the global keyword. PHP global variables are accessible without global only in global scope, such as when you use them in the main body of a script. In non-global scope, such as within a function, global indicates to PHP that you mean to access a global variable rather than a variable that is local to the function and just happens to have the same name. The function also uses the @ operator in the return() statement to suppress error messages. (If a parameter is not available, script_param() returns an unset value, and if the script happens to have modified the error reporting level to include warnings, returning an unset value would otherwise cause a warning to be printed.)

PHP 4.1 introduced two new parameter arrays: $_GET and $_PUT. These are similar to $HTTP_GET_VARS and $HTTP_POST_VARS, but are superglobal arrays. This means they are accessible in any scope without a global declaration. To modify script_param() to use the newer superglobal arrays if they are available, write the function as follows:

function script_param ($name) 
{
global $HTTP_GET_VARS, $HTTP_POST_VARS;

    unset ($val);
    if (isset ($_GET[$name]))
        $val = $_GET[$name];
    else if (isset ($_POST[$name]))
        $val = $_POST[$name];
    else if (isset ($HTTP_GET_VARS[$name]))
        $val = $HTTP_GET_VARS[$name];
    else if (isset ($HTTP_POST_VARS[$name]))
        $val = $HTTP_POST_VARS[$name];
    if (isset ($val) && get_magic_quotes_gpc ())
        $val = remove_backslashes ($val);
    # return @$val rather than $val to prevent "undefined value"
    # messages in case $val is unset and warnings are enabled
    return (@$val);
}

This modified version of script_param() is the one that you'll find in the sampdb.php library file in the sampdb distribution. It allows a script to easily access by name the value of input parameters without being concerned which array they might be stored in. You'll notice that this revised version also contains another change in addition to checking the $_GET and $_PUT arrays; after extracting the parameter value, it passes the value to remove_backslashes(). The purpose of this is to adapt to configurations that have the magic_quotes_gpc setting enabled with a line like the following in the PHP initialization file:

magic_quotes_gpc = On; 

If that setting is turned on, PHP adds backslashes to parameter values to quote special characters such as quotes or backslashes. The extra backslashes make it more difficult to check parameter values to see if they're valid, so remove_backslashes() strips them out. It's implemented as follows. The algorithm is recursive because in PHP 4 it's possible to create parameters that take the form of nested arrays:

function remove_backslashes ($val) 
{
    if (!is_array ($val))
        $val = stripslashes ($val);
    else
    {
        reset ($val);
        while (list ($k, $v) = each ($val))
            $val[$k] = remove_backslashes ($v);
    }
    return ($val);
}

Web Input Parameters and register_globals

You may be familiar with PHP's register_globals configuration setting that causes Web input parameters to be registered directly into script variables. For example, a form field or URL parameter named x would be stored directly into a variable named $x in your script. Unfortunately, enabling this capability means that clients can set variables in your scripts in ways you may not intend. This is a security risk, so the PHP developers now recommend that register_globals be disabled. The script_param() routine deliberately uses only the arrays provided specifically for input parameters, which is more secure and also works regardless of the register_globals setting.

Now that we have support in place for extracting Web input parameters conveniently, we can use that support for writing score_entry.php. That script needs to be able to communicate information from one invocation of itself to the next. We'll use a parameter called action for this, which can be obtained when the script executes as follows:

$action = script_param ("action"); 

If the parameter isn't set, that means the script is being invoked for the first time; otherwise, it can test the value of $action to find out what to do. The general framework for script_entry.php looks as follows:

<?php 
# score_entry.php - Score Entry script for grade-keeping project

include "sampdb.php";
# define action constants
define ("SHOW_INITIAL_PAGE", 0);
define ("SOLICIT_EVENT", 1);
define ("ADD_EVENT", 2);
define ("DISPLAY_SCORES", 3);
define ("ENTER_SCORES", 4);

# ... put input-handling functions here ...

$title = "Grade-Keeping Project -- Score Entry";
html_begin ($title, $title);

sampdb_connect()
    or die ("Cannot connect to database server");

# determine what action to perform (the default if
# none is specified is to present the initial page)

$action = script_param ("action");
if (!isset ($action))
    $action = SHOW_INITIAL_PAGE;

switch ($action)
{
case SHOW_INITIAL_PAGE:     # present initial page
    display_events ();
    break;
case SOLICIT_EVENT:         # ask for new event information
    solicit_event_info ();
    break;
case ADD_EVENT:             # add new event to database
    add_new_event ();
    display_events ();
    break;
case DISPLAY_SCORES:        # display scores for selected event
    display_scores ();
    break;
case ENTER_SCORES:          # enter new or edited scores
    enter_scores ();
    display_events ();
    break;
default:
    die ("Unknown action code ($action)");
}

html_end ();
?>

The $action variable can take on several values, which we test in the switch statement. In PHP, switch is much like its C counterpart; it's used here to determine which action to take and to call the functions that implement the action. To avoid having to use literal action values, the switch statement refers to symbolic action names that are set up earlier in the script using PHP's define() construct.

Let's examine the functions that handle these actions one at a time. The first one, display_events(), presents a list of allowable events by retrieving rows of the event table from MySQL and displaying them. Each row of the table lists the event ID, date, and event type (test or quiz). The event ID appears in the page as a hyperlink that you can select to edit the scores for that event. Following the event rows, the function adds one more row containing a link that allows a new event to be created:

function display_events () 
{
    print ("Select an event by clicking on its number, or select\n");
    print ("New Event to create a new grade event:<br /><br />\n");
    $query = "SELECT event_id, date, type FROM event ORDER BY event_id";
    $result_id = mysql_query ($query)
        or die ("Cannot execute query");
    print ("<table border=\"1\">\n");

    # Print a row of table column headers

    print ("<tr>\n");
    display_cell ("th", "Event ID");
    display_cell ("th", "Date");
    display_cell ("th", "Type");
    print ("</tr>\n");

    # Present list of existing events.  Associate each event id with a
    # link that will show the scores for the event; use mysql_fetch_array()
    # to fetch each row so that its columns can be referred to by name.

    while ($row = mysql_fetch_array ($result_id))
    {
        print ("<tr>\n");
        $url = sprintf ("%s?action=%s&event_id=%s",
                        script_name (),
                        urlencode (DISPLAY_SCORES),
                        urlencode ($row["event_id"]));
        display_cell ("td",
                    "<a href=\"$url\">"
                        . htmlspecialchars ($row["event_id"])
                        . "</a>",
                    FALSE);
        display_cell ("td", $row["date"]);
        display_cell ("td", $row["type"]);
        print ("</tr>\n");
    }
    # Add one more link for creating a new event

    print ("<tr align=\"center\">\n");
    $url = sprintf ("%s?action=%s",
                    script_name (),
                    urlencode (SOLICIT_EVENT));
    display_cell ("td colspan=\"3\"",
                    "<a href=\"$url\">" . "Create New Event" . "</a>",
                    FALSE);
    print ("</tr>\n");

    print ("</table>\n");
}

The URLs for the hyperlinks that re-invoke score_entry.php are constructed using script_name(), a function that determines the script's own pathname. (It can be found in the sampdb.php file.) script_name() is useful because it allows you to avoid hardwiring the name of the script into the code; if you write the name literally into the script and then rename it, the script breaks.

script_name() is somewhat similar to script_param() in that it accesses PHP global arrays. However, it uses different arrays because the script name is part of the information supplied by the Web server, not as part of the input parameters:

function script_name () 
{
global $HTTP_SERVER_VARS, $PHP_SELF;

    if (isset ($_SERVER["PHP_SELF"]))
        return ($_SERVER["PHP_SELF"]);
    if (isset ($HTTP_SERVER_VARS["PHP_SELF"]))
        return ($HTTP_SERVER_VARS["PHP_SELF"]);
    return ($PHP_SELF);
}

The display_cell() function used by display_events() generates cells in the event table:

# Display a cell of an HTML table.  $tag is the tag name ("th" or "td" 
# for a header or data cell), $value is the value to display, and
# $encode should be true or false, indicating whether or not to perform
# HTML-encoding of the value before displaying it.  $encode is optional,
# and is true by default.

function display_cell ($tag, $value, $encode = TRUE)
{
    if ($value == "")   # is the value empty or unset?
        $value = "&nbsp;";
    else if ($encode)   # perform HTML-encoding if requested
        $value = htmlspecialchars ($value);
    print ("<$tag>$value</$tag>\n");
}

If you select the "Create New Event" link in the table that display_events() presents, score_entry.php is re-invoked with an action of SOLICIT_EVENT. That triggers a call to solicit_event_info(), which displays a form that allows you to enter the date and type for the new event:

function solicit_event_info () 
{
    printf ("<form method=\"POST\" action=\"%s?action=%s\">\n",
                script_name (),
                urlencode (ADD_EVENT));
    print ("Enter information for new grade event:<br /><br />\n");
    print ("Date: ");
    print ("<input type=\"text\" name=\"date\" value=\"\" size=\"10\" />\n");
    print ("<br />\n");
    print ("Type: ");
    print ("<input type=\"radio\" name=\"type\" value=\"T\"");
    print (" checked=\"checked\" />Test\n");
    print ("<input type=\"radio\" name=\"type\" value=\"Q\" />Quiz\n");
    print ("<br /><br />\n");
    print ("<input type=\"submit\" name=\"button\" value=\"Submit\" />\n");
    print ("</form>\n");
}

The form generated by solicit_event_info() contains an edit field for entering the date, a pair of radio buttons for specifying whether the new event is a test or a quiz, and a Submit button. The default event type is 'T' (test). When you fill in this form and submit it, score_entry.php is invoked again, this time with an action value equal to ADD_EVENT. Then the add_new_event() function is called to enter a new row into the event table, which is the first point at which MySQL actually enters into the operation of the script:

function add_new_event () 
{
    $date = script_param ("date");  # get date and event type
    $type = script_param ("type");  # entered by user

    if (empty ($date))  # make sure a date was entered, and in ISO format
        die ("No date specified");
    if (!preg_match ('/^\d+\D\d+\D\d+$/', $date))
        die ("Please enter the date in ISO format (CCYY-MM-DD)");
    if ($type != "T" && $type != "Q")
        die ("Bad event type");

    $date = quote_value ($date);
    $type = quote_value ($type);
    if (!mysql_query ("INSERT INTO event (date,type) VALUES($date,$type)"))
        die ("Could not add event to database");
}

add_new_event() uses the script_param() library routine to access the parameter values that correspond to the date and type fields in the new-event entry form. Then it performs some minimal safety checks:

  • The date should not be empty, and it should have been entered in ISO format. The preg_match() function performs a pattern match for ISO format:

    preg_match ('/^\d+\D\d+\D\d+$/', $date) 
    

    Single quotes are used here to prevent interpretation of the dollar sign and the backslashes as special characters. The test is true if the date consists of three sequences of digits separated by non-digit characters. That's not bullet-proof, but it's easy to add to the script, and it will catch many common errors.

  • The event type must be one of those allowed in the type column of the event table ('T' or 'Q').

If the parameter values look okay, add_new_event() enters a new record into the event table. The query construction code uses quote_value() to make sure the data values are quoted properly for insertion into the query string. After executing the statement, add_new_event() returns to the main part of the script (the switch statement), which displays the event list again so that you can select the new event and begin entering scores for it.

When you select an item from the event list shown by the display_events() function, the score_entry.php script invokes the display_scores() function. Each event link contains an event number encoded as an event_id parameter, so display_scores() gets the parameter value, checks it to make sure it's an integer, and uses it in a query to retrieve a row for each student and any current scores the students may have for the event:

function display_scores () 
{
    # Get event ID number, which must look like an integer
    $event_id = script_param ("event_id");
    if (!preg_match ('/^\d+$/', $event_id))
        die ("Bad event ID");

    # select scores for the given event
    $query = sprintf ("
        SELECT
            student.student_id, student.name, event.date,
            score.score AS score, event.type
        FROM student, event
            LEFT JOIN score ON student.student_id = score.student_id
                    AND event.event_id = score.event_id
        WHERE event.event_id = %s
        ORDER BY student.name
    ", quote_value ($event_id));
    $result_id = mysql_query ($query)
        or die ("Cannot execute query");
    if (mysql_num_rows ($result_id) < 1)
        die ("No information was found for the selected event");

    printf ("<form method=\"POST\" action=\"%s?action=%s&event_id=%s\">\n",
                script_name (),
                urlencode (ENTER_SCORES),
                urlencode ($event_id));

    # print scores as an HTML table

    $row_num = 0;
    while ($row = mysql_fetch_array ($result_id))
    {
        # print event info and table heading preceding the first row
        if ($row_num == 0)
        {
            printf ("Event ID: %s, Event date: %s, Event type: %s\n",
                        htmlspecialchars ($event_id),
                        htmlspecialchars ($row["date"]),
                        htmlspecialchars ($row["type"]));
            print ("<br /><br />\n");
            print ("<table border=\"1\">\n");
            print ("<tr>\n");
            display_cell ("th", "Name");
            display_cell ("th", "Score");
            print "</tr>\n";
        }
        ++$row_num;
        print ("<tr>\n");
        display_cell ("td", $row["name"]);
        $col_val = sprintf ("<input type=\"text\" name=\"score[%s]\"",
                                htmlspecialchars ($row["student_id"]));
        $col_val .= sprintf (" value=\"%s\" size=\"5\" /><br />\n",
                                htmlspecialchars ($row["score"]));
        display_cell ("td", $col_val, FALSE);
        print ("</tr>\n");
    }

    print ("</table>\n");
    print ("<br />\n");
    print ("<input type=\"submit\" name=\"button\" value=\"Submit\" />\n");
    print "</form>\n";
}

The query that display_scores() uses to retrieve score information for the selected event is not just a simple join between tables, because that wouldn't select a row for any student who has no score for the event. In particular, for a new event, the join would select no records, and we'd have an empty entry form. We need to use a LEFT JOIN to force a row to be retrieved for each student, whether or not the student already has a score in the score table. If the student has no score for the given event, the value retrieved by the query will be NULL. (Background for a query similar to the one that display_scores() uses to retrieve score records from MySQL was given in Chapter 3, "MySQL SQL Syntax and Use," in the "Checking for Values Not Present in a Table" section.)

The scores retrieved by the query are placed in the form as input fields having names like score[n], where n is a student_id value. You can enter or edit the scores and then submit the form to have them stored in the database. When your browser sends the form back to the Web server, PHP will convert these fields into elements of an array associated with the name score that can be retrieved as follows:

$score = script_param ("score"); 

Elements of the array will be keyed by student ID, so we can easily associate each student with the corresponding score submitted in the form. The form contents are handled by the enter_scores() function, which looks like the following:

function enter_scores () 
{
    # Get event ID number and array of scores for the event

    $event_id = script_param ("event_id");
    $score = script_param ("score");

    if (!preg_match ('/^\d+$/', $event_id)) # must look like integer
        die ("Bad event ID");

    $invalid_count = 0;
    $blank_count = 0;
    $nonblank_count = 0;
    reset ($score);
    while (list ($student_id, $newscore) = each ($score))
    {
        $newscore = trim ($newscore);
        if (empty ($newscore))
        {
            # if no score is provided for student in the form, delete any
            # score the student may have had in the database previously
            ++$blank_count;
            $query = sprintf ("
                                DELETE FROM score
                                WHERE event_id = %s AND student_id = %s
                            ",
                                quote_value ($event_id),
                                quote_value ($student_id));
        }
        else if (!preg_match ('/^\d+$/', $newscore)) # must look like integer
        {
            ++$nonblank_count;
            $query = sprintf ("
                            REPLACE INTO score (event_id,student_id,score)
                            VALUES(%s,%s,%s)
                            ",
                                quote_value ($event_id),
                                quote_value ($student_id),
                                quote_value ($newscore));
        }
        else
        {
            ++$invalid_count;
            continue;
        }
        if (!mysql_query ($query))
            die ("score entry failed, event_id $event_id,"
                    ."student_id $student_id");
    }
    printf ("Number of scores entered: %d<br />\n", $nonblank_count);
    printf ("Number of scores missing: %d<br />\n", $blank_count);
    printf ("Number of invalid scores: %d<br />\n", $invalid_count);
    print ("<br />\n");
}

The student ID values and scores associated with them are obtained by iterating through the $score array with PHP's each() function. The loop processes each score as follows:

  • If the score is blank after any whitespace is trimmed from its ends, there is nothing to be entered. But just in case there was a score before, the script tries to delete it. (Perhaps we mistakenly entered a score earlier for a student who actually was absent, and now we need to remove it.) If the student had no score, DELETE will find no record to remove, but that's harmless.

  • If the score is not blank, the function performs some rudimentary validation of the value and accepts it if it looks like an integer. Note that integer testing is done using a pattern match rather than PHP's is_int() function. The latter is for testing whether a variable's type is integer, but form values are encoded as strings. is_int() will return FALSE for any string, even if it contains only digit characters. What we need here is a content check to verify the string, so a pattern match serves our purposes better. The following test is TRUE if every character from the beginning to the end of the string $str is a digit:

    preg_match ('/^\d+$/', $str) 
    

    If the score looks okay, we add it to the score table. The query uses REPLACE rather than INSERT because we may be replacing an existing score rather than entering a new one. If the student had no score for the grade event, REPLACE adds a new record, just like INSERT; otherwise, REPLACE replaces the old score with the new one.

That takes care of the score_entry.php script. All score entry and editing can be done from your Web browser now. One obvious shortcoming is that the script provides no security; anyone who can connect to the Web server can edit scores. The script that we'll write later for Historical League member entry editing shows a simple authentication scheme that could be adapted for this script. For more serious security, you'd set up an SSL connection to protect the traffic between your browser and the Web server. But that's beyond the scope of this book.

Some other modifications you could make to the score_entry.php script are as follows:

  • Display information about which scores were bad.

  • Enter the scores within a transaction and roll back the transaction if any bad scores are found. To do this, you must make sure the score table uses a transactional type, such as InnoDB. Then you'd precede the score entry loop with a BEGIN statement and follow it with a COMMIT or ROLLBACK statement, depending on the value of $invalid_count.

If you decide to modify the script to use a transactional approach, it's important to use a non-persistent connection. Should the script die in the middle of the transaction, you'd want the transaction to be rolled back, which is what will happen with a non-persistent connection. PHP will close the connection, and the MySQL server automatically will roll back any transaction in progress if the client exits abnormally. With a persistent connection, PHP will keep the connection open, so it's possible that the incomplete transaction might not be rolled back.

U.S. President Quiz

One of the goals for the Historical League Web site was to use it for presenting an online version of a quiz, similar to some of the quizzes that the League publishes in the children's section of its newsletter, Chronicles of U.S. Past. We created the president table, in fact, so that we could use it as a source of questions for a history-based quiz. Let's do this now, using a script called pres_quiz.php.

The basic idea is to pick a president at random, ask a question about him, and then solicit an answer from the user and see whether or not the answer is correct. The types of questions the script might present could be based on any part of the president table records, but for simplicity, we'll constrain it to asking only where presidents were born. Another simplifying measure is to present the questions in multiple-choice format. That's easier for the user, who only needs to pick from among a set of choices rather than typing in a response. It's also easier for us because we don't have to do any pattern matching to check whatever the user might have typed. We need only a simple comparison of the user's choice and the value that we're looking for.

The pres_quiz.php script must perform two functions. First, when initially invoked, it should generate and display a new question by looking up information from the president table. Second, if the user has submitted a response, the script must check it and provide feedback to indicate whether it was correct. If the response was incorrect, the script should redisplay the same question; otherwise, it should generate and display a new question.

The outline for the script is quite simple. If the user isn't submitting a response, it presents the initial question page; otherwise, it checks the answer:

<?php 
# pres_quiz.php - script to quiz user on presidential birthplaces

include "sampdb.php";

# ... put quiz-handling functions here ...

$title = "U.S. President Quiz";
html_begin ($title, $title);
sampdb_connect ()
    or die ("Sorry, could not connect to database; no quiz available");

$response = script_param ("response");
if (!isset ($response))     # invoked for first time
    present_question ();
else                        # user submitted response to form
    check_response ();

html_end ();
?>

To create the questions, we'll use ORDER BY RAND(), a feature introduced in MySQL 3.23.2. Using the RAND() function, we can select rows at random from the president table. For example, to pick a president name and birthplace randomly, the following query does the job:

SELECT CONCAT(first_name, ' ', last_name) AS name, 
CONCAT(city, ', ', state) AS place
FROM president ORDER BY RAND() LIMIT 1;

The name will be the president about whom we ask the question, and the birthplace will be the correct answer to the question "Where was this president born?" We'll also need to present some incorrect choices, which we can select using a similar query:

SELECT DISTINCT CONCAT(city, ', ', state) AS place 
FROM president ORDER BY RAND();

From the result of this query, we'll select the first four values that differ from the correct response. The reason for using DISTINCT in this query is to avoid the possibility of selecting the same birthplace for the choice list more than once. DISTINCT would be unnecessary if birthplaces were unique, but they are not, as you can discover by issuing the following statement:

mysql> SELECT city, state, COUNT(*) AS count FROM president 
    -> GROUP BY city, state HAVING count > 1;
+-----------+-------+-------+
| city      | state | count |
+-----------+-------+-------+
| Braintree | MA    |     2 |
+-----------+-------+-------+

The function that generates the question and the set of possible responses looks like this:

function present_question () 
{
    # issue query to pick a president and get birthplace
    $query = "SELECT CONCAT(first_name, ' ', last_name) AS name,"
            . " CONCAT(city, ', ', state) AS place"
            . " FROM president ORDER BY RAND() LIMIT 1";
    $result_id = mysql_query ($query)
        or die ("Cannot execute query");
    $row = mysql_fetch_array ($result_id)
        or die ("Cannot fetch result");
    $name = $row["name"];
    $place = $row["place"];
    # Construct the set of birthplace choices to present.
    # Set up the $choices array containing five birthplaces, one
    # of which is the correct response.
    $query = "SELECT DISTINCT CONCAT(city, ', ', state) AS place"
            . " FROM president ORDER BY RAND()";
    $result_id = mysql_query ($query)
        or die ("Cannot execute query");
    $choices[] = $place;    # initialize array with correct choice
    while (count ($choices) < 5 && $row = mysql_fetch_array ($result_id))
    {
        if ($row["place"] == $place)
            continue;
        $choices[] = $row["place"]; # add another choice
    }
    # seed random number generator, randomize choices, then display form
    srand ((float) microtime () * 10000000);
    shuffle ($choices);
    display_form ($name, $place, $choices);
}

present_question() as shown will not work if your version of MySQL precedes 3.23.2, because older versions don't allow functions in the ORDER BY clause. Check the comments in the source code of the pres_quiz.php script for a description of some modifications you can use to work around this limitation.

The display_form() function called by present_question() generates the quiz question using a form that displays the name of the president, a set of radio buttons that lists the possible choices, and a Submit button. This form serves the obvious purpose of presenting quiz information to the user, but it also needs to do something else: It must present the quiz information to the client and arrange that when the user submits a response, the information sent back to the Web server allows the script to check whether the response is correct and redisplay the question if not.

Presenting the quiz question is a matter of displaying the president's name and the possible birthplace choices, which is straightforward enough. Arranging to be able to check the response and possibly redisplay the question is a little trickier. It requires that we have access to the correct answer and also to all the information needed to regenerate the question. One way to do this is to use a set of hidden fields to include all the necessary information in the form. These fields become part of the form and will be returned when the user submits a response, but they are not displayed for the user to see.

We'll call the hidden fields name, place, and choices to represent the president's name, correct birthplace, and the set of possible choices. The choices can be encoded as a single string easily by using implode() to concatenate the values with a special delimiter character in between. (The delimiter allows us to properly break apart the string later with explode() if it becomes necessary to redisplay the question.) The display_form() function takes care of producing the form:

function display_form ($name, $place, $choices) 
{
    printf ("<form method=\"POST\" action=\"%s\">\n", script_name ());
    hidden_field ("name", $name);
    hidden_field ("place", $place);
    hidden_field ("choices", implode ("#", $choices));
    printf ("Where was %s born?<br /><br />\n", htmlspecialchars ($name));
    for ($i = 0; $i < 5; $i++)
    {
        radio_button ("response", $choices[$i], $choices[$i], FALSE);
        print ("<br />\n");
    }
    print ("<br />\n");
    submit_button ("submit", "Submit");
    print ("</form>\n");
}

display_form() uses several helper functions to generate the form fields. The first is hidden_field() that generates the <input> tag for a hidden field:

function hidden_field ($name, $value) 
{
    printf ("<input type=\"%s\" name=\"%s\" value=\"%s\" />\n",
                "hidden",
                htmlspecialchars ($name),
                htmlspecialchars ($value));
}

Because hidden_field() is a general-purpose routine likely to be useful in many scripts, the logical place to put it is in our library file, sampdb.php. Note that it uses htmlspecialchars() to encode both the name and value attributes of the <input> tag in case the $name or $value variables contain special characters, such as quotes.

Two other helper functions, radio_button() and submit_button(), are implemented as follows:

function radio_button ($name, $value, $label, $checked) 
{
    printf ("<input type=\"%s\" name=\"%s\" value=\"%s\"%s />%s\n",
                "radio",
                htmlspecialchars ($name),
                htmlspecialchars ($value),
                ($checked ? " checked=\"checked\"" : ""),
                htmlspecialchars ($label));
}

function submit_button ($name, $value)
{
    printf ("<input type=\"%s\" name=\"%s\" value=\"%s\" />\n",
                "submit",
                htmlspecialchars ($name),
                htmlspecialchars ($value));
}

When the user chooses a birthplace from among the available options and submits the form, the response is returned to the Web server as the value of the response parameter. We can discover the value of response by calling script_param(), which also gives us a way to figure out whether the script is being called for the first time or if the user is submitting a response to a previously displayed form. The parameter will not be set if this is a first-time invocation, so the main body of the script can determine what it should do based on the parameter's presence or absence:

$response = script_param ("response"); 
if (!isset ($response))     # invoked for first time
    present_question ();
else                        # user submitted response to form
    check_response ();

We still need to write the check_response() function that compares the user's response to the correct answer. For this, the values present in the name, place, and choices hidden fields are needed. We encoded the correct answer in the place field of the form, and the user's response will be in the response field, so to check the answer all we need to do is compare the two. Based on the result of the comparison, check_response() provides some feedback and then either generates and displays a new question or redisplays the same question:

function check_response () 
{
    $name = script_param ("name");
    $place = script_param ("place");
    $choices = script_param ("choices");
    $response = script_param ("response");

    # Is the user's response the correct birthplace?

    if ($response == $place)
    {
        print ("That is correct!<br />\n");
        printf ("%s was born in %s.<br />\n",
                htmlspecialchars ($name),
                htmlspecialchars ($place));
        print ("Try the next question:<br /><br />\n");
        present_question();
    }
    else
    {
        printf ("\"%s\" is not correct.  Please try again.<br /><br />\n",
                htmlspecialchars ($response));
        $choices = explode ("#", $choices);
        display_form ($name, $place, $choices);
    }
}

We're done. Add a link for pres_quiz.php to the Historical League home page, and visitors can try out the quiz to test their knowledge.

Hidden Fields Are Not Secure

pres_quiz.php relies on hidden fields as a means of transmitting information that is needed for the next invocation of the script but that the user should not see. That's fine for a script like this, which is intended only for fun. But hidden fields should not be used for any information that the user must not ever be allowed to examine directly, because they are not secure in any sense. To see why not, install pres_quiz.php in the ushl directory of your Web server document tree and request it from your browser. Then use the browser's View Source command to see the raw HTML for the quiz page. There you'll find the contents of the place hidden field that contains the correct answer for the current quiz question, exposed for anyone to see. This means it's very easy to cheat on the quiz. That's no big deal for this particular application, but the example does illustrate that hidden fields are not secure in the least. Do not use them for information that really must be kept secure from the user.

Historical League Online Member Entry Editing

Our final PHP script, edit_member.php, is intended to allow the Historical League members to edit their own directory entries online. Using this script, members will be able to correct or update their membership information whenever they want without having to contact the League office to submit the changes. Providing this capability should help keep the member directory more up to date, and, not incidentally, reduce the workload of the League secretary.

One precaution we need to take is to make sure each entry can be modified only by the member the entry is for or by the League secretary. This means we need some form of security. As a demonstration of a simple form of authentication, we'll use MySQL to store passwords for each member and require that a member supply the correct password to gain access to the editing form that our script presents. The script works as follows:

  • When initially invoked, edit_script.php presents a login form containing fields for the member ID and a password.

  • When the login form is submitted, the script looks in a password table that associates member IDs and passwords. If the password matches, the script looks up the member entry from the member table and displays it for editing.

  • When the edited form is submitted, we update the entry in the database using the contents of the form.

For any of this to work, of course, we'll need to assign passwords. An easy way to do this is to generate them randomly. The following statements set up a table named member_pass and then create a password for each member by generating an MD5 checksum from a random number and using the first eight characters of the result. In a real situation, you might let members pick their own passwords, but this technique provides a quick and easy way to set something up initially:

mysql> CREATE TABLE member_pass ( 
    -> member_id INT UNSIGNED NOT NULL PRIMARY KEY,
    -> password CHAR(8));
mysql> INSERT INTO member_pass (member_id, password)
    -> SELECT member_id, LEFT(MD5(RAND()), 8) AS password FROM member;

The MD5() function is unavailable prior to MySQL 3.23.2. Another way to generate eight-character random values that works in any version of MySQL is as follows:

mysql> INSERT INTO member_pass (member_id, password) 
    -> SELECT member_id, FLOOR(RAND()*99999999) AS password FROM member;

These values are less varied than those based on MD5() because they are composed entirely of digits.

In addition to a password for each person listed in the member table, we'll add a special entry to the member_pass table for member 0, with a password that will serve as the administrative (superuser) password. The League secretary can use this password to gain access to any entry:

mysql> INSERT INTO member_pass (member_id, password) VALUES(0, 'bigshot'); 

Note: Before creating the member_pass table, you might want to remove the samp_browse.pl script from your Web server's script directory. (That script, written in Chapter 7 allows anyone to browse the contents of any table in the sampdb database?including the member_pass table. Thus, it could be used to see any League member's password or the administrative password.)

When the member_pass table has been set up, we're ready to begin building edit_member.php. The framework for the script is as follows:

<?php 
# edit_member.php - Edit Historical League member entries via the Web

include "sampdb.php";

# define action constants
define ("SHOW_INITIAL_PAGE", 0);
define ("DISPLAY_ENTRY", 1);
define ("UPDATE_ENTRY", 2);

# ... put input-handling functions here ...
$title = "U.S. Historical League -- Member Editing Form";
html_begin ($title, $title);

sampdb_connect ()
    or die ("Cannot connect to server");

# determine what action to perform (the default if
# none is specified is to present the initial page)

$action = script_param ("action");
if (!isset ($action))
    $action = SHOW_INITIAL_PAGE;

switch ($action)
{
case SHOW_INITIAL_PAGE:     # present initial page
    display_login_page ();
    break;
case DISPLAY_ENTRY:         # display entry for editing
    display_entry ();
    break;
case UPDATE_ENTRY:          # store updated entry in database
    update_entry ();
    break;
default:
    die ("Unknown action code ($action)");
}

html_end ();
?>

The initial page is presented by display_login_page(), which generates a form that asks for a member ID and password:

function display_login_page () 
{
    printf ("<form method=\"POST\" action=\"%s?action=%s\">\n",
                script_name (),
                urlencode (DISPLAY_ENTRY));
    print ("Enter your membership ID number and password,\n");
    print ("then select Submit.\n<br /><br />\n");
    print ("<table>\n");
    print ("<tr>");
    print ("<td>Member ID</td><td>");
    text_field ("member_id", "", 10);
    print ("</td></tr>");
    print ("<tr>");
    print ("<td>Password</td><td>");
    password_field ("password", "", 10);
    print ("</td></tr>");
    print ("</table>\n");
    submit_button ("button", "Submit");
    print "</form>\n";
}

The captions and the value entry fields in the form are presented within the framework of an HTML table to make them line up nicely. With only two fields, this is a minor touch, but it's a generally useful technique, especially when you create forms with captions of very dissimilar lengths, because it eliminates vertical raggedness. Lining up the form components can make the form easier for the user to read and understand.

display_login_form() uses two more helper functions that can be found in the sampdb.php library file. text_field() presents an editable text input field:

function text_field ($name, $value, $size) 
{
    printf ("<input type=\"%s\" name=\"%s\" value=\"%s\" size=\"%s\" />\n",
                "text",
                htmlspecialchars ($name),
                htmlspecialchars ($value),
                htmlspecialchars ($size));
}

password_field() is the same, except that the type attribute is password (so I won't show it).

When the user enters a member ID and password and submits the form, the action parameter will be equal to DISPLAY_ENTRY, and the switch statement in the next invocation of edit_member.php will invoke the display_entry() function to check the password and display the member entry if the password matches:

function display_entry () 
{
    # Get script parameters; trim whitespace from ID, but
    # not from password, because password must match exactly.

    $member_id = trim (script_param ("member_id"));
    $password = script_param ("password");

    if (empty ($member_id))
        die ("No member ID was specified");
    if (!preg_match ('/^\d+$/', $member_id))    # must look like integer
        die ("Invalid member ID was specified (must be an integer)");
    if (empty ($password))
        die ("No password was specified");
    if (check_pass ($member_id, $password)) # regular member
        $admin = FALSE;
    else if (check_pass (0, $password))     # administrator
        $admin = TRUE;
    else
        die ("Invalid password");
    $query = sprintf ("
                    SELECT
                        last_name, first_name, suffix, email, street, city,
                        state, zip, phone, interests, member_id, expiration
                    FROM member WHERE member_id = %s
                    ORDER BY last_name
                ", quote_value ($member_id));
    $result_id = mysql_query ($query);
    if (!$result_id)
        die ("Cannot execute query");
    if (mysql_num_rows ($result_id) == 0)
        die ("No user with member_id = $member_id was found");
    if (mysql_num_rows ($result_id) > 1)
        die ("More than one user with member_id = $member_id was found");

    printf ("<form method=\"POST\" action=\"%s?action=%s\">\n",
                script_name (),
                urlencode (UPDATE_ENTRY));

    # Add member ID and password as hidden values so that next invocation
    # of script can tell which record the form corresponds to and so that
    # the user need not re-enter the password.

    hidden_field ("member_id", $member_id);
    hidden_field ("password", $password);

    # Read results of query and format for editing

    $row = mysql_fetch_array ($result_id);

    print ("<table>\n");

    # Display member ID as static text

    display_column ("Member ID", $row, "member_id", FALSE);

    # $admin is true if the user provided the administrative password,
    # false otherwise. Administrative users can edit the expiration
    # date, regular users cannot.

    display_column ("Expiration", $row, "expiration", $admin);

    # Display other values as editable text

    display_column ("Last name", $row, "last_name");
    display_column ("First name", $row, "first_name");
    display_column ("Suffix", $row, "suffix");
    display_column ("Email", $row, "email");
    display_column ("Street", $row, "street");
    display_column ("City", $row, "city");
    display_column ("State", $row, "state");
    display_column ("Zip", $row, "zip");
    display_column ("Phone", $row, "phone");
    display_column ("Interests", $row, "interests");

    print ("</table>\n");

    submit_button ("button", "Submit");
    print "</form>\n";

}

The first thing display_entry() does is to verify the password. If the password supplied by the user matches the password stored in the member_pass table for the given member ID, or if it matches the administrative password (that is, the password for the special member ID 0), edit_member.php displays the entry in a form so that its contents can be edited. The password-checking function check_pass() runs a simple query to yank a record from the member_pass table and compare its password column value to the password supplied by the user in the login form:

function check_pass ($id, $pass) 
{
    $query = sprintf ("SELECT password FROM member_pass WHERE member_id = %s",
                        quote_value ($id));
    $result_id = mysql_query ($query);
    if (!$result_id)
        die ("Error reading password table");
    if ($row = mysql_fetch_array ($result_id))
        return ($row["password"] == $pass); # TRUE if password matches
    return (FALSE);                         # no record found
}

Assuming that the password matches, display_entry() looks up the record from the member table corresponding to the given member ID and then goes on to generate an editing form initialized with the values from the record. Most of the fields are presented as editable text fields so that the user can change them, but there are two exceptions. First, the member_id value is displayed as static text. This is the key value that uniquely identifies the record, so it should not be changed. Second, the expiration date is not something that we want League members to be able to change. (They'd be able to push the date farther into the future, in effect renewing their memberships without paying the yearly dues.) On the other hand, if the administrative password was given at login time, the script does present the expiration date in an editable field. Assuming the League secretary knows this password, the secretary can then update the expiration date for members who renew their memberships.

Display of field labels and values is handled by the display_column() function. Its arguments are the label to display next to the field value, the array that contains the record to be edited, the name of the column within the record that contains the field value, and a boolean value that indicates whether to present the value in editable or static form. The last value is optional, with a default value of TRUE:

function display_column ($label, $row, $col_name, $editable = TRUE) 
{
    print ("<tr>\n");
    printf ("<td>%s</td>\n", htmlspecialchars ($label));
    print ("<td>");
    if ($editable)  # display as edit field
        text_field ("row[$col_name]", $row[$col_name], 80);
    else            # display as read-only text
        print (htmlspecialchars ($row[$col_name]));
    print ("</td>\n");
    print ("</tr>\n");
}

For editable values, display_column() generates text fields using names that have the format row[col_name]. That way, when the user submits the form, PHP will place all the field values into an array variable with elements keyed by column name. This makes it easy to extract the form contents and to associate each field value with its corresponding member table column when we update the record in the database. For example, by fetching the array into a $row variable, we can access the telephone number as $row["phone"].

The display_entry() function also embeds the member_id and password values as hidden fields in the form so that they will carry over to the next invocation of edit_script.php when the user submits the edited entry. The ID allows the script to determine which member table record to update, and the password allows it to verify that the user logged in before. (Notice that this simple authentication method involves passing the password back and forth in clear text, which isn't generally such a great idea. But the Historical League is not a high-security organization, so this method suffices for our purposes. Were you performing operations like financial transactions, you'd want to use a more secure form of authentication.)

The function that updates the membership entry when the form is submitted looks like this:

function update_entry () 
{
    # Get script parameters; trim whitespace from ID, but
    # not from password, because it must match exactly, or
    # from row, because it is an array.
    $member_id = trim (script_param ("member_id"));
    $password = script_param ("password");
    $row = script_param ("row");

    $member_id = trim ($member_id);
    if (empty ($member_id))
        die ("No member ID was specified");
    if (!preg_match ('/^\d+$/', $member_id))    # must look like integer
        die ("Invalid member ID was specified (must be an integer)");
    if (!check_pass ($member_id, $password) && !check_pass (0, $password))
        die ("Invalid password");

    # We'll need a result set to use for assessing nullability of
    # member table columns.  The following query provides one without
    # selecting any rows.  Use the query result to construct an
    # associative array that maps column names to true/false values
    # indicating whether columns allow NULL values.

    $result_id = mysql_query ("SELECT * FROM member WHERE 1 = 0");
    if (!$result_id)
        die ("Cannot query member table");
    $nullable = array ();
    for ($i = 0; $i < mysql_num_fields ($result_id); $i++)
    {
        $fld = mysql_fetch_field ($result_id, $i);
        $nullable[$fld->name] = !$fld->not_null;    # TRUE if nullable
    }
    mysql_free_result ($result_id);

    # Iterate through each field in the form, using the values to
    # construct the UPDATE statement.

    $query = "UPDATE member ";
    $delim = "SET";
    reset ($row);
    while (list ($col_name, $val) = each ($row))
    {
        $query .= "$delim $col_name=";
        $delim = ",";
        # if a form value is empty, update the corresponding column value
        # with NULL if the column is nullable.  This prevents trying to
        # put an empty string into the expiration date column when it
        # should be NULL, for example.
        $val = trim ($val);
        if (empty ($val))
        {
            if ($nullable[$col_name])
                $query .= "NULL";   # enter NULL
            else
                $query .= "\''";   # enter empty string
        }
        else
            $query .= quote_value ($val);
    }
    $query .= sprintf (" WHERE member_id = %s", quote_value ($member_id));
    if (mysql_query ($query))
        print ("Member entry was updated successfully.\n");
    else
        print ("Member entry was not updated.\n");
}

First we re-verify the password to make sure someone isn't attempting to hoax us by sending a faked form, and then the entry is updated. The update requires some care because if a field in the form is blank, it may need to be entered as NULL rather than as an empty string. The expiration column is an example of this. Suppose the League secretary logs in with the administrative password (so that the expiration field is editable) and clears the field to indicate "lifetime membership." This should correspond to a NULL membership expiration date in the database. If the script inserts an empty string into the e