Login

SELECT Queries and Perl

In this third part of a four-part series on Perl and DBI, you will learn about using SELECT queries to get information from databases. This article is excerpted from chapter 15 of the book Beginning Perl (Apress; ISBN: 159059391X).

The WHERE Clause

So far, all of our
SELECT
queries have shown every row in the table. Oftentimes we want only specific rows. The
WHERE
clause tells the
SELECT
query to show only the rows that match our criteria. For instance, we may want to see all the information in the
musicians
table for the musician with
player_id
having the value 1.

Note There are many different ways to use the
WHERE
clause in the
SELECT
. See the docs for all the details.

We could go on forever with describing all the different uses of the
WHERE
clause, but we should instead mention how to sort the output.

{mospagebreak title=The ORDER BY Clause}

The last bit of SQL that we will look at will be how to order the output. The
ORDER BY
clause allows us to specify on which field the output should be sorted. Let’s say we want to show all the musician information, but the output is to be sorted by name.

mysql> SELECT * FROM musicians ORDER BY name;

player_id name

phone

2 Geddy Lee

555-2323

5 Lenny Kravitz

555-5656

3 Marshall Mathers III 555-3434

6 Mike Diamond

555-6767

1 Roger Waters

555-1212

4 Thom Yorke

555-4545

6 rows in set (0.00 sec)

How about all the instruments and their difficulty from easiest to hardest.

Sometimes we want to select information from our database to satisfy criteria that are a bit more complicated than what we have seen so far. For instance, if we use our database for what it was really created for, finding out what instrument a particular musician plays, it is going to be more complex.

As an example, if we wanted to find out what instruments Lenny Kravitz plays, we would have to first find out what his
player_id
is by querying the
musicians
table; then using that
player_id
, select the
inst_id
s out of
what_they_play
; then for each of those
inst_id
s, we can then get the
instrument
name out of the
instruments
table.

Whew, that seems like a lot of work just to find the instruments that Lenny Kravitz plays, especially since this database was created to do just that kind of query. There must be a better way, right? Yes, there is another way.6 These four queries can be done in one query using a table join.

Table Joins

MySQL is a relational database, so we must be able to query information out of our database using information from more than one table. By joining the tables, we can use multiple tables in a single SELECT.

Again, the best way to talk about table joins is with an example. In the complex
SELECT
in the previous section, we discovered the instruments that Lenny Kravitz plays. We could have stated that request like this: “Give me all the
instrument
names in the
instruments
table that match the
inst_id
s in the
what_they_play
table for the
player_id
in the
musicians
table asso
ciated with the musician with the
name
Lenny Kravitz.”

The field
instrument
in the
instruments
table can be indicated in SQL with both the table name and the field name as
instruments.instrument
. Likewise,
inst_id
in
what_they_play
is
what_they_play.inst_id
. These fully qualified names allow us to use
player_id
in both the
musicians
table and the
what_they_play
table, and SQL can keep them separate because we will call them
musicians.player_id
and
what_they_play.player_id
.

Given that bit of good news, let’s translate the query we stated in English two paragraphs earlier into SQL:

The big difference in this query is that we have listed more than one table in the
FROM
part:
instruments
,
what_they_play
, and
musicians
. Then, our
WHERE
clause has several conditions all
AND
ed together. Since we are using more than one table and are comparing values in one table with the values in another, we are joining the data together. Hence the term table join.

Let’s do one more table join. Here is a query that will show all the musicians that play
percussion
instruments:

As you can see, the
SELECT
combinations are endless! SQL is quite flexible—we can pull out exactly the information we need in exactly the order we want from the particular tables in which we are interested. We have only explored a few SQL commands—there are so many more to learn. Check out the documentation and Kofler’s book for more information.

Using the MySQL command line interface is enjoyable, but it is much more fun to query our database using Perl and
DBI
.

{mospagebreak title=Introduction to DBI}

DBI is the Database Independent module and was written by Tim Bunce. It is a collection of APIs that allow a programmer to connect to and access a database. As the name implies, the module allows us to write programs to access a database independent of the type of database. We can write a program to query an Oracle database, or a Sybase database, or MySQL, or Postgres, or ODBC, and the list goes on and on. All we need on our computers is the DBI module and the appropriate Database Driver (DBD).

Note Each of the mentioned databases has its own dialect of SQL. Most implement the basic commands such as
INSERT
and
SELECT
in a similar way, but when it comes to the details of specific commands, they are sometimes implemented slightly differently from database to database. Keep this in mind if you are creating a Perl script that you want to port from one type of database to another—use the common form of each command even if a database has a nifty feature that you can use that is not supported elsewhere.

We are using MySQL server in this chapter, so we need to install
DBI
and the MySQL
DBD
modules.

Installing DBI and the DBD::mysql

The first step to using DBI is to install the appropriate modules. The first module we need is DBI. As this is being written, the latest version of DBI
on CPAN is 1.42, but as usual this version may not be the same by the time you read this.

Follow the instructions in Chapter 13 on installing modules to install
DBI
. When successful, it is time to install the MySQL driver. The name of this module is
DBD::mysql
.

Connecting to the MySQL Database

Our first Perl program will simply connect to the MySQL database. If it works correctly, we know that DBI and DBD::mysql were installed correctly, and the real fun can then begin. Let’s look at an example ( connect.pl
):

After the shebang, comment, and
use strict;
, we
use
the
DBI
module. Then we see a call to the
DBI->connect()
method. When we talked about object-oriented programming in Chapter 12, we mentioned that most modules use the method
new()
as their constructor.
DBI
, however, uses
connect()
. This is fine—any method name that we choose can be the constructor, and since to construct a
DBI
object we must connect to a database,
connect()
seems a logical choice.

There are three arguments to
connect()
: the DSN, also known as the data source name, the username of the user, and their password. In this invocation, the data source name is

"DBI:mysql:musicians_db"

All data sources will start with
DBI
, followed by a colon, the term
mysql
since we are using the MySQL server and
DBD::mysql
, a colon, and the database to which we are connecting, here
musicians_db
.

Note Let’s say that one day we want to port our database from MySQL to some other database server such as Oracle. In this script, all we need to do is change the text
mysql
in the data source to
oracle
. Provided that
DBD::oracle
is installed on our machine and we don’t use any MySQL-specific queries, the script will work perfectly. Talk about portable!

The return value of
DBI->connect()
is an object that we can use to do things with the data
base. We call this the database handler so we name it
$dbh
. After the call to
DBI->connect()
, we check the value of
$dbh
.

die "connect failed: " . DBI->errstr() unless $dbh;

This makes sure
$dbh
has a true value. If
DBI->connect()
fails, it returns a false value to
$dbh
, so we
die()
, complaining that something went wrong with the database connection. The function
DBI->errstr()
will report the error of whatever just went wrong, so as a help to the user we will include this information in the string that
die()
prints.

If all is well, we print a cheerful message and disconnect from the database.

print "connect successful!n";

$dbh->disconnect();

We use the
disconnect()
method to disconnect from the database. This is not really necessary since Perl will disconnect us when the script terminates, but it is still a polite thing to do.