Simple SELECT Statement

Let's take a look at a short program that fetches some data from a table called library in our database. The library table is filled with information on many of the computer books in a personal library. In the following section, you'll see blocks of code; then you'll see an explanation of what the numbered lines mean. The numbers are added for clarity and are not part of the code.

Note 

If you did not download the library data from the book's Web site, you will need to enter some sample data into the library table for these examples to work.

01: #!/usr/bin/perl -w 02: # 03: # select1.pl 04: # Chapter 4 05: # Listing 4-1 06: # 07: use strict; 08: use DBI;

Line 1 tells the system where to find Perl and turns on warnings with the -w flag.

Lines 2-6 are comments that provide information about the program.

Line 7 turns on the strict pragma. This forces programmers to use better programming practices, such as declaring variables before using them. Also, turning strict on can eliminate some hard-to-find bugs -such as using two variables with the same name.

Line 8 loads the DBI module-since we are interacting with a database, we need to load the DataBase interface.

09: my $dbh = DBI->connect ("DBI:mysql:BibleBook","bookuser","testpass")  10:     or die("Cannot connect: $DBI::errstr");

Lines 9-10 constitute a single Perl statement that tries to create an object of class DBI that represents a connection to the database. If the connection can't be made, our call to DBI->connect fails, and we abort the program with die. The $dbh stands for DataBase Handle.

11: my $sql = qq(SELECT * FROM library);

Line 11 is the SQL statement we use to fetch the data from the database table. The double quote operator (qq) works well when you are creating strings that may contain quotes. The qq operator acts just like a double quote, but you do not have to escape any double quotes that may be in the string.

This SQL statement tells the DBMS to fetch (SELECT) all of the data (*) FROM the requested table (library).

12: my $sth = $dbh->prepare($sql);

Line 12 calls the prepare method of the DBI. When you prepare an SQL statement, some databases store the SQL statement in an internally compiled form so that it executes faster when the execute method is called. MySQL does not compile the SQL statement but simply stores it. If you move from MySQL to a different database, however, you won't need to make any changes to work with the new database.

The prepare method returns a handle ($sth) used to access the DBI methods for that particular SQL statement. The $sth stands for Statement Handle.

13: $sth->execute; 14: my $counter = 1;

Line 13 calls the execute method on the SQL statement. This runs the SQL statement on the database-the results of the SQL call are stored as a reference in the statement handle ($sth).

Line 14 declares and initializes a variable named $counter to 1. We use this variable to count our records as we display the results of the SQL call.

15: while(my $record = $sth->fetch){ 

Line 15 begins a while loop that keeps returning data, one record at a time, until it runs out of data to return. The data it returns is from the result of the SQL call on Line 13.

Inside the while declaration, we declare a variable called $record. We use this variable as a pointer. Each time through the while loop, the $sth->fetch grabs one record from the results of the SQL call and stores a reference to an array in $record.

This may be a little hard to grasp at the moment, but it should click once we've gone through the next few lines of code. For now, just remember that $record holds a reference to an array. This is like saying $record = \@foo; when you are storing a reference to an array in regular Perl code.

16:     print "$counter: ";

Line 16 prints some text for our output and displays the current value in $counter.

17:     for my $field (@$record){ 18:         print "$field - "; 19:     }

Line 17 begins a for loop that goes through each item in the array @$record. Of course, instead of "@$record" we could have written "@{$record}", which means exactly the same thing. If you aren't comfortable with either of these syntaxes, please refer to the perlreftut man page that comes free with Perl.

Line 18 prints the current field value ($field) from the loop and prints a dash with a space to separate the record data and make it a little easier to read. Each time through the for loop, the current record's field data gets stored in the $field variable.

Line 19 ends the for loop.

20:     $counter++; 21:     print "\n\n"; 22: }

Line 20 increments the $counter variable-there is no point in having a counter if it is never incremented.

Line 21 prints two newline characters so that the output can be visually separated from one record to the next.

Line 22 ends the while loop as well as this program.

This program makes a connection to the database, creates an SQL statement to fetch all of the data from the library table, and then displays the results on the screen. A portion of this program's output is shown in the following code.

55: 1565922697 - Web Security and Commerce - Simson Garfinkel Gene Spafford Deborah Russell (Editor) -     $34.95 -  Paperback, 1st ed., 506pp. -  O'Reilly & Associates, Incorporated -  May 1997 -  -  56: 1565924924 - HTML: The Definitive Guide - Chuck Musciano Bill Kennedy Mike Loukides (Editor) -     $34.95 -  Paperback, 3rd ed., 587pp. -  O'Reilly & Associates, Incorporated -  September 1998 -  -  57: 0672308916 - PERL Programming Unleashed, with CD-ROM - Charles Salzenberg -     $34.95 -  Paperback, 800pp. -  Macmillan Computer Publishing -  February 1996 -  -  58: 1565925254 - Palm Programming: The Developer's Guide with CD-ROM - Neil Rhodes Julie McKeehan -     $32.95 -  Paperback, 1st ed., 482pp. -  O'Reilly & Associates, Incorporated -  December 1998 -  -  59: 1565924207 - PalmPilot: The Ultimate Guide - David Pogue -     $29.95 -  Paperback, 1st ed., 489pp. -  O'Reilly & Associates, Incorporated -  May 1998 -  -  60: 0672304023 - UNIX, with CD-ROM (Unleashed) - Emmett Dulaney Susan Christopherson Fran Hatton -     $29.95 -  Hardcover, 1st ed., 1600pp. -  Sams -  November 1993 -  - 

SELECT statement with WHERE clause

Now we know how to get every record in a database. But if you want only some of the records, or just one, you don't want to have to sift through all of the data yourself to find what you are looking for. Instead, let the database do the work for you! We can slightly modify the first program so that it looks for only a particular record.

01: #!/usr/bin/perl -w 02: # 03: # select2.pl 04: # Chapter 4 05: # Listing 4-2 06: # 07: use strict; 08: use DBI; 09: my $dbh = DBI->connect ("DBI:mysql:BibleBook","bookuser","testpass") 10:     or die("Cannot connect: $DBI::errstr"); 

Lines 1-10 are exactly like the code in our first example.

11: my $sql = qq(SELECT * FROM library WHERE isbn = ‘1565922697');

Line 11 has a SQL statement that causes the database to find a specific record for us.

The SQL statement starts out the same as in our first example, but now there is a WHERE statement. The entire WHERE statement is: WHERE isbn = ‘1565922697'. This tells the DBMS to look only for records where the isbn field is equal to 1565922697. Notice that we used a single = for a string comparison! This is because we are creating an SQL statement, which is different than a Perl statement.

In English, this is something like: Select all records from the library table where the isbn field is equal to 1565922697.

You are not limited to using just equals (=) in your WHERE statements. Logical operators and mathematical operators are also supported. Refer to Appendix B for some of the more common operators used with WHERE.

12: my $sth = $dbh->prepare($sql); 13: $sth->execute; 14: while(my $record = $sth->fetch){ 15:     for my $field (@$record){ 16:         print "$field\n"; 17:     } 18:     print "\n\n"; 19: }

Lines 12-19 work the same way as the code in our first example. Notice, though, that we have removed the code that acts as a counter. Since we are looking for one match only, there is no need to count how many records are returned.

The output should contain only one record, as shown in the following code.

1565922697 Web Security and Commerce Simson Garfinkel Gene Spafford Deborah Russell (Editor)     $34.95  Paperback, 1st ed., 506pp.  O'Reilly & Associates, Incorporated  May 1997
Note 

If you have entered your own data into the database, instead of downloading the table information from the Web site, you will have to change the SQL statement in Line 11 so that the WHERE statement looks for something that matches your data.

SELECT statement with ORDER BY clause

The DBMS can do more than just fetch the data; it can perform operations on the data so that it is displayed exactly how you want it to.

For example, you may want the data to be returned sorted on the publisher field. To do that, use the ORDER BY clause.

01: #!/usr/bin/perl -w 02: # 03: # select3.pl 04: # Chapter 4 05: # Listing 4-3 06: # 07: use strict; 08: use DBI; 09: my $dbh = DBI->connect ("DBI:mysql:BibleBook","bookuser","testpass")  10:     or die("Cannot connect: $DBI::errstr");

Lines 1-10 are exactly like the code from our first example.

11: my $sql = qq(SELECT * FROM library ORDER BY publisher);

Line 11 contains the new SQL statement. Notice that the only difference here is the ORDER BY publisher statement. This causes the DBMS to sort the output by the specified field (publisher in this case). Determining that the program output is sorted is difficult, especially since only three of the records are shown in the following output. Run this program on your system to verify the results. If you want, change publisher to one of the other field names such as author or title to verify that the output is indeed sorted

12: my $sth = $dbh->prepare($sql); 13: $sth->execute; 14: my $counter = 1; 15: while(my $record = $sth->fetch){

Lines 12-15 are identical to the code in our first example, running the search and then looping through each record returned.

16:     print "Book #$counter:\n"; 17:     for my $field(@$record){ 18:         print "$field\n"; 19:     }

Line 16 prints the book number and a newline.

Line 17 begins a for loop that iterates through all of the data for this record and stores the current data in the $field variable. Remember, $record is a reference to an array containing the data for the current record. By using @$record, we are simply looping through the array.

Line 18 prints the current item and a newline character.

Line 19 ends the for loop that begins on Line 17.

20:     $counter++; 21:     print "\n"; 22: }

Line 20 increments the $counter variable.

Line 21 prints a newline character.

Line 22 ends the while loop, as well as the program.

Take a look at the output in the following code. The data should be sorted on whatever field you put in your SQL statement on Line 11.

Book #58: 0471118494 The HTML SourceBook Ian S. Graham     $34.95  Paperback, 432pp.  Wiley, John & Sons, Incorporated  February 1995 Book #59: 0471247448 Official Guide to Programming with CGI.pm Lincoln Stein     $34.99  Paperback, 1st ed., 310pp.  Wiley, John & Sons, Incorporated  April 1998 Book #60: 1861000723 Professional Active Server Pages Alex Homer Richard Harrison Brian Francis Christian Gross Darren Gill    Bruce Hartwell Stephen Jakab Andrew Enfield Chris Ullman (Editor) Tim    Briggs (Editor)     $12.95  Paperback, 641pp.  Wrox Press, Inc.  March 1997



Perl Database Programming
Perl Database Programming
ISBN: 0764549561
EAN: 2147483647
Year: 2001
Pages: 175

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net