SQL and CGI with Perl and DBI - Perl DBI CGI = Fun!
(Page 5 of 6 )

Perl and DBI are enjoyable, but now it is time to take it to another level of fun. Let’s put our new skill of Perl and DBI together with the topic of the last chapter: CGI.

We will develop a CGI script that will interface to themusicians_dbdatabase. It will be a dynamic CGI program that will first present the user with a form, and when the user submits data by clicking a button, the program will then report the response back to the user. The user will be able to make a choice to see one of two responses: either a musician’s phone number or the instruments they play.

Let’s first have a look at the code. When examined closely, the code follows this general flow of execution:

if (param()) {
    if (param('Show phone number')) {
        # query database and show the musicians phone number
    } elsif (param('Show instruments')) {
        # query the database and show the instruments played by the musician
    }
} else {
    # query the database and build the initial form with all the musicians names
}

Here is the code in all its glory. We will look at the specific pieces in detail as we talk about them:

#!/usr/bin/perl -w
# musicians.pl

use strict;
use CGI ':standard';
use DBI;

if (param()) {
    # we have parameters, go grab the musicians
    # name
    my $musician = param('musician') || '';

    if (param('Show phone number')) {
        # the user wants to see the musician's phone number
        # print first part of HTML
        print
            header(),
            start_html("Phone Number for $musician"),
            h1("Phone Number for $musician");

        # query the database and get the phone number
        my $dbh = DBI->connect("DBI:mysql:musicians_db", "musicfan", 
                             "CrimsonKing");
        my $sth = $dbh->prepare("SELECT phone FROM musicians
                            WHERE name = ?")
                     or die "prepare failed: " . $dbh->errstr();

        $sth->execute($musician) or die "execute failed: " . $sth->errstr();

        my($phone);

        ($phone) = $sth->fetchrow();

        # print number and end HTML
        print
            "Call $musician at $phone.",
            end_html;
} elsif (param('Show instruments')) {
        # the user wants to see the instruments the musician
        # plays, start the HTML
        print
            header(),
            start_html("Instruments played by $musician"),
            h1("Instruments played by $musician"),
            "$musician plays:",
            '<ul>';

        # query the database with a table join and retrieve the
        # instruments played by musician
        my $dbh = DBI->connect("DBI:mysql:musicians_db", "musicfan",
                          "CrimsonKing");
        my $sth = $dbh->prepare("SELECT instrument
             FROM musicians, what_they_play, instruments
             WHERE musicians.name = ? AND
                   musicians.player_id = what_they_play.player_id AND
                   what_they_play.inst_id = instruments.inst_id")
              or die "prepare failed: " . $dbh->errstr(); 

        $sth->execute($musician) or die "execute failed: " . $sth->errstr();

   my($instrument);

   # print all the instruments in a bullet list
   while (($instrument) = $sth->fetchrow()) {
       print "<li>$instrument</li>";
   }

   # finish the HTML
   print
       '</ul>',
       end_html; 
}

} else {
    # no data was posted, so print the initial form to the user
    # allowing to select the musician and whether they want
    # to see the phone number or the instruments
    print
        header(),
        start_html('My Favorite Musicians'),
        h1('Select a Musician'),
        start_form(),
        '<select name-"musician">';

    # grab all the musician's names out of the database
    my $dbh = DBI->connect("DBI:mysql:musicians_db", "musicfan", "CrimsonKing");
    my $sth = $dbh->prepare("SELECT name FROM musicians")
                 or die "prepare failed: " . $dbh->errstr();

    $sth->execute() or die "execute failed: " . $sth->errstr();

    my($name);

    # loop through each row of data, printing it as an option
    # in the select widget
    while (($name) = $sth->fetchrow()) {
        print qq{<option value="$name">$name</option>};
    }

    # finish the select widget, print the submit buttons
    # and end the HTML
    print
        '</select>',
        br(),
        submit('Show phone number'),
        submit('Show instruments'),
        end_form(),
        end_html();
}

When we run the program the first time (http://localhost/cgi-bin/musicians.pl), we see this initial page:

This form allows us to select a musician from the drop-down list. Notice that the menu contains all the musicians that we have inserted into our table—in fact, it is built by reading from the database. Then there are two buttons—one that indicates if we click on it we will see the selected musician’s phone number. The other button, when clicked, will show the instruments played by the selected musician. This page is built with theelsepart of the program starting with

} else {
    # no data was posted, so print the initial form to the user
    # allowing them to select the musician and whether they want
    # to see the phone number or the instruments
    print
        header(),
        start_html('My Favorite Musicians'),
        h1('Select a Musician'),
        start_form(),
        '<select name="musician">';

First, the header is printed, followed by the start of the HTML for the page. Within the form we see the creation of a <select> widget—this is a drop-down menu that allows us to select one of the provided options. Then we see

  # grab all the musicians names out of the database
  my $dbh = DBI->connect("DBI:mysql:musicians_db", "musicfan", "CrimsonKing");
  my $sth = $dbh->prepare("SELECT name FROM musicians")
               or die "prepare failed: " . $dbh->errstr();

  $sth->execute() or die "execute failed: " . $sth->errstr();

This code connects to the database and prepares and executes the query to retrieve all the musician names. The code that displays the names is next.

  my($name);

  # loop through each row of data, printing it as an option
  # in the select widget
  while (($name) = $sth->fetchrow()) {
      print qq{<option value="$name">$name</option>};
  }

This code loops through all the rows of output (the musician names) and prints them in an <option> widget, adding them to the drop-down menu so that they can be selected. This is followed by

   # finish the select widget, print the submit buttons
   # and end the HTML
   print
       '</select>',
       br(),
       submit('Show phone number'),
       submit('Show instruments'),
       end_form(),
       end_html();
}

The<select>widget is closed, two submit buttons are printed, and the page is finished up. Since it has been a while since we talked to Geddy Lee, let’s select his name from the drop-down menu and click the button “Show phone number”. The following screen is produced:

Let’s look at the code that built this page. It starts with

if (param('Show phone number')) {
        # the user wants to see the musician's phone number
        # print first part of HTML
        print
            header(),
            start_html("Phone Number for $musician"),
            h1("Phone Number for $musician");

Theifdetermines that the user wants to see the musician’s phone number. The HTML is started for the page and then we see the code to access the database:

  # query the database and get the phone number
  my $dbh = DBI->connect("DBI:mysql:musicians_db", "musicfan",
                       "CrimsonKing");
  my $sth = $dbh->prepare("SELECT phone FROM musicians
                               WHERE name = ?")
               or die "prepare failed: " . $dbh->errstr();

  $sth->execute($musician) or die "execute failed: " . $sth->errstr();

This is familiar by now—we connect and query the database using the placeholder for the musician’s name. This returns the phone number for the musician, so we need to fetch the row, read the data into$phone, and print it to the browser followed by the end of the HTML:

  my($phone);

  ($phone) = $sth->fetchrow();
  # print number and end HTML
  print
      "Call $musician at $phone.",
      end_html;

After calling Geddy Lee, we may want to see the instruments played by Thom Yorke, so we can go back to the initial page where we can select Thom from the drop-down menu and click “Show instruments”. When we do so we see this page:

This page is built with this code:

} elsif (param('Show instruments')) {
        # the user wants to see the instruments the musician
        # plays, start the HTML
        print
            header(),
            start_html("Instruments played by $musician"),
            h1("Instruments played by $musician"),
            "$musician plays:",
            '<ul>';

We see that the user wants to show the musician’s instruments, so we start the HTML, print some text, and then start an unordered list (a bullet list). This is followed by the code to query the database:

  # query the database with a table join and retrieve the
  # instruments played by musician
  my $dbh = DBI->connect("DBI:mysql:musicians_db", "musicfan",
                       "CrimsonKing");
  my $sth = $dbh->prepare("SELECT instrument
                   FROM musicians, what_they_play, instruments
                   WHERE musicians.name = ? AND
                         musicians.player_id = what_they_play.player_id AND
                       what_they_play.inst_id = instruments.inst_id")
             or die "prepare failed: " . $dbh->errstr();

  $sth->execute($musician) or die "execute failed: " . $sth->errstr();

Using the table join that we have seen a few times in this chapter, we find all the instruments that the musician plays. Then we see the code that prints the instruments:

  my($instrument);

  # print all the instruments in a bullet list
  while (($instrument) = $sth->fetchrow()) {
      print "<li>$instrument</li>";
  }

Notice that each instrument is printed within an<li>tag, which makes it into a bullet item in the list. Finally we see the end of the unordered list and the end of the HTML:

   # finish the HTML
   print
       '</ul>',
       end_html;
}

That was fun! And using Perl, CGI, andDBIit was easy as well. Such is the power and practicality of Perl revealed.

Next: What We Didn’t Talk About >>
 

More Perl Articles
More By Apress Publishing

Comments On: SQL and CGI with Perl and DBI

· This article is an excerpt from the book "Beginning Perl," published by Apress. We...   >>> Post your comment now!

Buy this book...

This article is excerpted from chapter 15 of the book Beginning Perl (Apress; ISBN: 159059391X). Check it out today at your favorite bookstore. Buy this book now.