A Simple Web-Based ApplicationTo-Do List Maintenance

only for RuBoard - do not distribute or recompile

A Simple Web-Based Application—To-Do List Maintenance

You now know many of the capabilities that the DBI and CGI.pm modules provide you with, but they have been presented in the previous sections relatively separate from each other to allow better focus on each one. In this section, we’re going to switch gears and integrate several techniques by developing a little application that illustrates a number of concepts common to Web-based database programs. To keep it small, the application’s scope is limited: It just maintains a list of to-do items. You might be thinking, “What good is that? Why wouldn’t I just scribble my to-do list on a scrap of paper?” Perhaps you’re right. However, although I believe the application can be useful, I’m actually less interested in raw utility than in certain other aspects of it that make for suitable discussion here:

It’s simple and easy to understand.
It demonstrates many of the concepts we’ll use later on and provides further exposure to CGI.pm and DBI facilities. These include such things as form generation, form content processing, database record creation and editing over the Web, and searching based on user input.
With a little modification, the script can be used as the basis for a variety of related applications.

Furthermore, the application can be developed in a stepwise and modular fashion so that we can add code in sections. Here’s the sequence of events we’ll follow in writing it:

Create a todo table in the webdb database in which to store the items.
Develop an item entry form.
Provide a display of existing items.
Add the ability to edit items.
Add the ability to search for items.

Create the To-Do Table

The first thing we need is a table in which to store the to-do items we want to keep track of. I’ll assume each item comprises the following bits of information:

The date and time when the item was created. This will be a DATETIME value rather than a TIMESTAMP . A TIMESTAMP column does have the advantage that you can have it set automatically to the current date and time when you create a new record (which is convenient for record entry). However, the value also automatically changes whenever the record is modified, at which point it no longer would reflect record creation time. DATETIME columns must be set explicitly when records are created, but the values remain stable when other columns in the record are updated. That makes DATETIME a more suitable choice for this application.
The item content (a description of what’s to be done). This is a simple VARCHAR(255) string column. If you wanted to allow longer items, one of MySQL’s TEXT column types would be more appropriate.
The status of the item: open (still unfinished ), closed (finished), in progress (started, but not yet finished). An ENUM (enumeration) is a good column type here because values are selected from a small fixed set of choices.

Here’s the statement that defines the table, which is called todo rather than to-do. For older versions of MySQL, the hyphen (-) is not a legal character in table names . You can use it in newer versions, but you have to specify the table name within back quotes every time you refer to it ( `to-do` ), and that’s too much bother.

CREATE TABLE todo 
(
    t       DATETIME NOT NULL, 
    content VARCHAR(255) NOT NULL, 
    status  ENUM('open','closed','in-progress') NOT NULL, 
    INDEX (t) 
)

Type the preceding statement into the mysql program to create the table (remember to add a semicolon at the end) and you’ll be ready to start developing the application that uses it.

Design an Item Entry Form

The initial version of the to-do application, todo1.pl, will be quite rudimentary, allowing only entry of new to-do items. (We’ll worry about displaying and editing existing items later). To accomplish the item entry task, the application must do two things:

Display a form containing a box allowing the text of the item to be entered, and a button for submitting the form.
Add the new item to the todo table when the Submit button is selected. In this case, after entering the item, another blank entry form should be displayed in case the user wants to add another item.

Because there are two actions to be performed by the application, we should address the question of whether to use one script or two. The usual argument in favor of using multiple scripts is based on being able to split up an application into discrete pieces that don’t relate much to each other and that each are simpler than a combined script would be. For example, we might use one script to display the entry form, and another to process submitted items and insert them into the database. However, that analysis doesn’t really apply to the to-do application, because the second script also must display an entry form (after adding the new item). Given that both scripts would need to display the form anyway, there’s nothing to be gained by decomposing this particular application into multiple scripts.

Having made the decision to use a single script, we’re faced with the problem of how to know whether an item was submitted, so that we can tell whether to add a record to the todo table before displaying the entry form. Fortunately, it’s pretty easy to figure that out. When a CGI script runs, it receives from Apache a number of values in the form of environment variables . CGI.pm allows scripts to extract this information, some of which indicates the values of the elements (parameters) present in any form that might have been submitted. When the script is invoked for the first time, no form parameters will have been set; this tells the script it need only display the entry form. If the user fills in the form and selects the Submit button, the browser sends back the form information to the Web server, which invokes the script again to process the information. This time the CGI environment contains information indicating that the form parameters have been set; the script can detect this and take appropriate action to create a new record in the todo table before displaying a new blank form.

Detection of form input is based on the CGI.pm param() function, which returns the value of a form parameter given the parameter’s name. Therefore, we need to design the form and assign names to its parameters. Here’s what it looks like:

<form method="post" action="todo1.pl"> 
To-do item:<br> 
<textarea name="content" rows=3 cols=80></textarea><br> 
<input type="submit" name="choice" value="Submit"> 
</form>

The action attribute of the <form> tag names the script that generates the form, todo1.pl , because we want that same script to process the form when the user selects Submit and sends it back to the Web server. todo1.pl determines what to do based on the values present in the form. Its elements contain two parameters: content holds the text of the to-do item, and choice provides a Submit button. choice is the relevant parameter to use for figuring out whether to create a new item. It will be empty the first time a remote user invokes the script and will have the value " Submit " when the user submits the form.

Before we go any further, allow me to digress a bit to discuss a couple of conventions I use in this book for writing applications that contain forms.

Ascertaining the User’s Choice

In most cases, I use choice as the parameter that indicates what the user wants to do. For the form just shown, that parameter name is used for a single form element (the Submit button). If a form contains several buttons , however, we can name them all choice and determine which one the user selected by inspecting the choice parameter’s value (which happens to be the text displayed in the selected button). For example, we’ll add a Search button later, so at that point the form will contain two submit elements:

<input type="submit" name="choice" value="Submit"> 
<input type="submit" name="choice" value="Search">

The same name is used for both buttons, but we can tell which one a user selects by checking the value of the choice parameter, which will be either " Submit " or " Search ". (This contrasts with the approach of giving each button a different name. In that case, you have to check as many parameters as you have buttons. I prefer to extract the value of a single parameter.)

To simplify name-testing comparisons while allowing for whatever letter-case convention is used for buttons—perhaps you prefer all caps—it’s easiest to extract the choice parameter value and convert it to a specific case immediately. The form-processing applications in this book convert the value to lowercase, so you’ll see a statement like the following one in many of our programs:

$choice = lc (param ("choice"));

The lc() function also has the effect of converting undef to the empty string. That means we don’t have to test $choice with defined() ; we can just go ahead and use it without worrying about whether we’ll trigger warnings in the error log about use of an uninitialized variable.

Acting on the User’s Choice

After extracting the choice value, we need to examine it and dispatch to the proper actions that implement the user’s intent. For the logic of our to-do application, its dispatch code could be structured as follows :

if ($choice eq "submit")            # form was submitted 
{
    # ... insert new item 
} 
# ... display entry form

However, looking ahead to the fact that we’ll be adding other choices in upcoming versions of the application, I’ll use a different dispatch format that is more extensible and also includes a clause to handle “can’t happen” choices. The revised code looks like this:

if ($choice eq "")                  # initial script invocation 
{
    # ... display entry form 
} 
elsif ($choice eq "submit")         # form was submitted 
{
    # ... insert new item 
    # ... display entry form 
} 
else                                # hmm, we don't recognize the value! 
{
    # ... print warning message 
}

This dispatch code tests for the initial script invocation first, based on the fact that the choice parameter is empty when the user first requests the script. The middle clause tests for the " submit " choice indicating that the user selected the Submit button. The final clause handles the “can’t happen” case.

The structure of the code is such that the middle part can be extended easily by adding elsif clauses to handle other choices as we revise the application. The final clause comes in handy as a safety net for situations such as when you modify a form to add a button for an additional choice but forget to add code to the dispatcher to handle the button. The “can’t happen” clause prints an error message that notifies you about the omission the first time you try to use the new button.

Here’s the main body of the first version of our application:

#! /usr/bin/perl -w 
# todo1.pl - initial version of to-do application 

use strict; 
use lib qw(/usr/local/apache/lib/perl); 
use CGI qw(:standard); 
use WebDB; 

print header (), 
    start_html (-title => "To-Do List", -bgcolor => "white"), 
    h2("To-Do List"); 

# Dispatch to proper action based on user selection 

my $choice = lc (param ("choice")); # get choice, lowercased 

if ($choice eq "")                  # initial script invocation 
{
    display_entry_form (); 
} 
elsif ($choice eq "submit") 
{
    insert_item (param ("content")); 
    display_entry_form (); 
} 
else 
{
    print p ("Logic error, unknown choice: $choice"); 
} 

print end_html ();

The script puts out the page headers, title, and initial heading. Then it determines what to do based on the user’s choice and finishes the page. The call to start_html() shows something we haven’t used before: a named-argument list, where arguments are specified in -name => value format. CGI.pm supports this because several of its methods take so many optional arguments. Using named arguments enables you to specify just the ones you want, in any order. For the start_html() call shown, the title argument specifies the text of the page title and the bgcolor argument sets the background color of the page.

After writing out the initial part of the page, todo1.pl examines the choice parameter to see what to do. When the script is invoked for the first time, choice is empty. If the user fills in the form and selects the Submit button, choice will be " submit ", so the script knows it should add a new todo table record. The insert_item() function takes care of this. The only information passed to the function is the item content, because the item’s creation time (“now”) and its initial status (“open”) can be provided automatically. insert_item() does take some trouble to avoid entering items with empty content, because the user might select the Submit button without entering any text or after entering just whitespace. The function also doesn’t bother connecting to MySQL unless there is really something to insert:

sub insert_item 
{
my $content = shift; 
my $dbh; 
    $content =~ s/^\s+//;   # strip leading whitespace 
    $content =~ s/\s+$//;   # strip trailing whitespace 
    if ($content ne "")     # if content is non-empty, add to table 
    {
        $dbh = WebDB::connect (); 
        $dbh->do (qq{
            INSERT INTO todo SET t = NOW(), status = 'open', content = ? 
                }, undef, $content); 
        $dbh->disconnect (); 
    } 
}

Whether or not a new item is created, todo1.pl displays an entry form, using the following function:

sub display_entry_form 
{
    print start_form (-action => url ()), 
        "To-do item:", br (), 
        textarea (-name => "content", 
                    -value => "", 
                    -override => 1, 
                    -rows => 3, 
                    -columns => 80), 
        br (), 
        submit (-name => "choice", -value => "Submit"), 
        end_form (); 
}

Put todo1.pl in the cgi-bin directory and make it executable. (Web scripts must be executable, just like command-line scripts.) Then invoke todo1.pl from your browser to see how it works:

http://www.snake.net/cgi-bin/todo1.pl

There are a couple of subtle points about the form-generation code in the display_entry_form() routine. The first concerns the action argument to the start_form() that determines what script to invoke when the form is submitted. The url() function evaluates to the script’s own URL, which is how you get a script to invoke itself without hardwiring its name into the code. (Hardwiring the name is a bad idea; if you rename your script, it breaks.)

The second point involves the textarea() call. CGI.pm is designed to let you carry form values from one invocation of a script to the next, so that you can more easily set up a form with its previous values without initializing each element explicitly. For your item entry form, that means when you submit an item, the text of the item would carry over and be displayed in the next page automatically. That’s extremely useful for many applications. (If you’re presenting a product registration form that a user submits after filling in only some of the required fields, for example, you can present in response another form already filled with the supplied values along with a reminder to fill in the other fields.) However, for our to-do manager, that behavior is undesirable. Having the text of an item carry over to the next page means you’d have to delete the text before entering the text of the next item. To defeat this carryover behavior for the text box, we pass the override parameter to the textarea() function that generates it. Setting override to 1 tells CGI.pm to use the value of the value argument (that is, the empty string) no matter what was in the previous form. You can see the effect of this for yourself by removing the override argument from the textarea() call and then entering a few items. Notice that to enter the second and subsequent items, you now have to clear out the value that carried over into the form from the previous one. Restore override and you’ll see that this no longer occurs.

Display Existing Items

At this point, we have a simple application that enables us to enter new records into our todo table over the Web. But how do we see our items? The item entry page doesn’t show them to us. One way to see the records is to run a query using mysql :

mysql> SELECT * FROM todo; 
+---------------------+------------------------------------------+--------+ 
 t                    content                                   status  
+---------------------+------------------------------------------+--------+ 
 2001-03-12 19:54:38  Finish ch. 6 before the editor kills me   open    
 2001-03-13 09:23:09  Fix ch. 5 code examples                   open    
 2001-03-15 15:27:55  Revise ch. 10 outline                     open    
+---------------------+------------------------------------------+--------+

Given that we are entering records using a browser, however, it would be nice to see them that way, too. The next version of the script, todo2.pl , will do just that. It will take information from the Web and put it in the database, and it will take information from the database and send it to the Web. The modification means the script will make greater use of MySQL because it will not only create new records, it also will retrieve existing ones. This requires a change to the application’s connect and disconnect behavior. Currently, it connects to the server only if there is a record to insert, establishing the connection immediately before the INSERT statement and disconnecting immediately after. After we add item-display capability, that’s no longer appropriate. The script will always need a connection because it will check for existing records every time it runs. Consequently, we’ll move the connect and disconnect code out of the insert_item() function and into the main logic of the application and pass the database handle $dbh to each function that needs it. We’ll also add calls to display the current items. With these changes, the body of todo2.pl is as follows (leaving out the initial lines that haven’t changed):

print header (), 
    start_html (-title => "To-Do List", -bgcolor => "white"), 
    h2("To-Do List"); 

my $dbh = WebDB::connect (); 

# Dispatch to proper action based on user selection 

my $choice = lc (param ("choice")); # get choice, lowercased 

if ($choice eq "")                  # initial script invocation 
{
    display_entry_form (); 
    display_current_items ($dbh); 
} 
elsif ($choice eq "submit") 
{
    insert_item ($dbh, param ("content")); 
    display_entry_form (); 
    display_current_items ($dbh); 
} 
else 
{
    print p ("Logic error, unknown choice: $choice"); 
} 

$dbh->disconnect (); 

print end_html ();

To display the existing to-do items, display_current_items() runs a SELECT query and processes the results. The code shown here displays the most recent items first (ORDER BY t DESC sorts items in time-descending order):

sub display_current_items 
{
my $dbh = shift; 
my ($sth, $stmt, $count); 

    print hr (), strong ("Current items:"); 
    $stmt = qq { SELECT * FROM todo ORDER BY t DESC }; 
    $sth = $dbh->prepare ($stmt); 
    $sth->execute (); 
    $count = 0; 
    while (my $row = $sth->fetchrow_hashref ()) 
    {
        print br (), br (), 
                "$row->{t} $row->{status}", 
                br (), 
                $row->{content}; 
        ++$count; 
    } 
    $sth->finish (); 
    print p ("No items were found") if $count == 0; 
}

These modifications take care of displaying the current items. Here’s an example of what they look like when displayed in the browser window:

2001-03-12 19:54:38 open 
Finish ch. 6 before the editor kills me 

2001-03-13 09:23:09 open 
Fix ch. 5 code examples 

2001-03-15 15:27:55 open 
Revise ch. 10 outline

You can run todo2.pl to see how the page display now includes existing items. Unfortunately, after we enter a lot of items, the page becomes longer and takes more time to load into our browser with each additional item, so we need some way to restrict how many items to show at once. One simple way to throttle the displayed-item list is to add a LIMIT clause to the SELECT query. LIMIT is a MySQL-specific option that enables you to tell the server to return only a portion of the selected rows. Suppose we want to display no more than five items. This can be accomplished by changing the SELECT query from this:

SELECT * FROM todo ORDER BY t DESC

to this:

SELECT * FROM todo ORDER BY t DESC LIMIT 5

LIMIT 5 limits the query to the first 5 rows of the result. If there aren’t yet that many items in the table, MySQL returns them all. (The number 5 is arbitrary; the point of using a small value is that you won’t have to add many entries to see the effect of the LIMIT clause on item display.) Call the script todo3.pl , and then run it a few times to see how it works.

You may be thinking that LIMIT isn’t really a very good solution to the problem of restricting the item display to a manageable length, and you’re right. It’s a simple strategy, but also simple-minded: after we add more than five items, we don’t see the first items again! Other possible solutions, such as displaying a section of the available items, with “next” and “previous” links to other sections of the list, are discussed further in Chapter 7, “Performing Searches.”

Add Item-Editing Capabilities

After using the application for a while, presumably we’ll actually complete some of the tasks that we’ve entered into our to-do list, and then we’ll want to mark them as done or delete them. (You would mark them as done if you want to keep a record of what you’ve accomplished, or just delete them if you want to keep the list short.) Also, the ability to edit the text of existing items would be handy: You might decide an item’s content needs revision, or you might want to allow other people to use the application to add annotations or other comments to your items. Let’s write the next version of the application, todo4.pl , to include the ability to edit items. How does this change the application, compared to the previous version?

We’ll need an unambiguous way to select an item for editing. However, none of the columns in the table is suitable for that purpose. The t column that indicates the item creation time comes close, but not close enough. (Suppose you and your spouse both use the application; it’s conceivable that you’d both add an item within the same second.) Therefore, we’ll need a column in the table with values that are guaranteed to be unique so that each item has a distinct ID.
In the current-item display part of the page, we’ll need to add links to each item so that they can be selected for editing or deletion.
We’ll need not just an entry form, but an item-editing form. The entry form is unsuitable for editing purposes because it allows only the item text to be specified, and the form comes up blank. An editing form has different requirements. It must come up with the item’s current text displayed, and it needs an additional element allowing the item status to be displayed and modified.

One way of providing unique ID values for the todo table is to use a sequence number column. Most databases have a way of generating sequences; MySQL’s method is the integer AUTO_INCREMENT column. We can even add the column to the table without destroying its existing contents using MySQL’s ALTERTABLE statement. Enter the following query in mysql to add an id column to the todo table:

mysql>

ALTER TABLE todo ADD id INT NOT NULL AUTO_INCREMENT PRIMARY KEY;

The way an AUTO_INCREMENT column works is that if you enter a new record without specifying a value for the column (or if you enter a value of NULL explicitly), MySQL automatically sets the column to the next sequence number. (“Next” means the largest value that has already been used, plus one.) That works well to assign sequence numbers to new items, but how do we assign them to rows that already are in the todo table? As it happens, we don’t have to. When MySQL adds an AUTO_INCREMENT column to a table, it automatically numbers any existing rows in the table. You can see this for yourself. After issuing the ALTER TABLE statement, try the following query and you’ll see the ID values that were added:

mysql>

SELECT * FROM todo;

Now we should consider whether adding the id column requires any changes to the queries currently used by the to-do manager:

Does it change the INSERT statement that adds new items? No, because if we don’t specify a value for the id column in the statement, MySQL automatically sets its value to the next sequence number. That’s exactly what we want to happen.
Does it change the SELECT statement used to retrieve items for the current item display? The answer would be yes if we had named columns explicitly in that statement. But we used SELECT * to select all columns, and we used fetchrow_hashref() to fetch rows into a hash structure. After changing the table, that structure will contain a new id member automatically. We don’t have to fetch it explicitly.

What does change is the current-item display code, because we need to add Edit and Delete links for each item. The display_current_items() function looks like this after we modify it to add the links:

sub display_current_items 
{
my $dbh = shift; 
my ($sth, $stmt, $count); 

    print hr (), strong ("Current items:"); 
    $stmt = qq { SELECT * FROM todo ORDER BY t DESC LIMIT 5 }; 
    $sth = $dbh->prepare ($stmt); 
    $sth->execute (); 
    $count = 0; 
    while (my $row = $sth->fetchrow_hashref ()) 
    {
        my $edit_url = sprintf ("%s?choice=edit;id=%d", url (), $row->{id}); 
        my $delete_url = sprintf ("%s?choice=delete;id=%d", url (), 
                                  $row->{id}); 
        print br (), br (), 
                "$row->{t} $row->{status}", 
                " [", a ({-href => $edit_url}, "Edit"), "]", 
                " [", a ({-href => $delete_url}, "Delete"), "]", 
                br (), "\n", 
                "$row->{content}\n"; 
        ++$count; 
    } 
    $sth->finish (); 
    print p ("No items were found") if $count == 0; 
}

We construct the URLs using url() to get the script’s base URL, and then add choice and id parameters that specify what action to perform and to identify which item the user selected. We pass these URLs to the CGI.pm a() function to generate anchors for hyperlinks that the user can click. When one of these Edit or Delete links is selected, the browser sends back to the Web server a URL something like the following:

http://www.snake.net/cgi-bin/todo4.pl?choice=edit;id=14 
http://www.snake.net/cgi-bin/todo4.pl?choice=delete;id=3

Note how we’re extending the use of the choice parameter. Up until now, we’ve used it only for buttons, recovering its value using param() . But that function also provides access to parameters passed in URLs (that is, access to information present as name=value pairs after the ‘?’ in the URL). That means we don’t need to know or care whether a choice value arrives as part of a form or in the URL—a very nice feature indeed. param() tells us the user’s choice whether the user clicks a button or selects an Edit or Delete link. If the user selects a link, param() also tells us the id value that indicates which item to operate on.

Now you need to write the code that handles the Edit and Delete choices and that modifies the todo table accordingly . Let’s cover deleting an item first, because that’s easier than editing an item. The clause in the dispatch logic that handles the choice is pretty simple:

elsif ($choice eq "delete") 
{
    if (!delete_item ($dbh, param ("id"))) 
    {
        print p ("No record with id ". param ("id"). " was found"); 
    } 
    display_entry_form (); 
    display_current_items ($dbh); 
}

The clause calls delete_item() to clobber the item with the appropriate ID number, and then displays a fresh entry form and list of current items. delete_item() looks like this:

sub delete_item 
{
my ($dbh, $id) = @_; 
    return ($dbh->do (qq{ DELETE FROM todo WHERE id = ? }, 
                undef, 
                $id)); 
}

Item editing is a bit more involved because it’s not a single-step operation. First, we display an edit form initialized with the item’s current values, along with an Update button for submitting the modified item. (In addition, we’ll provide a Cancel button in case the user decides not to modify the item after all.) Second, we process the updated item when the Update button is selected. The dispatch logic needs several new clauses to handle these choices:

elsif ($choice eq "edit") 
{
    if (my $row = select_item ($dbh, param ("id"))) 
    {
        display_edit_form ($row); 
    } 
    else 
    {
        print p ("No record with id ", param ("id"), " was found"); 
        display_current_items ($dbh); 
    } 
} 
elsif ($choice eq "update") 
{
    update_item ($dbh, param ("id"), param ("content"), param ("status")); 
    display_entry_form (); 
    display_current_items ($dbh); 
} 
elsif ($choice eq "cancel") 
{
    display_entry_form (); 
    display_current_items ($dbh); 
}

select_item() runs a simple SELECT query to retrieve the item with the given id value and returns the row as a hash reference.

sub select_item 
{
my ($dbh, $id) = @_; 
my ($sth, $row); 

    $sth = $dbh->prepare (qq{ SELECT * FROM todo WHERE id = ? }); 
    $sth->execute ($id); 
    $row = $sth->fetchrow_hashref (); 
    $sth->finish (); 
    return ($row); 
}

The row returned by select_item() is passed to display_edit_form() so that the form can be initialized to the item’s current state:

sub display_edit_form 
{
my $row = shift; 

    print strong ("Item Editing Form"), 
        start_form (-action => url ()), 
        hidden (-name => "id", $row->{id}), 
        "To-do item $row->{id}:", br (), 
        textarea (-name => "content", 
                    -value => $row->{content}, 
                    -override => 1, 
                    -rows => 5, 
                    -columns => 100), 
        br (), 
        radio_group (-name => "status", 
                    -values => ["open", "closed", "in-progress"], 
                    -default => $row->{status}), 
        br (), 
        submit (-name => "choice", -value => "Update"), 
        submit (-name => "choice", -value => "Cancel"), 
        end_form (); 
}

The editing form differs from the entry form in the following ways:

It has a different title (a cosmetic difference).
The item ID is displayed in the form for the user to see, but that value is just static text and won’t be returned when the user selects the Update button. We’ll need the value when the form is submitted so that we can tell which item to update, so the form also includes the id value as a hidden form value. This instance of the value is not displayed to the user, but will be transmitted back to the Web server when the edited item is submitted.
The content field is initialized to $row->{content} . That makes the form come up with the item’s current text displayed and ready for modification.
A set of radio buttons is provided for setting the item’s status, and the current status value is used to set the default button. (The possible values for the status buttons are hardwired into the code based on your knowledge of the table structure. In Chapter 6, “Automating the Form-Handling Process,” we’ll see how to generate a radio button list automatically by examining a table’s structure directly to pull out the legal enumeration values.)
The choice button for submitting the modified item is labeled " Update " rather than " Submit " to prevent the application from confusing it with the Submit button in the item entry form.
The form includes a Cancel button to give the user the option of returning to the item entry form without making any changes.

If a modified item is submitted, we call update_item() . This function needs four parameters: the database handle, the item ID (to identify which item to update), and the new content and status of the item (the item creation date remains unchanged):

sub update_item 
{
my ($dbh, $id, $content, $status) = @_; 

    $content =~ s/^\s+//;   # strip whitespace 
    $content =~ s/\s+$//; 
    $dbh->do (qq{ UPDATE todo SET content = ?, status = ? WHERE id = ? }, 
                undef, 
                $content, $status, $id); 
}

Add Item-Searching Capabilities

We’re almost done building the to-do list manager. All that remains is to add searching capability, so that items can be selected by content based on a word or phrase. There are different ways to enable users to perform searches, but we’ll use a simple strategy: Add a Search button to the item entry form. To enter new items, you can use the form as before. To search for items, enter a word or phrase and select Search rather than Submit. The only modification we need to make to the display_entry_form() function is to change this line:

submit (-name => "choice", -value => "Submit"),

To this:

submit (-name => "choice", -value => "Submit"), 
submit (-name => "choice", -value => "Search"),

To handle the new button, add another clause to the dispatch code. The search string arrives as the value of the content parameter, so we pass that to the search function:

elsif ($choice eq "search") 
{
    display_entry_form (); 
    display_search_hits ($dbh, param ("content")); 
}

The function for displaying search hits will need to display individual items, just like the function that displays the current items. Therefore, we’ll move the item-display code from display_current_items() into a function display_item() and call the latter function from within display_search_hits() as well. display_search_hits() searches for and displays matching items as follows:

sub display_search_hits 
{
my ($dbh, $str) = @_; 
my ($sth, $stmt, $count); 

    print hr (), p ("Search Results"); 
    $str =~ s/^\s+//;   # strip whitespace 
    $str =~ s/\s+$//; 
    if ($str eq "")     # cancel search if there's nothing to search for 
    {
        print p ("No search term was entered"); 
        return; 
    } 
    print hr (), strong ("Items matching \"$str\":"); 
    $stmt = qq { SELECT * FROM todo WHERE content LIKE ? ORDER BY t DESC }; 
    $sth = $dbh->prepare ($stmt); 
    $sth->execute ("%$str%"); # search for wildcard form of string 
    $count = 0; 
    while (my $row = $sth->fetchrow_hashref ()) 
    {
        display_item ($row); 
        ++$count; 
    } 
    $sth->finish (); 
    print p ("No matching items were found") if $count == 0; 
}

The SELECT query uses a WHERE clause based on LIKE rather than on = to perform a pattern match rather than a literal match. Note the use of " %$str% " as the search string, rather than $str . If we searched just for $str , we’d find only records where the content value is exactly that string. By using the SQL “match anything” wildcard character ‘%’ on both ends of $str , we’ll find items that contain the string anywhere in the content value—that’s the more useful thing to do here.

The helper function display_item() looks like this:

sub display_item 
{
my $row = shift; 
my ($edit_url, $delete_url); 

    $edit_url = sprintf ("%s?choice=edit;id=%d", url (), $row->{id}); 
    $delete_url = sprintf ("%s?choice=delete;id=%d", url (), $row->{id}); 
    print br (), br (), 
            "$row->{t} $row->{status}", 
            " [", a ({-href => $edit_url}, "Edit"), "]", 
            " [", a ({-href => $delete_url}, "Delete"), "]", 
            br (), "\n", 
            "$row->{content}\n"; 
}

Install this script as todo5.pl in the cgi-bin directory to try it out.

Shortcomings of the To-Do Application

The to-do application we’ve just built performs several functions now. You can enter new items, see existing ones, edit or delete items, and search for items that match a string. That need not stop us from critiquing the application, however. Some of the ways it could be improved are described here:

The application doesn’t perform much verification of field input. It makes sure items are not blank, but there’s no check that the item is within the 255 character limit of the content field in the todo table. Nor does it complain if you remove all the text when you modify an item. (Take a look at the update_item() function; how could the update operation be made better?)
Searches are very rudimentary. It doesn’t allow searching for results that fall within a given date range or that have a particular status. For example, you can’t search for items that were entered within the last month that now are marked “done.”
The item display format could be better. For example, dates are displayed to full resolution down to the second. It’s perhaps not very likely you really care about the seconds part of the item creation time. The script could chop off the seconds, either using DATE_FORMAT() in the SELECT query or by chopping the end of already retrieved values.
There is no security. Anyone who can connect to your Web server can see or change items in your list. That’s okay if you want to allow other people access to your list, but my guess is that it’s unlikely you want the whole world to use it.
It’s useful for managing only one person’s to-do list. A more general application might provide a front end that allows users to sign in, and then chooses the appropriate list to edit.

You can probably think of other ways the application might be improved or extended to be more useful. If so, that’s good. It’s usually a worthwhile exercise to consider ways to make an application better. Naturally, you want to give some thought to this during the design stage before you spend a lot of time building the thing, but there’s certainly no rule you can’t keep an eye out for ways to improve the design later.

Adapting the To-Do Application for Other Uses

The to-do list maintenance application implementation involves a number of concepts that come up in several related contexts. With a little modification, you could use the application as the basis for any of the following types of programs:

Note-taking application. This actually is simpler than a to-do list manager; you need to record only the text of items, not manage status values.
Scheduler. Add a field to the forms allowing the date to be specified or edited, modify the script accordingly, and the application becomes a rudimentary scheduling manager.
Guest book. For this type of application, the entry display component can be made optional (depending on whether you want to display previous entries), and you’d remove the entry editing and searching functions
Customer feedback help desk application. Here you’d remove the entry display, editing, and searching functions. Alternatively, you could modify the form so that those functions become accessible if an administrator signs in to use the application. If the entry form were modified to include a field for an email address, the application could allow the administrator to send back replies to people submitting questions. The item-editing function might allow email responses to be generated, perhaps saving the response in the table along with the original question.

Some of the applications just described require the ability to do things that we haven’t covered yet, such as date verification, email generation, user authentication, and session support. We’ll get to these topics in later chapters.

only for RuBoard - do not distribute or recompile