Dec
02
2011
How To Sort Columns of MySQL Data on a Web Page With Perl
Posted by Tony Darnell on Fri 02 Dec 2011 11:00 UTC
Tags:

A friend of mine was building a web site so his customers could view his current inventory of transportation containers, and he asked me for help on how to sort the rows of information that appeared on his site. So, in this post, I will give you a quick example on how to sort columns on a web page.

First, let’s start with an inventory database that we will build in MySQL:

CREATE TABLE `inventory` (
`id` int(6) NOT NULL AUTO_INCREMENT,
`item_name` varchar(30) NOT NULL,
`item_SKU` varchar(20) NOT NULL,
`item_cost` decimal(4,2) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=100000 DEFAULT CHARSET=latin1

Next, here are some SQL statements to populate the MySQL database with some sample data.

use inventory;
insert into inventory (item_name, item_SKU, item_cost) values ('Apple', '10001', '1.04');
insert into inventory (item_name, item_SKU, item_cost) values ('Peach', '10004', '1.28');
insert into inventory (item_name, item_SKU, item_cost) values ('Plum', '10301', '1.17');
insert into inventory (item_name, item_SKU, item_cost) values ('Apricot', '13033', '1.92');
insert into inventory (item_name, item_SKU, item_cost) values ('Grapes', '20422', '1.34');
insert into inventory (item_name, item_SKU, item_cost) values ('Kiwi', '98561', '2.78');
insert into inventory (item_name, item_SKU, item_cost) values ('Mango', '56231', '0.99');
insert into inventory (item_name, item_SKU, item_cost) values ('Strawberry', '24689', '1.52');
insert into inventory (item_name, item_SKU, item_cost) values ('Banana', '65213', '0.39');
insert into inventory (item_name, item_SKU, item_cost) values ('Tangerine', '47112', '1.22');

For this example, I am not going to show you how to create a new record, edit or delete your information in a MySQL database, as I did that in an earlier post.

Now that we have our data, we are going to need a Perl script that will retrieve all of the data and create our web page at the same time. I have named the Perl script inventory_view. Don’t forget to change all of the variables to match your system. I will explain the variables that we will use in the Perl Script after the script below:

#!/usr/bin/perl
#--------------------------------------------------------------------------
# inventory_view.pl
#--------------------------------------------------------------------------

use DBI;
use DBD::mysql;
use CGI qw(:standard);

my $Database = "scripting_mysql";

# get the sort parameters from the URL
my $query = new CGI;
# $sort is the order by which to sort - ascending or descending
# you only need to set a value if it is descending (desc)
$sort = $query->param("sort");
# $sortby is the field name to be used for sorting
$sortby = $query->param("sortby");

# check for sort order - ascending or descending
if ($sort =~ "asc")

{
$order_by = "order by $sortby";
}

else

{
$order_by = "order by $sortby $sort";
}

# if the sort isn't set to a value, set it to a default sort of item_name
if (length($sort) < 1)

{
$order_by = "order by item_name";
}

# print HTML header
print header;

# connect to the database and pull every record
$dbh = ConnectToMySql($Database);
$query = "select item_name, item_SKU, item_cost from inventory $order_by";
$sth = $dbh->prepare($query);
$sth->execute();

# print the table header
print <<HTML;
<table border=0>
<tr>
<td style="text-align:center;border:1px solid gray;padding: 5px 10px 5px 10px;"><a href=http://192.168.1.2/cgi-bin/scripting_mysql/inventory_view.pl?sort=asc&sortby=item_name><img alt="sort ascending" title="sort ascending" height=15 src=http://tonydarnell.com/mysql_blog//arrow_blue_up.png></a>  <a href=http://192.168.1.2/cgi-bin/scripting_mysql/inventory_view.pl?sort=desc&sortby=item_name><img alt="sort descending" title="sort descending" height=15 src=http://tonydarnell.com/mysql_blog//arrow_blue_down.png></a></td>
<td style="text-align:center;border:1px solid gray;padding: 5px 10px 5px 10px;"><a href=http://192.168.1.2/cgi-bin/scripting_mysql/inventory_view.pl?sort=asc&sortby=item_SKU><img alt="sort ascending" title="sort ascending" height=15 src=http://tonydarnell.com/mysql_blog//arrow_blue_up.png></a>  <a href=http://192.168.1.2/cgi-bin/scripting_mysql/inventory_view.pl?sort=desc&sortby=item_SKU><img alt="sort descending" title="sort descending" height=15 src=http://tonydarnell.com/mysql_blog//arrow_blue_down.png></a></td>
<td style="text-align:center;border:1px solid gray;padding: 5px 10px 5px 10px;"><a href=http://192.168.1.2/cgi-bin/scripting_mysql/inventory_view.pl?sort=asc&sortby=item_cost><img alt="sort ascending" title="sort ascending" height=15 src=http://tonydarnell.com/mysql_blog//arrow_blue_up.png></a>  <a href=http://192.168.1.2/cgi-bin/scripting_mysql/inventory_view.pl?sort=desc&sortby=item_cost><img alt="sort descending" title="sort descending" height=15 src=http://tonydarnell.com/mysql_blog//arrow_blue_down.png></a></td>
</tr>

<td style="text-align:center;border:1px solid gray;padding: 5px 10px 5px 10px;font-size:12px;">Item Name</td>
<td style="text-align:center;border:1px solid gray;padding: 5px 10px 5px 10px;">Item SKU</td>
<td style="text-align:center;border:1px solid gray;padding: 5px 10px 5px 10px;">Item Price</td>

HTML

# set the second line background color
$background_color = "#FFFFFF";

# loop through the data fetched from the query
while (@data = $sth->fetchrow_array()) {

$item_name = $data[0];
$item_SKU = $data[1];
$item_cost = $data[2];

# alternate the background colors
if ($background_color =~ "#FFFFFF")

{
$background_color="#FFFFCC";
}

else

{
$background_color="#FFFFFF";
}

# print the table rows, one for each item from the database
print <<HTML;

<tr bgcolor="$background_color">
<td style="text-align:right;border:1px solid gray;padding: 5px 10px 5px 10px;font-size:12px;"</td>$item_name </td>
<td style="text-align:right;border:1px solid gray;padding: 5px 10px 5px 10px;font-size:12px;"</td>$item_SKU </td>
<td style="text-align:right;border:1px solid gray;padding: 5px 10px 5px 10px;font-size:12px;"</td>$item_cost </td>
</tr>
HTML
}

# close the table
print "</table>";

exit;

# more on what I am doing with the accessSM file may be found at:
# http://scriptingmysql.wordpress.com/2011/07/27/connecting-to-mysql-with-perl/
#----------------------------------------------------------------------
sub ConnectToMySql {
#----------------------------------------------------------------------

my ($db) = @_;

open(PW, "<..\/accessSM") || die "Can't access login credentials";
my $db= <PW>;
my $host= <PW>;
my $userid= <PW>;
my $passwd= <PW>;

chomp($db);
chomp($host);
chomp($userid);
chomp($passwd);

my $connectionInfo="dbi:mysql:$db;$host";
close(PW);

# make connection to database
my $l_dbh = DBI->connect($connectionInfo,$userid,$passwd);
return $l_dbh;

}

When you run the script for the first time in a browser, you should see something like this:

In our Perl script, we are using the $query variable to select the three columns from our MySQL database – item_name, item_SKU and item_cost. And we will decide which one to sort by with the variable $sortby. And we will assign ascending or descending order to the variable $sort. This will give us a total of six options for sorting columns and in which order:

$sortby $sort
item_name ascending order
item_name descending order
item_SKU ascending order
item_SKU descending order
item_cost ascending order
item_cost descending order

To build the URL for each, we simply use the script name – inventory_view.pl – and add the variables $sortby and $sort along with their values, using an ampersand for the delimiter.

inventory_view.pl?sortby=[column_name]&sort=[asc or desc]

Example:

inventory_view.pl?sortby=item_name&sort=asc

We will then use an image of an arrow pointing upwards (for ascending order) and downwards (for descending order). Here is an example of the HTML for “sort by item_name and ascending order”: (don’t forget to add the full path names for both the location of the Perl script and the location of your image file)

<a href="cgi-bin/inventory_view.pl?sortby=item_name&sort=asc"><img src="images/arrow_blue_up.png"></a>

You simply have to build a link for each of the six sorting options (two per database column) to correspond to each of the blue up/down arrows. All six links would look like this:

<a href="cgi-bin/inventory_view.pl?sort=asc&sortby=item_name"><img alt="sort ascending" title="sort ascending" height="15" src="images/arrow_blue_up.png"></a>
<a href="cgi-bin/inventory_view.pl?sort=desc&sortby=item_name"><img alt="sort descending" title="sort descending" height="15" src="images/arrow_blue_down.png"></a>

<a href="cgi-bin/inventory_view.pl?sort=asc&sortby=item_SKU"><img alt="sort ascending" title="sort ascending" height="15" src="images/arrow_blue_up.png"></a>
<a href="cgi-bin/inventory_view.pl?sort=desc&sortby=item_SKU"><img alt="sort descending" title="sort descending" height="15" src="images/arrow_blue_down.png"></a>

<a href="cgi-bin/inventory_view.pl?sort=asc&sortby=item_cost"><img alt="sort ascending" title="sort ascending" height="15" src="images/arrow_blue_up.png"></a>
<a href="cgi-bin/inventory_view.pl?sort=desc&sortby=item_cost"><img alt="sort descending" title="sort descending" height="15" src="images/arrow_blue_down.png"></a>

When the inventory_view.pl script is executed the first time, we have not set the $sortby column or $sort order, so it will default to sorting by item_name and ascending order (which really doesn’t have a value, as ascending order is the default sort order). Each time a sort request is made, a new connection will be made to the database, so that is something to consider when you decide which columns you want to be available to sort. And we aren’t putting a limit on the number of rows that are retrieved, so that you have multiple pages of items – but I will try to cover that in a future post.

While there are other solutions that don’t require you to hit the database each time, this should give you a quick (and dirty) way to sort columns of information in a table on a web page.

 

Tony Darnell is a Principal Sales Consultant for MySQL, a division of Oracle, Inc. MySQL is the world’s most popular open-source database program. Tony may be reached at info [at] ScriptingMySQL.com and on LinkedIn.