Perl & MySQL Database Machinations
Learn to operate at a professional level with Perl & MySQL |
 By Thomas Valentine
Viewed 12345 Times
Posted Sept. 18, 2024 |
|
Perl and MySQL work together to achieve some very powerful procedures for the manipulation of your database data. Through the use of the Perl DBI module, it is possible to store and retrieve your database data in a very timely fashion. The DBI module is a great tool to work with, as it contains methods for just about any database machination you will run across.
We’ll be exploring the syntax and use of Perl and the MySQL queries that we all will come across eventually. In this article, we’ll concentrate on retrieving information from the database, with a few exceptions for fun.
In the examples that follow, every conceivable method of acquiring the data is presented. You’ll see that the methods for retrieving the piece of data are simple and to the point. Once the data is retrieved, you may act upon it in any way you see fit. The data will exist in the variables and arrays exactly as it appears in the database.
Select One Database Item Into One Variable
Retrieving one piece of information from a database is a straightforward operation, but there are some finer points that need to be addressed. First, what you need to do is decide if you’re going to use the fetchrow() method or fetchrow_array() method. Since there is only one piece of information to be retrieved, the fetchrow() method will be used.
Note that it is entirely possible to use fetchrow_array() to catch the piece of data – you simply wouldn’t because that is not what it was designed for. The fetchrow_array() method loads an array into memory rather than a scalar variable. Since we’re only taking out one piece of information from the database, it is not recommended that you use fetchrow_array() in this situation.
Select A Piece of Data Using the 3 Step Method
$query = “SELECT id FROM this_table WHERE id = 12”;
$sth = $dbh->prepare($query);
$sth->execute();
$thisvariable = $sth->fetchrow();
The example shows how to define the query and place it in $query. You then prepare the query with the prepare() method and execute the query by calling the execute() method. You then catch the piece of data with the fetchrow() method and place it in $thisvariable
Select A Piece Of Data Using the 2 Step Method
$sth = $dbh->prepare(SELECT id FROM this_table WHERE id = 12);
$sth->execute();
$thisvariable = $sth->fetchrow();
With this two step method, we define the query within the prepare() method and execute the query by calling execute(). In place of the $query variable from the previous example, we send the textual database query as the argument to prepare(). We then catch the piece of data with the fetchrow() method and place it in $thisvariable. Now we’ll explore the one step method through the use of the do() method.
Select a Piece Of Data Using The 1 Step Method
$dbh->do(qq{SELECT id FROM this_table WHERE id = 12});
$thisvariable = $sth->fetchrow();
We used the do() method to define, prepare, and execute a database call, then catch the piece of data with the fetchrow() method and place it in $thisvariable. This is a great method to use to clean up your code if you have amany database queries in the same script. With less to read, there is less confusion and your code is cleaner in general.
Select Many Database Items Into Many Variables
Since there will be a future need to select many pieces of information into many variables, the examples will show this. However, there are a few points to consider before continuing. Since we’ll be retrieving more than one piece of information, it is recommended that you use the fetchrow_array() method to achieve your database query.
The fetchrow_array() method is of a higher order than is the fetchrow() method, making the executions with this method very fast and very reliable. The simple constructions used with the fetchrow_array() method are effective and intuitive, which no doubt lends to the massive popularity of the DBI module.
The database query itself must be of a sort that allows for more than one piece of data to be retrieved. Using a WHERE clause is the easiest and most straightforward way of accomplishing this. For example, if you were to use one definite value with your WHERE clause, you would only be able to retrieve one piece of data. Through the use of a WHERE clause that is designed to offer more than one result, the script is able to retrieve multiple values from multiple columns or rows, as the examples suggest. A simple > (greater than) operator achieves this very well, as you’ll see in the coming examples.
The examples outline the constructions that can be used to retrieve many pieces of information and store them in several scalar variables. You can use an array in place of the variables, but this is not recommended. Keep your coding as simple as possible and you’ll have the best results.
As always, the data retrieved should be presented in your variables and arrays as it exists in the database. This lends to a clean and concise database model and an easy to understand script. This is also a very easy and simple to implement approach to working with a database.
Select Many Pieces Of Data Into Many Variables
$query = “SELECT col1, col2, col3 FROM this_table WHERE id = 12”;
$sth = $dbh->prepare($query);
$sth->execute();
while (@thisarray = $sth->fetchrow_array()) {
$thisvalue1 = $thisarray[0];
$thisvalue2 = $thisarray[1];
$thisvalue3 = $thisarray[2];
}
In the example, we stated the database query and place it in $query. We then prepared the query for execution by using the prepare() method and executed the query using execute(). Then the fetchrow_array() method was called to place the data in the three variables.
Select Many Database Items Into One Array
Now let’s start using arrays as the end product of our database queries. We’ll begin with the simplest of constructions, pulling many pieces of information from the database and putting them in one array. Since there is only one piece of information to be pulled from the database at one time, the fetchrow() method will be used. The successive database data items are pushed onto an array to achieve the final result, an array that reflects your database column and rows exactly.
The example pulls many rows of data from one table and places the table data in one array. You may then access the data within the array by index numbers or loop through the entire array for one large list. Of note in the example tasks is the id >= 0, which tells MySQL to fetch all of the columns whose id value is greater than or equal to zero. This will essentially pull every row of the table and place the data in the one array, which will be a perfect reflection of what you have in that one database column.
You wouldn’t use fetchrow_array() because we are fetching only one column of the database table. Although we are fetching only one column, the columns will span across many rows. In this situation, the fetchrow_array() method is not needed. The fetchrow() method fits the need very well.
In the examples, it can be seen that every conceivable option for pulling data from the database is explored - the DBI module is a great piece of work to use. You can see from the example tasks that a very large amount of information can be fetched using the methods described. You may then act upon the data as you see fit. With a database call like this one, you are conceivably working with a very large amount of data. Constructing a list is a simple affair of looping through the index items with a foreach or while statement.
Select Many Pieces Of Information Using A WHERE Clause
$query = “SELECT col1 FROM this_table WHERE id >= 0”;
$sth = $dbh->prepare($query);
$sth->execute();
while ($thisvalue = $sth->fetchrow()) {
push @thisarray, $thisvalue;
}
We stated the database query and placed it in $query. We then called the prepare() method to prepare the $query for execution. The execute() method was used to execute the database call, and we used a while loop to iterate through the table columns, pushing the array for each iteration.
With Perl and MySQL it is entirely possible to select a large amount of information in one database query. You eventually want to make some very large lists of information that reflect many columns and rows of the database. Like I said before, you want your arrays and variables to reflect the contents of your database.
Because the script makes multiple calls to the database, it is a necessity to keep things clear and simple by reflecting the database structure in your variables and arrays. For each row of the database, there is a corresponding set of arrays that represent the table columns. This simple yet effective method of reflecting the database structure in your variables and arrays is a good programming convention to stick to. It will make your more complicated future scripts much simpler to follow and will allow a much more complicated database model for future machinations.
The coming examples show how to put the data from many table rows in columns that reflect the structure of the database. You can see that the data retrieved is placed in three different arrays to be acted upon. The fetchrow_array() method of the DBI.pm module is used to perform the multi-column query. Each column of the database is reflected in the arrays that follow, one array to each column. The rows are structured as the index items of each successive array.
Select Many Items To Many Arrays
$query = qq{SELECT col1, col2, col3 FROM $this_table WHERE id >= 0 ORDER BY id DESC};
$sth = $dbh->prepare($query);
$sth->execute();
while (@this_data = $sth->fetchrow_array()) {
push @col1, $this_data[0];
push @col2, $this_data[1];
push @col3, $this_data[2];
}
We constructed a query, in this case, the query is for three items - col1, col2, and col3. We prepare the query using the DBI.pm prepare() method. This reviews the query for syntactical correctness. We then execute the query using execute(). This sends the query to the database. Then we gathered the data using the DBI.pm fetchrow_array() method. The data is now contained in @col1, @col2, and @col3, and matches the database columns and rows exactly.
You may order your query by the aforementioned AUTO_INCREMENT id column without selecting the id column into a variable or array – MySQL does the work for you. You don’t even have to select the id column, just reference it in your query and MySQL will act accordingly. This has the benefit of keeping the number of variables or arrays at a manageable number and simplifies the script as a whole. The id column is present in every hypothetical table that we’ll be using and is the primary key of the table.
The AUTO_INCREMENT id column is present in every table that we’ll be using because it is such a handy way to refer to the contents of the table rows and columns. Using this method will ensure that you have the fastest and most capable queries possible with this combination of Perl, MySQL, and Apache.
Inserting Many Database Items From One Array
Using an array to insert your database items is a bit more complicated, but is still a fairly simple task. Using a $count that increments with every iteration of the loop, it is possible to dynamically insert as many rows as you have a need for.
You can see how from the examples that looping through the array’s index items is an easy thing with Perl. The $count variable keeps track of the index items for you so the database column exactly reflects the contents of @this_column. With each iteration of @this_ column, the value of $this_column[$count] changed and was loaded into the database. We used the simple one column MySQL statement and Perl code introduced in a previous section of this chapter.
While it is possible to use a while loop in your constructions to achieve the same result, the efficiency and simplicity of the foreach loop with a $count variable is a better choice. There is less to do with a foreach loop, since all we’re doing is iterating through the array’s index items and incrementing a count. The foreach loop automatically loops through the entire array and stops when it has reached the end of the array. With a while statement, you would have to have a test be performed to see if the $count matches the number of index items in the array. This slows things down, just a bit.
A few words must be given to system resources. Because this method of inserting table rows is a very powerful one, it is possible to overwhelm your server by using an array that contains more data than can be handled successfully and in a timely fashion by the server. You wouldn’t use a construction such as this to insert thousands of table rows with each piece of data having many kilobytes of information. The resulting operation, while still safe, might leave you with an overwhelmed server.
Use A foreach Loop To Insert From An Array
$count = “0”;
foreach (@this_column) {
$query = “INSERT INTO this_table (col1) VALUES (‘$this_column[$count]’)”;
$sth = $dbh->prepare($query);
$sth->execute();
++$count;
}
We declared a count, and declared a foreach loop that will iterate until the length of @this_column is reached. We then declared the query and put it in $query. The query was then prepared for execution using prepare(). We executed the query using execute() and incremented the $count for the next iteration of the loop.
Inserting Many Database Items From Many Arrays
So we’ve explored single column, single array additions to your database. The introduction of more arrays into the equation isn’t a bad thing, we’re just starting to learn the tremendous flexibility of this combination of programming tools.
You can see in the example tasks that for each element of @col1, we looped through the index items of three arrays (@col1, @col2, and @col3) due to the iteration of $count. The example assumes you have three arrays of equal length for each table column. The rows are inserted one at a time and perfectly reflect the array data. You might have noticed my change in syntax compared to the previous example. We’ve accomplished the exact same thing - with Perl you can break up the more complicated code into more readable forms. This is mine. You’ll find yours.
For each iteration of the loop, more than one array is being referred to. This results in a potentially massive amount of data being inserted into the database. Be sure to limit yourself to 3 or 4 hundred iterations, although more can be used if the amount of data is fairly small for each insertion. You may have as many row and column insertions as you need – this construction is infinitely scaleable. Use a LIMIT declaration to limit the amount of database rows to be created.
You may use either a foreach loop or a while loop to achieve the same result. Use a while loop when you have a specific number of rows to be inserted. That is, if you know beforehand how many rows are to be inserted, then use while. If you have a varying amount of database rows to be inserted, use the foreach loop to iterate through your gathered arrays.
Use A foreach Loop To Insert Many Items From Many Arrays
$count = “0”;
foreach (@col1) {
$query = qq{INSERT INTO this_table (
col1,
col2,
col3
) VALUES (
‘$col1[$count]’,
‘$col2[$count]’,
‘$col3[$count]’
)}”;
$sth = $dbh->prepare($query);
$sth->execute();
++$count;
}
We declare the $count and set it to zero. The foreach loop was then declared and we used @col1 as the argument. We can use @col1 as the argument because every array is of the same length – no data will be left out of the insertion. We declared the query and stored it in $query. We then prepared the query using prepare(). We executed the query using execute() and incremented the $count for the next iteration of the loop.
You can see how powerful and intuitive are the workings of the Perl DBI module. With it you are able to store and retrieve any data from the database. While we used very simple examples as learning tools, there is essentially no limit on how complicated and capable your future DBI machinations can be. The limit is your skill and imagination – Perl and MySQL are up to the task. |
|
|
|
|
| I do not employ cookies or tracking devices of any kind |
|
|
