Essential Perl & MySQL Skills
Learn to operate at a professional level with Perl & MySQL |
 By Thomas Valentine
Viewed 12345 Times
Posted Sept. 18, 2024 |
|
Before we start delving into the scripts that make up the project for this book, we should first cover a few bases. In this chapter, we'll review the skills necessary in order to make sense of what is to come. You may already be familiar with these concepts, but it is always a good idea to review a skill before having to apply it to a live, functioning script.
In this chapter, we'll work through the functions that will be used in the project. We'll cover only those functions that will be used in the project – we won't cover all of the abilities that the DBI module offers, for example.
MySQL Column & Index Types
Within MySQL are many different types of data that you can tailor your database tables to accept. Everything from common text characters to binary information may be provided for.
Column types are declared upon creation of a table. There are many different types, and we'll cover them all in the sections to come. While we won't be using every column type in the project, it is of paramount importance that you know and understand the full breadth of what is available. This knowledge will come in handy if you plan on expanding the project, a full featured bulletin board.
Integer Column Types
We'll start with the integer column types. The integer column types are a numeric type of column and are used to store numbers only – if you try to insert text, MySQL will throw an error. Examine Table 4.1:
Integer Column Types, Signed Ranges
TINYINT
SMALLINT
MEDIUMINT
INT or INTEGER
BIGINT Signed Range
-128 to 127
-32768 to 32767
-8388608 to 8388607
-2147483648 to 2147483647
-9223372036854775808 to 9223372036854775807
You can see in this preceeding table that there is a very wide range of integer types. Table 4.1 shows the full range of signed numbers available. By signed I mean the negative numbers are represented. Notice that the positive number is one less than the negatively signed number. This is because the number zero is still considered an integer. Zero is a value – it is not to be confused with the null value, which is considered to be an absence of value.
Table 4.2 shows the unsigned range of integer values. Unsigned values are those that are positive. Negative numbers are not a possibility.
Integer Column Types, Unsigned Ranges
TINYINT
SMALLINT
MEDIUMINT
INT or INTEGER
BIGINT Unsigned Range
0 to 255
0 to 65535
0 to 16777215
0 to 4294967295
0 to 184467444073709551615
It should be noted that a declaration flag of UNSIGNED must be included in your table creation in order to use the unsigned range of the column type you're using. Otherwise the signed range is used.
Floating Point Column Types
A floating point column type is simply a column type that includes a decimal point. The decimal point may "float" from one position to another, hence the floating point name. Examine Table 4.3:
Floating Point Column Types
FLOAT
DOUBLE or
PRECISION or REAL
1.175494351e-38 to 3.402823466E+38
2.2250738585072014E-308 to 1.7976931348623157E+308
Floating point numbers are usually used when the range of numbers from an integer column type isn't high or low enough for the task at hand. It should be noted that floating point numbers are approximate values – MySQL will round the floating point number to the number of decimal places you define when the table is created.
Character String Column Types
Character strings are either CHAR or VARCHAR. CHAR stands for "Characters" and VARCHAR stands for "Varying Characters". A fixed length for the column is set during table creation. If the data is not as long as the length of the declared CHAR or VARCHAR, the space remaining is padded with spaces. It should be noted that if the value you've inserted has trailing spaces by design, the spaces will be removed. You'll have to provide for this within the Perl script in order to add the trailing spaces.
If you need a case-sensitive CHAR or VARCHAR column, set this with the BINARY flag. If the BINARY flag is present, the column is case sensitive when used for sorting or comparison. Otherwise, strings are case insensitive.
BLOB Column Types
BLOB column types are of varying width. BLOB stands for Binary Large Object. They are able to store very large data sets and don't trim trailing spaces. A BLOB column is case sensitive. Values that exceed the maximum length are simply truncated. BLOBs cannot have default values. Examine Table 4.4:
BLOB Coumn Types
TINYBLOB or TINYTEXT
BLOB or TEXT
MEDIUMBLOB or MEDIUMTEXT
LONGBLOB or LONGTEXT 255 Bytes
64 KBs (65535 bytes)
16 MBs (16777215 bytes)
4 GBs (4294967295 bytes)
You might have noticed that a column type of TEXT is present in Table 4.4. TEXT columns are of the same size as binary BLOBs and follow the same basic set of rules, with the exception being that the column is not of binary data.
Enumeration or Set Column Types
Enumerations and sets are string columns that have a small set of possible values. If no value is declared upon table creation, an empty string will be inserted. The ENUM column type has a maximum value of 64 KBs (65535 bytes) and the SET column type has a maximum value of 64 bytes.
To insert a value in an ENUM column, use a string literal. That is, encase the string within quotes. To insert multiple values in a SET column, separate the values within the opening and closing quotes with commas.
Time Column Types
MySQL provides a wide array of date and time stamps. The time is taken from the operating system of the server the MySQL database resides upon.
MySQL is able to use time and date stamps in an ORDER BY clause, so you're able to sort your data by both date and time. This great feature comes in handy if you're looking to order the contents of a table by date or time or both. Examine Table 4.5:
Date & Time Column Types
DATE
DATETIME
TIME
TIMESTAMP
YEAR
1000-01-01 to 9999-12-31
1000-01-01 00:00:00 to 9999-12-31 23:59:59
-838:59:59 to 838:59:59
1970-01-01 00:00:00 to 2037-12-31 23:59:59
1970 to 2069 or 1901 to 2155
All time and date values are integers. If, for some reason, you should have a floating point number as part of a date, MySQL will round to the nearest value of the following: month values are limited to 1 to 12. Day values are limited to 1 to 31. Hour values are from 0 to 23 while minutes and seconds range in value from 0 to 59.
Perl Functions Review
Reviewing a skill, no matter its depth, is always a good thing as you will always come away with a new vantage point. In this section, we'll be reviewing the specific skills that will be used in the project which we'll begin working on in the next chapter.
There is always more than one way to do something with Perl, syntactally speaking. Because of this I'll try to present each topic in this section in more than one way.
Creating Tables
Creating a table is a straight forward affair, but there are a few things that you need to know first. We'll use the three step method to create our tables. We visited this three step method in Chapter 2.
When creating a table, you will first need to know the column types that you'll be using. You will also need to know the names of the columns. Column names are case-sensitive, remember, so name your columns accordingly.
In the project to come, the primary key is the first column, named "id". It is an auto increment column, as you can see in the following example of a table creation:
$query = qq{CREATE TABLE $thisthread (
id INT AUTO_INCREMENT PRIMARY KEY,
title VARCHAR (250) NOT NULL,
threadid VARCHAR (20) NOT NULL,
posts VARCHAR (20) NOT NULL,
date VARCHAR (20) NOT NULL
)};
$sth = $dbh->prepare($query);
$sth->execute();
As you can see, creating a table is a simple but very powerful function. The id column is an integer and is the primary key of the table. The other VARCHAR columns range in size from 20 to 250 characters. The 20 character limited columns are numbers and the date, all of which will not exceed 20 characters in length. The title column is set to 250 characters, as it is the title of a thread. The maximum character count (defined in a different script within the project) is 250 characters because it is a descriptive column of the table – the thread name may be up to 250 characters. You'll see this table creation in putnewthread.cgi within the project we'll soon be working on, so I won't show any more of the code for now. Please focus on the table structure within the Perl code.
Loops
One of the most common functions we'll be using in the project are simple loops. We'll be constructing lists that are accrued via the use of a loop that selects data from the database and formats it into an HTML segment. This segment of HTML and the data from the database is then added to the final results and is displayed in the user's browser.
There are two forms of loops used in the project – the foreach() loop and the while() loop. Each is used in slightly different ways and locations, as you'll see below.
The while() Loop
The while() loop is used to select one piece of data at a time. If there is only one result, it is placed in a scalar variable. If there are multiple results, the data is pushed onto an array, as follows:
$query = "SELECT id FROM $thistopic WHERE id >= 0 ";
$sth = $dbh->prepare($query);
$sth->execute;
while ($thisvalue = $sth->fetchrow()) {
push @theseids, $thisvalue;
};
The while() loop iterates until there are no more results being pushed onto the array by MySQL. Note that the primary key, id, is being selected. You'll see a lot of that in the scripts to follow.
The foreach() Loop
We use the foreach() loop mostly to construct the final HTML markup that will be displayed to the user, although that is not the only place it is used. It can also be used to loop through table rows via a previous database call. Using the id column as a means to limit your database queries is a good place to use a foreach() loop, as follows:
$count = "0";
foreach (@theseids) {
$query = qq{SELECT title, threadid, posts, date
FROM $thistopic
WHERE id = $theseids[$count] LIMIT 30};
$sth = $dbh->prepare($query);
$sth->execute();
while (@thisvalue = $sth->fetchrow_array()) {
push @titles, $thisvalue[0];
push @threadids, $thisvalue[1];
push @posts, $thisvalue[2];
push @date, $thisvalue[3];
}
++$count;
};
This code snippet was taken from threads.cgi, starting on line 233. It shows a foreach() loop that will iterate as long as there is a value (a previously gathered array based on the id primary key) within @theseids. You can also see that a while() loop is being used to gather data from the database based on the $theseids[$count] array. We'll delve further into the workings of a construction such as this in a later chapter. For now this basic construction has been an example of what is to come in terms of loops.
Push An Array
By "push" an array I mean that a value is being added to the end of an array. An array can be likened to a list, with each list item being given an index number, starting at zero. Every time you push an array you're adding to the end of the list. The syntax for pushing an array is as follows:
push @thisarray, $thisvariable;
The scalar variable $thisvariable is being added to the end of the list @thisarray. Simple. We'll be using this snippet of code in almost every script within the project, so it is important that you understand the process now, before we start examining code in great detail.
Gathering Content
Every script we'll be using is a collection of individual sets of data that have been formatted into HTML markup. In collecting the data, you do so in a fashion that is conducive to being easily formatted. What is usually done once the data is collected is the data is placed in the logically ordered HTML markup, which can be text within a table or anchor tag links, although you are not limited to just those two options. Examine the following snippet of code:
$count = "0";
foreach (@these_ids) {
$oneContentElement = qq{<TR><TD ALIGN="CENTER" VALIGN="TOP">
<A HREF= "/cgi-bin/this_script.cgi?id=$id[$count]& id2=$id2[$count]">$thislink[$count] </A></TD></TR>};
push @endarray, oneContentElement;
++$count;
};
First a $count is declared and is set to zero. We then initiate a foreach() loop using @these_ids as the argument. The newly declared scalar variable, $oneContentElement, is stuffed with some HTML markup. Notice that there are three separate and distinct arrays being used (@id, @id2 and @thislink). Each array's index items are used as the unique data for every iteration of @these_ids. Since the $count is being incremented every time the loop iterates, the data within each of the three arrays (three links) changes and is pushed onto @endarray.
It is the entire HTML markup that is pushed onto @endarray that is to be displayed in the user's browser. This example containes only the code for three links. In the project to come, we'll be adding images, links and other useful page elements to the mix.
Ordering Your Arrays – Perl reverse() vs. MySQL ASC or DESC
In working with an array it is sometimes favorable to reverse it and apply formatting to the data that has been reversed. There are three options available – one via Perl and two via MySQL.
The Perl reverse() function is one such option. You would apply the reversal to every array that you are working with. This takes MySQL out of the loop, and is sometimes favorable in situations where you've already retrieved data from the database.
Use the reverse() method as follows:
@this_new_array = reverse(@this_array);
You can see how easy it is to use the reverse() function. It is equally easy to use the ASC (ascending) and DESC (descending) clauses via MySQL. Examine the following examples:
$query = qq{SELECT fieldone, fieldtwo FROM this_table
ORDER BY id ASC};
Or
$query = qq{SELECT fieldone, fieldtwo FROM this_table
ORDER BY id DESC};
Each MySQL statement uses the id primary key to organize the data returned – what direction the data is actually retrieved from is a function of the ASC or DESC clauses.
DESC orders the information in a descending manner. That is, the data that is at the top of the table is also the oldest. A descending order returns the data organized from the oldest data on the top of the list to the newest data on the bottom of the list. The ASC clause places the oldest data at the bottom of the list, leaving the newest data at the top of the list.
There are pros and cons for all three methods of ordering your data. While the Perl reverse() function is useful, it is the slowest of the three methods. The ASC and DESC methods are faster, since MySQL is optimized for this kind of operation and is already memory resident. What method to use is entirely up to you – tailor to your situation.
Links & Parameters
We'll be using loaded links extensively in the upcoming project sections. A loaded link is one that contains parameters that scripts use to pass on data that is used to retrieve data for the next page. An example of a loaded link is as follows:
<A HREF="/cgi-bin/this_script.cgi?id=1234&id2=5678">This Link</A>
This method of loading links is the GET method, as you'll remember from a previous chapter. The id parameter holds the value 1234 and the id2 parameter holds the value 5678. These parameters and their associated values are what are used to generate the page that is generated by the this_script.cgi script. |
|
|
|
| I do not employ cookies or tracking devices of any kind |
|
|
