Sorting a table

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

I have a table, with about 50 rows and 9 columns. Some of the columns contain words, others just numbers.

What is the best way of displaying this data in a web page, where I can sort all the data by clicking on the column header? Ideally, clicking once on the name of the column would sort ascending, and again would sort descending. The same way programs, like WMP or Explorer, work.

If there is a simple way to do the sorting that would be great, if not, I have done that in a horribly long section of code (which I would prefer to get rid of). The main problem is getting the data to sort ascending AND descending by clicking on the column name.

One column needs to be sorted in an order I will define, not alphabetically.
ie: name1, name2, name3, name4
sorts to: name 2, name 4, name 3, name1
(and then backwards for descending)

Again, I have done this already but my method is ugly and long. This is for the ranks, as below. Each &display_rank scans through the whole list of people (~50) so isn't very efficient.

I am using 'CGI qw/:standard/', so ideas ideally in that format, but that's not necessary.

Here is my sorting function:

sub show {

  my ($order, $option) = @_;

  if ($option eq 'rank_title' && $order eq 'ascending') {
    &display_rank('Leader');
    &display_rank('Chairman');
    &display_rank('Lieutenant');
    &display_rank('Veteran');
    &display_rank('Rookie');
    
  } elsif ($option eq 'rank_title' && $order eq 'descending') {
    &display_rank('Rookie');
    &display_rank('Veteran');
    &display_rank('Lieutenant');
    &display_rank('Chairman');
    &display_rank('Leader');
    
  } elsif ($option eq 'username') {    
    my $user;
    if ($option eq 'ascending') {
      $hoa{$user}{$a} <=> $hoa{$user}{$b};
    } elsif ($option eq 'descending') {
      $hoa{$user}{$b} <=> $hoa{$user}{$a};
    }
    
  } else {
    &display($order, $option);
  }
}
[download]

Looks horrible IMO. I wrote the script last night and am having trouble understanding it now.

Oh and the data structure is as follows:

A 2D hash contains all data. %hoa
Each element (is that the correct term?) is the name of the user and is another hash. $hoa{$user}
Each of these hashes contains 8 elements. $hoa{$user}{$option}

The data needs to be sorted by any of the 8 elements and the user element.

Any help or ideas would be greatly appreciated!

Comment on Sorting a table Select or Download Code

Replies are listed 'Best First'.
Re: Sorting a table by Fletch (Bishop) on Jul 02, 2008 at 18:13 UTC
You can do this server side by making links that define calls which call back to your script with a parameter telling the sort key and perhaps a direction. You'd then use that sort key to sort the table into a temporary AoA which you'd use to output your table. Another alternative which you might want to look into (if it's acceptable to use Javascript) is any of the umpteen bazillion JS live table packages (see for example Yahoo's YUI). These have the advantage of sending the page one time and then the sorting takes place client side rather than requiring a server round trip. You could also use JQuery to whip up your own if you need fancier behavior that an off the shelf solution doesn't provide. An unrelated note: don't prefix subroutine calls with an &; it has a special meaning which while you're probably not going to get into trouble here could cause headaches down the road if you get into a bad habit. The cake is a lie. The cake is a lie. The cake is a lie.	[reply]
Re^2: Sorting a table by pc0019 (Acolyte) on Jul 03, 2008 at 01:11 UTC
After a quick crash course in JS, I managed to get my perl script outputting a good looking table using the YUI :D I always wanted to start learning the language but never got round to it. Thanks for the code ikegami but I think I will do this bit in JS. I'll be taking my questions to a JS forum now!	[reply]
Re: Sorting a table by ikegami (Patriarch) on Jul 02, 2008 at 19:31 UTC
You could do something like the following: my %users; my %rank_order = ( Leader => 1, Chairman => 2, Lieutenant => 3, Veteran => 4, Rookie => 5, ); sub by_hi_rank { $rank_order{$users{$a}{title}} <=> $rank_order{$users{$b}{title} +} \|\| $a cmp $b } sub by_lo_rank { $rank_order{$users{$b}{title}} <=> $rank_order{$users{$a}{title} +} \|\| $a cmp $b } my %sorters = ( by_rank => { ascending => \&by_hi_rank, descending => \&by_lo_rank, }, by_user => { ascending => sub { $a cmp $b }, descending => sub { $b cmp $a }, }, ); my $sorter = $sorters{$criteria}{$order} or die; my @sorted_users = sort { $sorter->() } keys %users; [download]	[reply] [d/l]
Re: Sorting a table by friedo (Prior) on Jul 02, 2008 at 18:07 UTC
IMHO it's much easier to handle this sort of thing on the client side. There are a number of Javascript libraries that make automatically sorting your tables extremely easy, and as a bonus it doesn't require a roundtrip to your web server. I've heard good things about the Standardista library but I haven't tried it myself.	[reply]