Hi Don:
I just finished a mod that allows you to sort on any number of fields. Realistically though it is setup for a maximum of 3 fields. I can outline the changes below ...
In html.cgi add your Select boxes for the Sort and Order fields naming them sb1, so1, sb2, so2, sb3, so3 as in ...
Code:
Sort By (1):
<SELECT NAME="sb1">
<OPTION>---
~; for (my $i =0; $i <= $#db_cols; $i++) { print qq~<OPTION VALUE="$i">$db_cols[$i]</OPTION>\n~ if ($db_form_len{$db_cols[$i]} >= 0); } print qq~
</SELECT>
Sort Order:
<SELECT NAME="so1">
<OPTION VALUE="ascend">Ascending
<OPTION VALUE="descend">Descending
</SELECT><br><br>
Sort By (2):
<SELECT NAME="sb2">
<OPTION>---
~; for (my $i =0; $i <= $#db_cols; $i++) { print qq~<OPTION VALUE="$i">$db_cols[$i]</OPTION>\n~ if ($db_form_len{$db_cols[$i]} >= 0); } print qq~
</SELECT>
Sort Order:
<SELECT NAME="so2">
<OPTION VALUE="ascend">Ascending
<OPTION VALUE="descend">Descending
</SELECT><br><br>
Sort By (3):
<SELECT NAME="sb3">
<OPTION>---
~; for (my $i =0; $i <= $#db_cols; $i++) { print qq~<OPTION VALUE="$i">$db_cols[$i]</OPTION>\n~ if ($db_form_len{$db_cols[$i]} >= 0); } print qq~
</SELECT>
Sort Order:
<SELECT NAME="so3">
<OPTION VALUE="ascend">Ascending
<OPTION VALUE="descend">Descending
</SELECT><br><br>
Changes to db.cgi are in the query sub
1. At the top change the local() definition to be
Code:
local (%sortby1);
local (%sortby2);
local (%sortby3);
2. Replace the code starting with
Code:
if ($key_match | | (!($in{'keyword'}) && !($in{'ma'}))) {
if (exists $in{'sb1'} && exists $in{'sb2'} && exists $in{'sb3'}) {
$sortby1{(($#hits+1) / ($#db_cols+1))} = $values[$in{'sb1'}];
$sortby2{(($#hits+1) / ($#db_cols+1))} = $values[$in{'sb2'}];
$sortby3{(($#hits+1) / ($#db_cols+1))} = $values[$in{'sb3'}];
push (@hits, @values);
}
elsif (exists $in{'sb1'} && exists $in{'sb2'}) {
$sortby1{(($#hits+1) / ($#db_cols+1))} = $values[$in{'sb1'}];
$sortby2{(($#hits+1) / ($#db_cols+1))} = $values[$in{'sb2'}];
push (@hits, @values);
}
elsif (exists $in{'sb1'}) {
$sortby1{(($#hits+1) / ($#db_cols+1))} = $values[$in{'sb1'}];
push (@hits, @values);
}
else {
(($numhits >= $first) and ($numhits <= $last)) and push (@hits, @values);
}
$numhits++; # But we always count it!
}
}
close DB;
# Now we've stored all our hits in @hits, and we've got a sorting values stored
# in %sortby indexed by their position in @hits.
$numhits ? ($db_total_hits = $numhits) : ($db_total_hits = 0);
($db_total_hits == 0) and return ("no matching records.");
# Sort the array @hits in order if we are meant to sort.
if (exists $in{'sb1'}) { # Sort hits on first field.
my ($sort_func, $tmp_func);
$sort_func = "";
$sort_pos = 1;
$sb_num = "sb" . "$sort_pos";
while (exists ($in{$sb_num})) {
$tmp_func = $sort_func eq "" ? "" : "$sort_func" . " | | ";
$sort_func = $tmp_func . &build_sort_func;
$sort_pos += 1;
$sb_num = "sb" . "$sort_pos";
}
$sort_func =~ tr/!/$/; # Replace temporary characters with $
foreach $hit (sort { eval($sort_func); } (keys %sortby1)) {
$first = ($hit * $#db_cols) + $hit; $last = ($hit * $#db_cols) + $#db_cols + $hit;
push (@sortedhits, @hits[$first .. $last]);
}
@hits = @sortedhits;
}
At the end of the file add the subroutine that builds the sort function on the fly...
Code:
sub build_sort_func {
my ($var1, $var2, $preprocess_func, $output, $sort_order,
$sort_order_var, $sort_type, $sort_var, $sort_comp);
$sort_order_var = "so" . "$sort_pos";
$sort_order = "$in{$sort_order_var}";
$sort_type = $db_sort{$db_cols[$in{$sb_num}]};
$sort_var = "!sortby" . "$sort_pos";
if ($sort_type eq "alpha") {
$sort_comp = ") cmp ";
$preprocess_func = "lc(";
}
elsif ($sort_type eq "date") {
$sort_comp = ") <=> ";
$preprocess_func = "&date_to_unix(";
}
else {
$sort_comp = ") <=> ";
$preprocess_func = "(";
}
if ($sort_order eq "ascend") {
$var1 = "!a";
$var2 = "!b";
}
else {
$var1 = "!b";
$var2 = "!a";
}
$output = "$preprocess_func" . "$sort_var" . "{$var1}$sort_comp" .
"$preprocess_func" . "$sort_var" . "{$var2})";
return $output;
}
Example code at
oilandgasreserves.com/cgi-bin/dbman/db.pl Excuse my beginner Perl. Hope it helps.
------------------
Don Mitchinson