SQL and CGI with Perl and DBI

In this conclusion to a four-part series on Perl and DBI, you will learn about SQL queries and DBI, using Perl and DBI with CGI, and more. This article is excerpted from chapter 15 of the book Beginning Perl (Apress; ISBN: 159059391X).

Executing an SQL Query with DBI

The last example, connect.pl, demonstrated how we can connect to the database. Now it is time to talk about how to execute arbitrary SQL queries.7 We will first look at a program that will connect to the database musicians_db and display all the rows in the musicians table. It is called showmusicians.pl :

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

use strict;
use DBI;

my $dbh = DBI->connect("DBI:mysql:musicians_db", "musicfan", "CrimsonKing");

die "connect failed: " . DBI->errstr() unless $dbh;

# prepare the query to get the data out
# of the musicians table
my $sth = $dbh->prepare("SELECT player_id,name,phone FROM musicians")
             or die "prepare failed: " . $dbh->errstr();

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

my($player_id, $name, $phone);

# loop through each row of data, printing
it
while (($player_id, $name, $phone) = $sth->fetchrow()) {
    print "$player_id : $name : $phonen";
}

$sth->finish();

$dbh->disconnect();

This program connects as before, prepares and executes an SQL query, and then loops through the result of the query. Here is the code that prepares the query:

# prepare the query to get the data out
# of the musicians table
my $sth = $dbh->prepare("SELECT player_id,name,phone FROM musicians")
             or die "prepare failed: " . $dbh->errstr();

The database handler, $dbh , executes the prepare() method. This method’s job is to take its argument, an SQL query, and compile it and prepare it to execute. The query in this case is
"SELECT player_id,name,phone FROM musicians" , which will select those three fields from the musicians table. If the prepare() method succeeds, it returns back an object, known as the state handler, that is assigned to $sth . If the prepare() method fails, it returns back false, and if so, we die() printing $dbh->errstr() , the reason for the failure.

If all is well at this point, we then execute the query.

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

The execute() method executes the query, storing the result into the $sth object. If execute() fails, we die() , and explain why by executing $sth->errstr() .

The $sth object has the result of the query stored within it, so we have to retrieve that information. The fetchrow() method does this for us.

# loop through each row of data, printing it while (($player_id, $name, $phone) = $sth->fetchrow()) {
    print "$player_id : $name : $phonen";
}

The fetchrow() method returns the next row of information returned by the query. This code takes that row and copies it memberwise into three variables. Since our query asked for the player_id , name , and phone from the musicians table, we take those three pieces of infor mation and store them in $player_id , $name , and $phone , respectively. Those variables are then printed.

After we are done with the state handler, it is good practice to finish it with this code:

$sth->finish();

Executing this code produces the following:

$ perl showmusicians.pl
1 : Roger Waters : 555-1212
2 : Geddy Lee : 555-2323
3 : Marshall Mathers III : 555-3434
4 : Thom Yorke : 555-4545 
5 : Lenny Kravitz : 555-5656
6 : Mike Diamond : 555-6767
$
  

 

{mospagebreak title=A More Complex Example}

Remember the complex query we did previously in this chapter when we tried to find out the instruments played by Lenny Kravitz? It was a three-step process: first, we found his player_id from the musicians table. Then we used that player_id to read the inst_id s from the what_they_play table. For each of those inst_id s, we read the instrument name from the instruments table. Here is how we might do this in Perl and DBI :

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

use strict;
use DBI;

my($who, $player_id, $inst_id);

print "Enter name of musician and I will show you his/her instruments: ";
chomp($who = <STDIN>);

my $dbh = DBI->connect("DBI:mysql:musicians_db", "musicfan", "CrimsonKing");

die "connect failed: " . DBI->errstr() unless $dbh;

# first, grab the musicians player_id
my $sth = $dbh->prepare("SELECT player_id FROM musicians WHERE name = ‘$who’")
             or die "prepare failed: " . $dbh->errstr();

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

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

die "player_id not found" unless defined $player_id;

# given the player_id, grab their inst_ids from what_they_play
$sth = $dbh->prepare("SELECT inst_id FROM what_they_play
                             WHERE player_id = $player_id")
             or die "prepare failed: " . $dbh->errstr();

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

# foreach inst_id, grab the instrument name from th e
# instruments table and print it
while (($inst_id) = $sth->fetchrow()) {
    my $sth = $dbh->prepare("SELECT instrument FROM instruments
                                     WHERE inst_id = $inst_id")
             or die "prepare failed: " . $dbh->errstr();

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

    my($instrument) = $sth->fetchrow();
    print "    $instrumentn";

    $sth->finish();
}

$sth->finish();

$dbh->disconnect();

Let’s look at each step of this process. After connecting to the database as we have been doing in the previous examples and asking for the user to enter the name of a musician, we construct and execute a query to obtain that musician’s player_id .

# first, grab the musicians player_id
my $sth = $dbh->prepare("SELECT player_id FROM musicians WHERE name = ‘$who’")
             or die "prepare failed: " . $dbh->errstr();

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

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

die "player_id not found" unless defined $player_id;

Notice how the SQL query is constructed using the variable $who . The outer double quotes of the query string are needed to take the value of $who . The SQL query needs to quote the name since it is a string, so wrapped around $who within the query are single quotes.

The query is then executed. This should return only one row (it could return zero rows if the musician is not found in the table) so we only have to call fetchrow() once—we take the return value of that method, a list of one value, and assign it to the assignable list ($player_id) . The result is that $player_id will be the player_id of the musician that the user entered at standard input or undef if the musician was not found. The program die() s if $player_id is not defined.

Then we use $player_id and construct a query asking for the inst_id s:

# given the player_id, grab their inst_ids from what_they_play
$sth = $dbh->prepare("SELECT inst_id FROM what_they_play
                             WHERE player_id = $player_id")
             or die "prepare failed: " . $dbh->errstr();

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

Notice how the query string contains $player_id , the value we just read out of the data base. Since the player_id is an integer, it does not need to be quoted within the SQL query. When executed, this should return back all the inst_id s for that player_id . We then loop through the result, each row at a time:

# foreach inst_id, grab the instrument name from the
# instruments table and print it
while (($inst_id) = $sth->fetchrow()) {
   
my $sth = $dbh->prepare("SELECT instrument FROM instrument s
                                    WHERE inst_id = $inst_id")
       or die "prepare failed: " . $dbh->errstr();

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

    my($instrument) = $sth->fetchrow();
    print "    $instrumentn";

    $sth->finish();
}

As we loop through each row of output from the previous query, we prepare() another query to read the name of the instrument from the instruments table. Notice that within the while loop, $sth will receive the return value from prepare() and that we have declared this variable with a my() . The my() here is very important—it creates a new copy of $sth within the while loop so we will not clobber the previous value of $sth outside the while loop (the result of the query of the what_they_play table). If we had not declared $sth with a my() , that previous query would have been overwritten and we would have only processed one row of output from the query of what_they_play . An alternative to declaring $sth with my() would have been selecting a different variable name, which would have been fine, but sticking with $sth as the state handler variable is the convention (besides, that new variable would have to have been my() ed as well).

Executing the program looks like this:

$ perl showinstruments1.pl
Enter name of musician and I will show you his/her instruments: Roger Waters
   
guitar
    vocals
$

Excellent! But, not so fast. There is a problem with this program, demonstrated with this example:

$ perl showinstruments1.pl
Enter name of musician and I will show you his/her instruments: Chris O’Rourke DBD::mysql::st execute failed: You have an error in your SQL syntax near
‘Rourke” at line 1 at showinstruments1.pl line 20, <STDIN> line 1.
execute failed: You have an error in your SQL syntax near ‘Rourke” at line 1 at showinstruments1.pl line 20, <STDIN> line 1. $

Can you see what the problem is? The query that uses $who , the name entered, looks like this:

$dbh->prepare("select player_id from musicians where name = ‘$who’")

Since $who is single-quoted in the SQL query string, the single quote in the name “Chris O’Rourke” makes SQL think that the string it is comparing to name is “Chris O”. SQL then sees “Rourke”, which is totally out of place—this is a syntax error.

You may think the answer is to escape the single quote and turn $who into “Chris O’Rourke”. This is possible, and would work, but there is a better way.

{mospagebreak title=Use Placeholders}

Notice how the SQL query strings change in showinstrument2.pl:

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

use strict;
use DBI;

my($who, $player_id, $inst_id);

print "Enter name of musician and I will show you his/her instruments: ";
chomp($who = <STDIN>);

my $dbh = DBI->connect("DBI:mysql:musicians_db", "musicfan", "CrimsonKing");

die "connect failed: " . DBI->errstr() unless $dbh;

# first, grab the musicians player_id
my $sth = $dbh->prepare("SELECT player_id FROM musicians WHERE name = ?")
            
or die "prepare failed: " . $dbh->errstr();  

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

($player_id) = $sth->fetchrow();
die "player_id not found" unless defined $player_id; 
 

# given the player_id, grab their inst_ids from what_they_play
$sth = $dbh->prepare("SELECT inst_id FROM what_they_play
                             WHERE player_id = ?")
               or die "prepare failed: " . $dbh->errstr();  

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

# foreach inst_id, grab the instrument name from the
# instruments table and print it
while (($inst_id) = $sth->fetchrow()) {
   
my $sth = $dbh->prepare("SELECT instrument FROM instruments
                                WHERE inst_id = ?")
              or die "prepare failed: " . $dbh->errstr();

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

    my($instrument) = $sth->fetchrow();
    print "    $instrumentn";

    $sth->finish();
}

$sth->finish();

$dbh->disconnect();

The first call to prepare() and execute() has changed to

my $sth = $dbh->prepare("SELECT player_id FROM musicians WHERE name = ?")
            
or die "prepare failed: " . $dbh->errstr();  

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

Instead of using the variable $who in the query string, we use a question mark (?). This acts as a placeholder for a variable or value that we will provide later. That later ends up being an argument to the execute() method: $sth->execute($who) . DBI will take the argument $who and plug it into the question mark in the query string. The nice thing about using this feature is that we don’t have to worry about escaping the single quote. Much better!

 

You may be wondering—what if there is more than one variable in the query string? All of their values are provided in the execute() method and are plugged into the placeholders member-wise as shown in this snippet:

$sth = $dbh->prepare("SELECT * FROM data WHERE name = ? AND age = ?");
$sth->execute($name, $age);

But wait a minute! Both showinstruments1.pl and showinstruments2.pl are using three SQL queries. We learned earlier in this chapter that we could obtain the same information using one query by using a table join.

{mospagebreak title=DBI and Table Joins}

Any SQL query is possible using DBI. This includes table joins. Let’s modify the previous example showinstruments2.pl and perform a table join as shown here in showinstrument3.pl:

#!/usr/bin/perl – w
# showinstruments3.pl

use strict;
use DBI;

my($who, $instrument);

print "Enter name of musician and I will show you his/her instruments: ";
chomp($who = <STDIN>);

my $dbh = DBI->connect("DBI:mysql:musicians_db", "musicfan", "CrimsonKing");

die "connect failed: " . DBI->errstr() unless $dbh;

# use a table join to query the instrument names
my $sth = $dbh->prepare("SELECT instruments.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($who) or die "execute failed: " . $sth->errstr();

# loop through them, printing them
while (($instrument) = $sth->fetchrow()) {
    print "    $instrumentn";
}

$sth->finish();

$dbh->disconnect();

The big change is the preparation and execution of the query:

# use a table join to query the instrument names
my $sth = $dbh->prepare("SELECT instruments.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($who) or die "execute failed: " . $sth->errstr();

Here we construct one large query as we did previously in this chapter. It joins the musicians , what_they_play , and instruments tables. Notice how we are using a placeholder when we compare musicians.name and how the variable $who is provided within the execute() method.

Does this table join work? Yep.

$ perl showinstruments3.pl
Enter name of musician and I will show you his/her instruments: Thom Yorke
    keyboards 
    guitar
    vocals
$

Perl and DBI allow us to easily create programs that query our database. This means we can do anything with Perl that we can do with the SQL database including many SQL commands that we have not talked about in this chapter.

{mospagebreak title=Perl + DBI + CGI = Fun!}

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 the musicians_db database. 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 instru ments played by the selected musician. This page is built with the else part 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
    prin t
        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");

The if determines 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 instru ments 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
   
prin t
       ‘</ul>’,
       end_html;
}

That was fun! And using Perl, CGI, and DBI it was easy as well. Such is the power and prac ticality of Perl revealed.

{mospagebreak title=What We Didn’t Talk About}

This chapter is not meant to be an exhaustive discussion of SQL and DBI. There are many topics we did not talk about that should be learned if the maximum power of SQL is to be harnessed.

First, there are several commands that are essential to use including the following:

  • UPDATE : Allows data in a table to be modified. An example might be

    UPDATE musicians SET phone = "555-9999" WHERE player_id = 3;
  • DELETE : Deletes a row from a table. An example might be 

    DELETE FROM instruments WHERE inst_id = 13;

    Be careful! If the WHERE clause is not used, all rows in the table are deleted.

     
  • REPLACE : If the key provided does not exist, the data is inserted; otherwise the row with that key is first deleted, then the new row is inserted. An example might be

    REPLACE INTO musicians (player_id, name, phone )
           VALUES (1, "Neil Peart", "555-8888");

In addition to the preceding SQL commands, another topic that is important to know is indexing a table. This can significantly increase the speed of SELECT statements on large tables. See the docs for more information.

Speaking of seeing the docs for more information on SQL, as a reminder, be sure to check out the online documentation for MySQL at http://dev.mysql.com/doc/mysql/en/ and the excellent book The Definitive Guide to MySQL, Second Edition by Michael Kofler.

Summary

In this chapter we described how we can access a database using Perl and the DBI module. We started with a description of a relational database and followed that with a brief introduction to SQL.

We then installed MySQL and created a database with three tables. We talked about several SQL commands: INSERT and SELECT were the most important ones. Table joins were discussed as a way to implement the relations in relational databases.

Then we introduced DBI and DBD::mysql , and wrote several Perl scripts to access and query the database.

We ended with an example of how easy it is to create dynamic web content by connecting Perl, DBI , and CGI.pm . And in the middle of that discussion we took time out of our busy day to call one of our favorite musicians.

Exercises

  1. Write a Perl script that prompts the user for an instrument and then prints all the musicians that play that instrument.
  2. Write a CGI program similar to musicians.pl that is a web interface to the script you created for exercise 1.

 


 

1. These aren’t their real phone numbers. Sorry about that.

2. www.rush.com

3. This is a very bad password for many reasons, the least of which is that it is published in this book. For information on creating good passwords, see Hacking Linux Exposed, Second Edition, Brian Hatch, Osborne Press (2002).

4. This MySQL stuff is easy!

5. Another bad password, but a snippet of lyric from a great song.

6. TMTOWTDI in SQL too!

7. As usual, there are a lot of ways to execute an SQL query and retrieve its results using Perl and DBI. We will look at the easiest and most common way, but you can read about all the various ways by typing
perldoc DBI at the shell prompt.

[gp-comments width="770" linklove="off" ]
antalya escort bayan antalya escort bayan