SQL and CGI with Perl and DBI - DBI and Table Joins (
Page 4 of 6 )
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 " $instrument\n";
}
$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.