DBI and Table Joins - Perl

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 themusicians,what_they_play, andinstrumentstables. Notice how we are using a placeholder when we comparemusicians.nameand how the variable$whois provided within theexecute()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 andDBIallow 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.