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 "    $instrument\n";

    $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 "    $instrument\n";

    $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.