Retrieving Query Results

Once you know how to connect to MySQL, you can start querying the database. There are two general types of queries: those that return records (i.e., SELECT queries) and those that do not (ALTER, CREATE, UPDATE, and DELETE). Queries of the first type are slightly more complicated, but I'll go ahead and demonstrate one of those first. Executing queries that return results is a two-step process just to send the query to the database. It requires both the prepare() and execute() methods.

Assuming you have connected to the database and assigned that result to $dbh, you would then do this:

$sth = $dbh->prepare("SQL QUERYSTATEMENT");
$sth->execute();

The result returned by the prepare() call is assigned to a variable that I'll call $sth (short for statement handle). Normally, before you execute the query, you'll want to make sure that prepare() returned a good result. The easiest way to do so is to use the defined() function.

if (defined($sth)) {
   $sth->execute();
} else {
   print "Could not execute the query!\n";
}

After executing the query, you can retrieve all of the returned values using the fetchrow_array() function. It returns each record as an indexed array, beginning with 0. (See the accompanying sidebar, "Referring to Columns by Name," for other options.)

while (@row = $sth->fetchrow_array()) {
   print "$row[0] $row[1]...\n";
}

Referring to Columns by Name The fetchrow_array() method is the most efficient and common way to retrieve values from the database, but it does have one significant drawback: You cannot refer to the columns by name to get their values. There are other, more complex, options, though. The first and easiest is to use fetchrow_hashref(). It works like fetchrow_array() but allows you to name the columns to retrieve their values. while ($row = $sth->fetchrow_hashref()) { print $row->{'column';}; } Unfortunately, this method is significantly slower than fetchrow_array(). Second, if you know exactly how many columns will be returned, and in what order, you can assign these values to variables when you use fetchrow_array(): while ( ($col1, $col2, $col3) = $sth-> fetchrow_array()) ) {... The third option is to use the bind_col() method to preassign column numbers to variable names. This system requires significantly more programming, so see the MySQL and Perl documentation for examples.

Once you have finished retrieving the values, you conclude the query process.

$sth->finish();

As a simple example of using this information, I'll write a Perl script that takes the name of a database as an argument and displays all of the tables in that database.

To retrieve query results:

1.	Create a new Perl script (Script 8.3). #!/usr/bin/perl -w use strict; use DBI;
2.	Determine which database to use and check that one was entered. my $database = $ARGV[0]; if (defined($database)) { This script will take one argument, the database name, which will be accessed via the @ARGV array and assigned to the $database variable. If the variable has a value, it's safe to proceed with the script.
3.	Connect to MySQL and prepare the query. [View full width] my $dbh = DBI->connect("DBI:mysql: $database:localhost",'username', 'password', {RaiseError => 1}); my $sth = $dbh->prepare('SHOW TABLES'); The connection line in this script is slightly different from its predecessors in that it now uses a variable for the database name. To do this, I had to change the quotation marks from single to double, so that the script would insert the value of $database (interpolate the variable). As for the query itself, it's quite simple: SHOW TABLES will return a list of tables in the current database. Script 8.3. The browse_tables.pl script is a simple interface to show the list of tables in a given database. [View full width] 1 #!/usr/bin/perl -w 2 3 # Script 8.3, 'show_tables.pl' 4 # This script takes a database name as 5 # a command line argument and shows 6 # the tables in that database. 7 8 # Use what needs to be used. 9 use strict; 10 use DBI; 11 12 # This script takes one argument when invoked. 13 # The argument is a database name. 14 my $database = $ARGV[0]; 15 16 if (defined($database)) { 17 18 # Connect to the database. 19 my $dbh = DBI->connect("DBI:mysql: $database:localhost", 'username', 'password', {RaiseError => 1}); 20 21 # Query the database. 22 my $sth = $dbh->prepare('SHOW TABLES'); 23 24 if (defined($sth)) { 25 $sth->execute(); 26 my @row; 27 while (@row = $sth-> fetchrow_array()) { 28 print "$row[0]\n"; 29 } 30 } 31 $sth->finish(); 32 33 # Disconnect. 34 $dbh->disconnect; 35 36 } else { 37 print "Please enter a database name when calling this script! \n"; 38 }
4.	Execute the query. if (defined($sth)) { $sth->execute(); If the prepare() statement worked, $sth has a good value and the query can be executed.
5.	Retrieve and print every record. my @row; while (@row = $sth->fetchrow_array()) { print "$row[0]\n"; } The most foolproof method to access every column returned for every row is to use this construct here. It will print out each element, one line at a time. With the SHOW TABLES query, each element will be a single table name. You can confirm what results the Perl script receives by running that same query using the mysql client (Figure 8.16). Figure 8.16. Know what results your scripts will receive by running the same query using another interface. [View full size image]
6.	Finish the query and close the database connection. $sth->finish(); $dbh->disconnect;
7.	Finish the main conditional. } else { print "Please enter a database name when calling this script! \n"; } If the script's user failed to enter a database name, a message will be printed saying so.
8.	Save the script as show_tables.pl, change the permissions (if necessary), and run the script using the syntax ./show_tables.pl databasename or perl show_tables.pl databasename (Figures 8.17 and 8.18). Figure 8.17. The show_tables.pl script will list every table in the database named, such as accounting here. Figure 8.18. As long as the script has permission to access a database, it will list its tables without further modification. (This database is not discussed in this book.) Remember that your script will still need the proper permissions to access any database you enter here. With that in mind, you'll have the easiest time if you use the root username and password to connect, although that is less secure.