CGI Script example: database insert and backend reporting

 

We saw this one before…

 

Now, we’ll analyze it line by line.  Keep in mind, you DO NOT put the line numbers in your perl cgi script.  They are only included so that we can reference what each line of code is doing.

 

1      #!/usr/bin/perl –w

2

3      use strict;

4      use CGI::Carp qw(fatalsToBrowser);

5      use CGI qw(:standard);

6      use DBI; # we’ll talk about this in more detail later

 

7      my $mode = param(‘mode’);

8      my $url = url;

 

9      print header,

10     start_html;

11     if($mode eq ‘’) # two open quotes means ‘blank’ or ‘null’ or ‘empty’ in perl

12     {

13       print h2(‘Please enter your name’),

14       start_form(-action=>$url,

15                  -method=>’post’),

16       hidden(-name=>’mode’,

17              -value=>’process_form’,

18              -override=>1),

19       textfield(‘name’),

20       end_form;

21     }

22     elsif($mode eq ‘process_form’)

23     {

24       my $dbh = DBI->connect("dbi:$db_driver:database=$db_name;host=localhost;", $db_username, $db_password, {RaiseError=>'1'}); 

25       my $sth = $dbh->prepare(‘insert into names (name) values (?)’);

26       $sth->execute(param(‘name’));

27       print redirect($url . ‘?mode=read_submitted_names’);

28       $dbh->disconnect;

29     }

30     elsif($mode eq ‘read_submitted_names’)

31     {

32       my $dbh = DBI->connect("dbi:$db_driver:database=$db_name;host=localhost;", $db_username, $db_password, {RaiseError=>'1'}); 

33       my $sth = $dbh->prepare(‘select * from names’);

34       $sth->execute;

35       while(my $row_href = $sth->fetchrow_hashref)

36       {

37         print $row_href->{‘name’} . br;

38       }

39         $dbh->disconnect;

40     }
41     else

42     {

43       print h2(‘Bad mode specified.  Exiting…’);

44     }

45     print end_html;

 

Line 1: Initialize the perl interpreter to run our program, and pass it the warn flag so we get good errors for debugging.

Line 2: Always put an empty line after calling perl.

Line 3: Enable more useful error messages for debugging.

Line 4: Load the module that turns cryptic 500 Internal Server Errors into useful error messages we can read in a web browser.

Line 5: Load the CGI module and import it’s functions, which saves us tons of time and effort in writing cgi scripts.

Line 6: Load the DBI module, so that we can connect to a database and interact with it.

Line 7: Define a mode scalar string variable so that we know which screen of our cgi script to execute code from.

Line 8: Define a scalar string variable that tells us the URL (Uniform Resource Locator, or “address”) of the currently executing cgi script.  This is useful to reference in forms where we “post back” to our original script.

Line 9: This prints out the required HTTP header our script must output to work properly.

Line 10: A CGI.pm function to print out the beginning tags of an HTML document.

Line 11: An if statement test to determine if no mode has been passed to the program.

Line 12: The opening bracket to the above if statement.

Line 13: Start a print statement, and call the CGI.pm function to print out an HTML <h2> tag, passing the argument of “Please enter your name” to be displayed.

Line 14 and 15: Continue the print statement, calling the CGI.pm function start_form with two arguments passed using hash notation, which denote the url the form will be sent to, and the method of transmission of the form, which in this case is post.

Line 16, 17, and 18: Continue the print statement, calling the CGI.pm function hidden, which generates an html hidden input tag.  We pass this function three arguments using hash notation, one for the name of the tag, one for the value, and one to tell the function NOT to use the value of this input from the previous form that sent us to this current page.

Line 19: Continue the print statement, calling the CGI.pm function textfield, which produces an html text input tag, passing it the argument of ‘name’, using standard argument syntax.

Line 20: Continue the print statement, calling the CGI.pm function named end_form, which prints out the necessary html to end a form.  At the end of this line, we type a semicolon, which ends the print statement originally started on line 13.

Line 21: Close the if statement we started on line 11.

Line 22: Start an elsif statement, testing to see if the mode variable contains the value of ‘process_form’.

Line 23: The opening bracket for the above elsif statement.

Line 24: Open a database connection and return a database handle object.  See the chapter on ‘The Perl DBI Module And Database Manipulation’ for full information on the input parameters and how this works in detail, and also to learn what an object is and how an object’s “methods” are similar but different than regular functions.

Line 25: Prepare a statement handle by calling the database handle object’s prepare method, and passing as an argument some SQL to insert a record into the specified database table.  See the chapter ‘Basic Relational Database Concepts, Design, And The SQL Language’ for more information on SQL queries.

Line 26: Execute the prepared SQL statement handle by passing the CGI.pm name/value pair value of ‘name’ to the statement handle objects placeholder, using the execute method.

Line 27: Use CGI.pm’s redirect function to redirect the user to the ‘read_submitted_names’ mode. 

28: Disconnect from the database.

29: end the elsif statement started on line 22.

30: Begin an elsif statement testing to see if the value of the mode scalar string variable is ‘read_submitted_names’.

31: The open bracket to indicate beginning the elsif statement from the line above.

32: Connect to the database and return a database handle object.

33. Prepare a statement handle by calling the database handle object’s prepare method, and passing as an argument some SQL to select a record from the specified database table.

34. Execute the above query by calling the statement handle objects execute method.

35. begin a while loop, creating a hash reference called $row_href which contains a list of name/value pairs of the columns in the queried database table by calling the statement handle’s fetchrow_hashref method.

36.  An open bracket to denote the beginning of the above while loop

37. print out the value of the database table’s name column, using the assigned hash reference’s ‘name’ name/value pair.

 

Note: A hash reference is very similar to a hash, except that instead of accessing it’s values as $my_hash{‘keyname’}, you access it’s values as$my_hash_reference->{‘keyname’}.  The little dash greater than sign is the only difference.  This is my preferred way of accessing data from a database, because you can reference database table column names by name, and not by the order they exist in the table, which would be the case if you used an array for this purpose.  For some reason, the perl database library doesn’t let you use a regular hash for this purpose.  Don’t ask me why.

 

38. the bracket to denote the ending of the while loop which grabs all relevant records (as defined in the sql statement on line 33) from the database table.

39. Disconnect from the database by calling the database handle’s disconnect method.

40. end the elsif statement we begun on line 30.

41. Begin an else statement, which means that none of the above if or elsif statements evaluated to true.  What this means in reality is that a valid ‘mode’ was not passed, but there was some (invalid) value in the ‘mode’ variable.

42. The opening bracket for the else statement.

43. Calling the print function to print out the CGI.pm function that renders the html tag <h2>, passing it the argument of “Bad mode specified.  Exiting…”, which prints this text out to the browser wrapped inside an <h2> tag.

44.  The closing bracket for the else statement.

45.  Print the HTML to end an html document.