Friday, December 25, 2009

7.11 Project�CGI/MySQL/DBI



[ Team LiB ]










7.11 Project�CGI/MySQL/DBI


Now we have all the necessary tools to do what we came here to do: hook DBI into CGI programs. We'll create a CGI program that displays the current contents of the age_information table like that created in Chapter 5. It also presents the user with a form to fill out, and if the user submits the form, the data submitted is added to the database table.


The program is in the file /var/www/cgi-bin/age.cgi. The entire contents can be found at either http://localhost/mysql/age.cgi or www.opensourcewebbook.com/mysql/age.cgi.


This program generates a page that has the look and feel of our web site, so it builds a bunch of HTML code. We put this code into two functions, top_html() and bottom_html(), to build the HTML for the top of the page and the bottom of the page, respectively. The HTML for the top of the page is quite involved because it includes the <head> information (such as metainformation) and the links along the left rail.


The first thing seen in age.cgi is the subroutine top_html(), which is one big here document. This is followed by the code for bottom_html(), which is one small here document.


This program is a bit more complicated than the other CGI examples we've shown, and as a result, more things can go wrong. If they do, the script needs to do more things to clean up. It needs a subroutine to do that:



#### 
# 
# handle_error() - a subroutine we can use to handle any error 
#       conditions 
# 
#### 
sub handle_error { 
    my $msg = shift ||´ ´; 
    my $dbh = shift ||´ ´; 
    my $sth = shift ||´ ´; 

    print <<EOHTML; 
Content-type: text/html 

<head> 
<title>Age Information Error</title> 
</head> 
<body bgcolor="#ffffff"> 
There was an error: <b>$msg</b> 
</body> 
</html> 
EOHTML 

    # finish the state handle and disconnect, if necessary 
    $sth->finish()     if $sth; 
    $dbh->disconnect() if $dbh; 
    exit 0; 
}

The subroutine handle_error() is defined at the top of this program. It takes up to three arguments: $msg, a message is printed to the browser; $dbh, a database handle; and $sth, a statement handle. These values are shifted from @_into my() variables. After printing HTML telling the user what went wrong, the subroutine finishes the statement handle, if there is one, and then disconnects from the database, if it needs to. This means that this subroutine can be called with a statement handle and/or a database handle, if it is appropriate. It then exits the CGI program gracefully, so nothing else happens.


A subroutine to process the form data is then defined:



#### 
# 
# process_form_data() - subroutine to process the form data: 
#    we get the posted data, check to make sure we have received 
#    what we need, do some sanity checking on the data, then insert 
#    the data into the table 
# 
#### 
sub process_form_data { 
    # get the argument passed in, the database handle 
    my $dbh = shift; 

    # get the posted data 
    my $lname = param(´lname´) ||´ ´; 
    my $fname = param(´fname´) ||´ ´; 
    my $age   = param(´age´)   || 0; 

    # check to be sure we have all the fields 
    unless ($lname and $fname and $age) { 
       handle_error(´You need to enter last name, first name and age!´, 
                   $dbh); 
    } 

    # check the length of the data 
    if (length($lname) > 20 or length($fname) > 20 or length($age) > 3) { 
        handle_error(´The length of your data is too long!´, $dbh); 
    } 

    # error out if the age is not numeric (contains a character which is 
    # not a digit 
    if ($age =~ /\D/) { 
        handle_error(´Your age is not numeric!´,$dbh); 
    } 

    # ok, now insert data 
    my $sth = $dbh->prepare(´INSERT INTO age_information 
                       (lastname, firstname, age) VALUES (?,?,?)´) 
           or handle_error("Can't prepare SQL: " . $dbh->errstr(), $dbh); 

    $sth->execute($lname, $fname, $age) 
           or handle_error("Can't execute SQL: " . $dbh->errstr(), 
                            $dbh, $sth); 
}

The subroutine process_form_data takes one argument, the opened database handle $dbh. The posted data is then grabbed and stored into three variables: $lname, $fname, and $age. Then, three very important checks are made. First, the program checks to see that it has received the proper number of parameters. Also, for good style (security is always good style), it checks to see that none of the data exceeds the length that can be stored in the database. This is necessary because anyone can send this program posted data that exceeds 20 characters of text�especially critical if our MySQL data type is TEXT or BLOB. It then checks to make sure that the age passed in is numeric�if not, it is not an age. If any of these checks fail, call handle_error() to handle the error.


But you might say, "Hey, didn't we limit the amount of text the user can enter into the form by setting the text widget's maxsize to 20 characters?" Yes, but this program can easily be called directly by not using this form, or it would be simple to create our own form that would allow us to enter more than 20 characters. Once the data has been checked, it can be inserted into the database.


And now for the main code:



#### 
# 
# herein begins the main code 
# 
#### 

# connect to the MySQL server 
my $dbh = DBI->connect(´DBI:mysql:people´, ´apache´, ´LampIsCool´) 
        or handle_error("Can't connect..." . DBI->errstr()); 
# if param() returns true, we have parameters, so process them 
if (param()) { 
    process_form_data($dbh); 
} 

# here we query the db for the data that is to be 
# displayed in a table 
my $sth = $dbh->prepare(´SELECT lastname, firstname, 
                        age FROM age_information´) 
        or handle_error("Can't prepare SQL" . $dbh->errstr(), $dbh); 

$sth->execute() 
        or handle_error("Can't execute SQL" . $sth->errstr(), $dbh, $sth);

After the definition of these subroutines, the script connects as usual, but now if there is an error, handle_error() is called, not die().



The checks we make in this program are only moderately important for this application. If we failed to make these checks, DBI would do the right thing for us, given the database. Our table was defined so that the first and last names were both 20 characters in length, and the age was an integer. If we received a first or last name that exceeded 20 characters, DBI would store only the first 20 in the database, and if we received an age that was not numeric, the value 0 would be used. We check because we would like to tell the user that they have entered incorrect or missing data. We also check because it is a good habit to get into.



The code if (param) checks to see if any posted data has been received. If so, the script calls process_form_data() to gather the posted data and add it to the database.



# now, create HTML for the table and the form 
# first, use CGI methods and the defined subroutines 
# to build HTML 
print 
    header(), 
    top_html(),   # this is the subroutine defined above 
    <<EOHTML; 
<h2>Current Name/Age Information</h2> 
<table border="1"> 
<tr><th>Last Name</th><th>First Name</th><th>Age</th></tr> 
EOHTML

The script then prints the header and start of the HTML, including the start of the table that displays the information in the MySQL table.



# and print the records in the table 
my($ln,$fn,$age); 
while (($ln,$fn,$age) = $sth->fetchrow()) { 
    print "<tr><td>$ln</td><td>$fn</td><td>$age</td></tr>\n"; 
}

Next, the script grabs all the information from the database, looping through the result of the SELECT query and printing each record as a row in the table.



# print the end of the table, then the form using a 
# here document 
print 
    <<EOHTML; 
</table> 
<hr> 
<h2>Enter New Name/Age Information</h2> 
If you enter your name and age, you will be added to the database 
and will be displayed above. 
<form action="/cgi-bin/age.cgi" method="post"> 
<table border="0"> 
  <tr> 
    <td>Last name:</td> 
    <td><input type="text" name="lname" maxsize="20" value=""></td> 
  </tr> 
  <tr> 
    <td>First name:</td> 
    <td><input type="text" name="fname" maxsize="20" value=""></td> 
  </tr> 
  <tr> 
    <td>Age:</td> 
    <td><input type="text" name="age" maxsize="20" value=""></td> 
  </tr> 
  <tr> 
    <td><input type="submit" value="Submit Data"></td> 
    <td><input type="reset" value="Reset Form"></td> 
  </tr> 
</table> 
</form> 
EOHTML

The next step is to print the form that collects information from the user to be added to the MySQL table if the client fills it out and clicks the submit button.



print 
    bottom_html(); # this is the subroutine defined above 

# clean up 
$sth->finish(); 
$dbh->disconnect();

And finally, the bottom HTML is printed, and the program cleans up by finishing the state handler and disconnecting the database handle. To execute this code, load one of these URLs into your browser: http://localhost/cgi-bin/age.cgi or www.opensourcewebbook.com/cgi-bin/age.cgi. You should see Figure 7.14.



Figure 7.14. Age information, before adding a new entry





Now, add the following information:



Last name:  Kedzierski 
First name: John 
Age:        23

Enter this data and click the submit button to produce the output shown in Figure 7.15.



Figure 7.15. Age information, after adding a new entry






This program uses both forms of printing HTML to the browser: The first part of the program uses CGI.pm modules such as start_html() and h1(); the latter part uses a short here document. This is fine�programs can use both styles. The style chosen for any application is a matter of suitability, programming style, and the specific application�TMTOWTDI.







    [ Team LiB ]



    Posted by at

    No comments:

    Post a Comment

    Subscribe to: Post Comments (Atom)