Perl
  Home arrow Perl arrow Page 3 - SQL and CGI with Perl and DBI
Dev Shed Forums 
Administration  
Apache  
BrainDump  
DHTML  
Flash  
Java  
JavaScript  
Multimedia  
MySQL  
Oracle  
Perl  
PHP  
Practices  
Python  
Reviews  
Security  
Style-Sheets  
Web Services  
XML  
Zend  
Zope  
Forums Sitemap 
IBM® developerWorks 
Dedicated Servers 
E-Commerce Hosting 
Linux Web Hosting 
Managed Hosting 
Small Business Hosting 
Download TestComplete 
VPS Hosting 
Weekly Newsletter

 
Developer Updates  
Free Website Content 
IBM Developerworks
 RSS  Articles
 RSS  Forums
 RSS  All Feeds
Write For Us Get Paid 
Request Media Kit
Contact Us 
Site Map 
Privacy Policy 
Support 
 USERNAME
 
 PASSWORD
 
 
  >>> SIGN UP!  
  Lost Password? 
PERL

SQL and CGI with Perl and DBI
By: Apress Publishing
  • Search For More Articles!
  • Disclaimer
  • Author Terms
  • Rating: 4 stars4 stars4 stars4 stars4 stars / 2
    2008-04-03

    Table of Contents:
  • SQL and CGI with Perl and DBI
  • A More Complex Example
  • Use Placeholders
  • DBI and Table Joins
  • Perl DBI CGI = Fun!
  • What We Didn’t Talk About

  • Rate this Article: Poor Best 
      ADD THIS ARTICLE TO:
      Del.ici.ous Digg
      Blink Simpy
      Google Spurl
      Y! MyWeb Furl
    Email Me Similar Content When Posted
    Add Developer Shed Article Feed To Your Site
    Email Article To Friend
    Print Version Of Article
    PDF Version Of Article
     
     
     
    ADVERTISEMENT

    Dell PowerEdge Servers

    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.

    More Perl Articles
    More By Apress Publishing


       · This article is an excerpt from the book "Beginning Perl," published by Apress. We...
     

    Buy this book now. 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.

       

    PERL ARTICLES

    - Perl: A Continuing Look at Hashes and Multid...
    - Perl: Another Round with Hashes
    - Perl Hashes
    - Perl Lists: A Final Look at List::Util
    - Perl Lists: Utilizing List::Util
    - Perl Lists: The Split() Function
    - SQL and CGI with Perl and DBI
    - Perl Lists: More Functions and Operators
    - SELECT Queries and Perl
    - Perl Lists: More on Manipulation
    - Creating a Database with Perl and DBI
    - Perl: Sailing the List(less) Seas
    - Perl and DBI
    - Perl: Concatenating Text and More
    - Perl Text: Quoting Without Quote Marks

     
    Accelerating Trading Partner Performance
     
    Competing on Analytics
     
    Cost Effective Scaling with Virtualization and Coyote Point Systems
     
    Five Checkpoints to Implementing IP Telephony
     
    Hosted Email Security: Staying Ahead of New Threats
     




    © 2003-2008 by Developer Shed. All rights reserved. DS Cluster 4 hosted by Hostway