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

Next: Perl DBI CGI = Fun! >>
 

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.