7.11 ProjectCGIMySQLDBI
7.11 Project”CGI/MySQL/DBINow 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 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
 |
EAN: 2147483647
Pages: 136