Sorting data returned from DBI ...

chinamox has asked for the wisdom of the Perl Monks concerning the following question:

Hello monks,

I am working on a project for an online class involving using a DBI to access a mySOL database and print out the results. Actually I have done all of this but now I am trying to refine how the resulting data is displayed in an html table.

My current Program:

#!/opt/bin/perl -w
#
use strict;
use DBI;
use CGI;

my $cgi = CGI->new();
my $paygroup_num = $cgi->param('paygroup_num');

if ($paygroup_num) {
    print $cgi->header(),
          $cgi->start_html("sample cgi page 1");
    print "<table>";
    
    display_paygroup_page($cgi, $paygroup_num);

    print "</table>";
    $cgi->end_html();
} else {
    show_form($cgi);
};


########
# SUBS BELOW
########

sub display_paygroup_page {
    
    
    my ($cgi, $paygroup_num) = @_;
my $match = pop(@_);
print "<tr><td> City Name </td><td>  Last Name </td><td> First Name </
+td></tr>\n";
    

## Connect to database
my $dbh = DBI->connect('dbi:mysql:adatabase', 'undef', 'undef')
            || die "couldn't connect to database";

## Get the data            
my $sql ='SELECT city, last_name, first_name FROM employees ORDER BY c
+ity';
my $sth = $dbh->prepare($sql);

$sth->execute();

## worked through data 
my($last_name, $first_name, $city, $paygroup_num1);
$sth->bind_columns(\($city, $last_name, $first_name, $paygroup_num1));

while($sth->fetch()){
    if($match =~ $paygroup_num1){
    print"<tr><td> $city </td><td> $last_name </td><td> $first_name </
+td></tr>\n";
    }
}
   
$sth->finish();   
$dbh->disconnect();
};


sub show_form {
    my $cgi = shift;

    print $cgi->header(),
          $cgi->start_html("Please specify an employee number"),
          $cgi->start_form(),
          "Enter an employee pay level number: ", 
          $cgi->textfield(-name => 'paygroup_num'),
          $cgi->submit(),
          $cgi->br(), "(for example: S-32 or H-22)",
          $cgi->end_form(),
          $cgi->end_html();

};
[download]

For example if the proceeding CGI is run, it renders the results as an HTML table. Similar to this one:

__DATA__
City Name Last Name First Name
Boston     Ma       Rebecca
Boston     Kent     Orin
Boston     Austin    James
Newark     Smith     Jessica 
Newark     Clark     Benjamin
Seattle    Jones     Matt
Seattle    Jones     George 



__END__
[download]

Now I want to sort the table so that it is ordered by city, last name, first name instead of just by city.

__DATA__
City NameLast NameFirst Name
Boston     Austin    James
Boston     Kent      Orin
Boston     Ma        Rebecca
Newark     Clark     Benjamin
Newark     Smith     Jessica
Seattle    Jones     George
Seattle    Jones     Matt

__END__
[download]

I can’t really figure out how best to do this sorting and so I am asking if anyone knows of a useful tool inside of DBI that could help me, or failing that, another clever way of sorting the displayed data first by city, then last name and finally first name.

As always, thank you for your time and efforts.

Best,

-mox

Comment on Sorting data returned from DBI ... Select or Download Code

Replies are listed 'Best First'.
Re: Sorting data returned from DBI ... by davido (Cardinal) on Dec 10, 2006 at 06:51 UTC
You shouldn't need to re-sort after the fact. Check your SQL documentation on ORDER BY. The ORDER BY clause allows you to specify multiple search criteria, in order of significance. You're already using ORDER BY, you may as well use it to its full potential. You might reconstruct your ORDER BY clause like this: `.... ORDER BY city, last, first` [download] Check out this online resource for details on ORDER BY. Dave	[reply] [d/l]
Re^2: Sorting data returned from DBI ... by chinamox (Scribe) on Dec 10, 2006 at 07:09 UTC
Thank for the link and uber fast response! That was what exactly what I needed. I was going through the DBI documentation this morning but it had not occurred to me that you could use ORDER BY like that. -mox	[reply]
Re^3: Sorting data returned from DBI ... by davido (Cardinal) on Dec 10, 2006 at 16:48 UTC
In general, if you can push some task off onto the database (out of Perl's hands) you're better off. This is only a general rule of thumb, but it seems to hold in most cases. When in doubt, let the database do the work. In this case, you were already sorting once in the database, and then contemplating sorting again within Perl. That would mean performing two O(n log n) exercises instead of one. Dave	[reply]
Re: Sorting data returned from DBI ... by McDarren (Abbot) on Dec 10, 2006 at 06:54 UTC
"Now I want to sort the table so that it is ordered by city, last name, first name instead of just by city." So, just modify your SQL query to do exactly that.... `my $sql ='SELECT city, last_name, first_name FROM employees ORDER BY c +ity, last_name, first_name';` [download] Simple ;)	[reply] [d/l]
Re^2: Sorting data returned from DBI ... by chinamox (Scribe) on Dec 10, 2006 at 07:13 UTC
I have learned that Perlmonks is an excellent resource for situations where I catch myself thinking "there has got to be a better way to do this..." Thank you for the help, no matter how simple it might have been ;) -mox	[reply]
Re^3: Sorting data returned from DBI ... by Devanchya (Beadle) on Dec 10, 2006 at 14:31 UTC
chinamox, If you really want to delve deeper, and school projects are always great for this if you have the time, look at your program and decide a feature you would like to improve. Looking at the code, rewrite it using the fetchrow_hashref and fetchrow_hasharray DBI option. You can see how they act different from the bind statement, and how Hashref in this case will make easier to work with your data. Right now if you want to add a column to your table, you have to update the binding. What if you add a column to your data, and it just appears in teh hashref automattically. note there is some danger here in making a huge hashref with only 2 varibles you are going to use. If you do this though, need ot check out the actual SQL statement... This is a CGI program, you may want to look at Javascript to allow it to be sorted dynamically on the site. Or you can also look at CGI::AJAX, which are a set of commands to refresh data with out refreshing the whole page. My main point is: Just because the book or professor showed you how to do it one way, does not mean that is the best way.. -- Even smart people are dumb in most things...	[reply]