SQL and CGI with Perl and DBI - Use Placeholders
(Page 3 of 6 )

Notice how the SQL query strings change in showinstrument2.pl:

#!/usr/bin/perl -w
# showinstruments2.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 = ?")
             or die "prepare failed: " . $dbh->errstr();  

$sth->execute($who) 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 = ?")
               or die "prepare failed: " . $dbh->errstr();  

$sth->execute($player_id) or die "execute failed: " . $sth->errstr();

# 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 instruments
                                WHERE inst_id = ?")
              or die "prepare failed: " . $dbh->errstr();

    $sth->execute($inst_id) or die "execute failed: " . $sth->errstr();

    my($instrument) = $sth->fetchrow();
    print "    $instrument\n";

    $sth->finish();
}

$sth->finish();

$dbh->disconnect();

The first call to prepare() and execute() has changed to

my $sth = $dbh->prepare("SELECT player_id FROM musicians WHERE name = ?")
             or die "prepare failed: " . $dbh->errstr();  

$sth->execute($who) or die "execute failed: " . $sth->errstr();

Instead of using the variable $who in the query string, we use a question mark (?). This acts as a placeholder for a variable or value that we will provide later. That later ends up being an argument to the execute() method:$sth->execute($who).DBIwill take the argument$whoand plug it into the question mark in the query string. The nice thing about using this feature is that we don’t have to worry about escaping the single quote. Much better!

 

You may be wondering—what if there is more than one variable in the query string? All of their values are provided in theexecute()method and are plugged into the placeholders member-wise as shown in this snippet:

$sth = $dbh->prepare("SELECT * FROM data WHERE name = ? AND age = ?");
$sth->execute($name, $age);

But wait a minute! Bothshowinstruments1.plandshowinstruments2.plare using three SQL queries. We learned earlier in this chapter that we could obtain the same information using one query by using a table join.

Next: DBI and Table Joins >>
 

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.