Home arrow Perl Programming arrow Page 4 - SQL and CGI with Perl and DBI

DBI and Table Joins - Perl

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

TABLE OF CONTENTS:
  1. SQL and CGI with Perl and DBI
  2. A More Complex Example
  3. Use Placeholders
  4. DBI and Table Joins
  5. Perl DBI CGI = Fun!
  6. What We Didnít Talk About
By: Apress Publishing
Rating: starstarstarstarstar / 5
April 03, 2008

print this article
SEARCH DEV SHED

TOOLS YOU CAN USE

advertisement

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.



 
 
>>> More Perl Programming Articles          >>> More By Apress Publishing
 

blog comments powered by Disqus
escort Bursa Bursa escort Antalya eskort
   

PERL PROGRAMMING ARTICLES

- Perl Turns 25
- Lists and Arguments in Perl
- Variables and Arguments in Perl
- Understanding Scope and Packages in Perl
- Arguments and Return Values in Perl
- Invoking Perl Subroutines and Functions
- Subroutines and Functions in Perl
- Perl Basics: Writing and Debugging Programs
- Structure and Statements in Perl
- First Steps in Perl
- Completing Regular Expression Basics
- Modifiers, Boundaries, and Regular Expressio...
- Quantifiers and Other Regular Expression Bas...
- Parsing and Regular Expression Basics
- Hash Functions

Developer Shed Affiliates

 


Dev Shed Tutorial Topics: