Re: sorting a database table with primary keys

First, why not use the power/native utility of the database to sort w/an ORDER BY clause? (see second code snippet for setting $field)

my $sql = "SELECT bug_id, owner, subject, status, assigned FROM BUGS O
+RDER BY $field";
[download]

Second, i personally would prefer selectall_hashref .. simplifies the database retrieval, and makes the sorting clearer and trivial .. (also just a hash works instead of a "switch")

my $bugTable = $dbh->selectall_hashref("SELECT bug_id, owner, subject,
+ status, assigned FROM BUGS");
my %numSorts = ( 1=>'bug_id' );
my %strSorts = ( 2=>'owner', 3=>'subject', 4=>'status', 5=>'assigned' 
+);
my $input = <STDIN>;
chomp $input;
my @srtArr = keys %$bugTable;
if( exists $strSorts{$input} ){
  my $k = $strSorts{$input};
  @srtArr = sort { $bugTable->{$a}->{$k} cmp $bugTable->{$b}->{$k} } @
+srtArr;
}elsif( exists $numSorts{$input} ){
  my $k = $numSorts{$input};
  @srtArr = sort { $bugTable->{$a}->{$k} <=> $bugTable->{$b}->{$k} } @
+srtArr;
}
print " @{$bugTable->{$_}} \n" for @strArr;
[download]

Comment on Re: sorting a database table with primary keys Select or Download Code

Replies are listed 'Best First'.
Re^2: sorting a database table with primary keys by pengwn (Acolyte) on Apr 05, 2006 at 09:27 UTC
thanks davidrw. here $choices{$input} mean 1, 2, 3, 4 or 5 right?	[reply]
Re^3: sorting a database table with primary keys by davidrw (Prior) on Apr 05, 2006 at 12:29 UTC
yes .. sorry about that -- I originally had `my %choices = (1 => bug_id, 2=>'owner', 3=>'subject', 4=>'status', 5=>'assigned' );` but broke it into `%numSorts` and `%strSorts` so that `cmp`/`<=>` could be used accordingly, and just didn't copy/paste all the way through... The code in my above reply has been updated ..	[reply] [d/l] [select]
Re^2: sorting a database table with primary keys by Anonymous Monk on Apr 04, 2006 at 05:06 UTC
Because of his itch hands, that's all. It is a good practice for certain people, so don't stop them from doing that. There always some people hands itch, not a bad thing, not at all. On the other hand, let db do the sort isnot always the solution. For example order by doe snot play with things like limit in some db implementation. In those cases, you are forced to do the order by yourself. Of course nested query is also a solution. Voila.	[reply]