Thomas is a writer residing in Selkirk, Manitoba, Canada. He can be contacted at [email protected].
One day at work, I was shocked to find that my junior co-workers didn't understand the ease and flexibility of Perl with regard to database machinations. They all had rudimentary Perl skills, so I decided to explain everything in one simple, easy to understand lesson. I'm sharing this basic tutorial here in hopes that more novice Perl programmers will make use of Perl's tight database integration, in this case with the MySQL database.
Background
Each example assumes you're already connected to a database and are using the DBI module to do so. DBI is a database-independent interface for Perl, which means that it provides a common interface to all kinds of databases. It also requires the DBD (or database-dependent) driver module that corresponds to the database you're using. In the case of MySQL, that would be the DBD::mysql module. This architecture allows you to change the underlying database that a Perl application uses, without having to change much of the application's code. DBI and all the DBD::* modules are available on CPAN.
In all of the following statements, there appear the following three lines of code. Get to know them well, as you'll be writing them often.
$query = "SELECT col1, col2, col3, col4 FROM $thistable"; $sth = $dbh->prepare($query); $sth->execute();
As with anything Perl, there are many ways to accomplish the basic logic of the database call. I've chosen this one because it shows simply and clearly the stages of the program execution. The MySQL query is contained in $query and will fluctuate with every example. The statement is then prepared and executed. If there are problems in your SQL syntax, this is the moment in the program execution that an error will be generated. Check your logs or employ CGI::Carp to report errors to the browser (assuming your database query is part of a web-based application) if you run into this.
While these three lines are the starting point, what I'll focus on in this article are the ways to select the values required and get them into the sort of data structure that is most useful for a given situation. Note also that the $thistable variable can be anything you require. This is also true for every bit of information contained in the MySQL query ($query).
This document presents the following constructs:
- Selecting one column into one variable using one variable.
- Selecting one column from many rows into one array using one variable.
- Selecting many columns from one row into one array using one array.
- Selecting many columns from many rows into many arrays using one array.
Each construct should fit with any number of applications. What we'll be exploring is the final form the values take.
Selecting one column into one variable using one variable. The starting point in any understanding of the easy integration of Perl and MySQL must begin with the simplest of all database calls—calling one known column and placing it into one known variable, like this:
$query = "SELECT thiscolumn FROM thistable
WHERE id = 6";
$sth = $dbh->prepare($query);
$sth->execute();
$thisvariable = $sth->fetchrow();
The query is loaded into $query and the database selection is prepared and executed. The previously declared variable $thisvariable is then loaded with the result of the database call via the fetchrow() function. Simple. Note that the MySQL statement in all examples reflects the need of the example.
Selecting one column from many rows into one array using one variable. When you need one array of values as the end product, this construct will suffice. There are several other ways an array (or even many arrays) can result from this processing block as well. More on that later.
$query = "SELECT col1 FROM thistable
WHERE id >= 0";
$sth = $dbh->prepare($query);
$sth->execute();
while ($thisvalue = $sth->fetchrow()) {
push @thisarray, $thisvalue;
};
This example shows the normal preparation and execution procedures, as well as a good use of the fetchrow() function. The while loop stuffs values that are generated by the database call into @thisarray. The length and complexity of @thisarray can be whatever you require.
Selecting many columns from one row into one array using one array. This is the first time we've used the fetchrow_array() function. This function fetches entire rows and is one of the so-called "higher functions" that have been given priority of execution. This makes for a speedier database call for higher volumes of data.
$query = "SELECT col1, col2, col3, col4 FROM
$thistable WHERE id = 6";
$sth = $dbh->prepare($query);
$sth->execute();
@thesevalues = $sth->fetchrow_array());
The normal preparation and execution takes place, followed by a fetchrow_array() call that is simply placed into the one array, @thesevalues. Each column is one index item.
Selecting many columns from many rows into many arrays using one array. This is perhaps the best implementation of the powerful combination that is the meshing of Perl and MySQL. The amount of data awaiting processing can be enormous. Your only limit is memory resources.
The columns called will be one array index item for each column fetched from the database. The complexity of the database call narrows the number of index items and thus the volume of data. Use a MySQL statement to narrow or order the search, as in the following example:
$query = "SELECT col1, col2, col3, col4 FROM
$thistable WHERE id >= 0 ORDER BY id DESC";
$sth = $dbh->prepare($query);
$sth->execute();
while (@thesevalues = $sth->fetchrow_array()) {
push @array1, $thesevalues[0];
push @array2, $thesevalues[1];
push @array3, $thesevalues[2];
push @array4, $thesevalues[3];
};
The construct performs the database preparation and execution normally. What changes in this machination is that the array that is filled with fetchrow_array() is then pushed onto more arrays waiting within the while loop. For every successive result the fetchrow_array() function is able to return, the numbered arrays (@array1, @array2, etc.) are filled. You may then act upon all of the arrays in any way you see fit. Each array will reflect one column within the database for many rows, as befitting your MySQL database call. Using an index column such as the AUTO_INCREMENT ID column given in the example is handy to keep your database model simple and easy to retrieve.
This set of constructs is a great starting point in examining database calls. Each is simple and easy to use and conveys the mindset a database programmer employs. I hope these will get you started having fun with database calls using Perl.
TPJ