Gossamer Forum
Home : Products : DBMan SQL : Discussion :

Sorting on multiple fields

Quote Reply
Sorting on multiple fields
Hi

I seem to have ported the sort on multiple fields mod from the flat file version http://www.gossamer-threads.com/...m12/HTML/001080.html.

It's fairly basic code but it seems to work :-)

In html.pl in sub serach options delete the existing sort by stuff and add this (this is based on sorting on upto 6 fields):

Code:
Sort By (1): <select name="sb1">
<option>---</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="ASC">Ascending</option>
<option value="DESC">Descending</option>
</select>



Sort By (2): <select name="sb2">
<option>---</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="ASC">Ascending</option>
<option value="DESC">Descending</option>
</select>



Sort By (3): <select name="sb3">
<option>---</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="ASC">Ascending</option>
<option value="DESC">Descending</option>
</select>



Sort By (4): <select name="sb4">
<option>---</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="so4">
<option value="ASC">Ascending</option>
<option value="DESC">Descending</option>
</select>



Sort By (5): <select name="sb5">
<option>---</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="so5">
<option value="ASC">Ascending</option>
<option value="DESC">Descending</option>
</select>



Sort By (6): <select name="sb6">
<option>---</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="so6">
<option value="ASC">Ascending</option>
<option value="DESC">Descending</option>
</select>
Then in db.cgi sub query comment out this:

Code:
# $in{'so'} ? ($order = $in{'so'}) : ($order = "ASC");
# $in{'sb'} and ($sortby = "ORDER BY $in{'sb'} $order");
and add:
Code:
if ($in{'sb1'}) {
$sortby = "ORDER BY $in{'sb1'}";
if ($in{'so1'}) {
$sortby .= " $in{'so1'}";
}
}
if (($in{'sb1'}) && ($in{'sb2'})) {
$sortby .= ", $in{'sb2'}";
if ($in{'so2'}) {
$sortby .= " $in{'so2'}";
}
}
if (($in{'sb1'}) && ($in{'sb2'}) && ($in{'sb3'})) {
$sortby .= ", $in{'sb3'}";
if ($in{'so3'}) {
$sortby .= " $in{'so3'}";
}
}
if (($in{'sb1'}) && ($in{'sb2'}) && ($in{'sb3'}) && ($in{'sb4'})) {
$sortby .= ", $in{'sb4'}";
if ($in{'so4'}) {
$sortby .= " $in{'so4'}";
}
}
if (($in{'sb1'}) && ($in{'sb2'}) && ($in{'sb3'}) && ($in{'sb4'}) && ($in{'sb5'})) {
$sortby .= ", $in{'sb5'}";
if ($in{'so5'}) {
$sortby .= " $in{'so5'}";
}
}
if (($in{'sb1'}) && ($in{'sb2'}) && ($in{'sb3'}) && ($in{'sb4'}) && ($in{'sb5'}) && ($in{'sb6'})) {
$sortby .= ", $in{'sb6'}";
if ($in{'so6'}) {
$sortby .= " $in{'so6'}";
}
}
It's actually a easier mod than the flat file version because MySQL does to sorting :-)

BTW it's nice being able to set a font for this forum -- Lucida in Netscape in X11 results in it actually being readable without Verdana installed, however stuff in
Code:
[ / pre ] comments doesn't come out in a fixed width font :-(

Chris

--
http://webarchitects.co.uk/
Quote Reply
Re: Sorting on multiple fields In reply to
And condensing that a little:

Code:
my $list;
for (0 .. $#db_cols) {
$list .= "<option value='$_'>$db_cols[$_]" if ($db_form_len{$db_cols[$i]} >= 0);
}
for (1 .. 6) {
print qq~
Sort By ($_): <select name="sb$_"><option value="">---$list</select>
Sort Order: <select name="so$_"><option value="ASC">Ascending<option value="DESC">Descending</select>


~;
}
Then in db.cgi sub query comment out this:

Code:
# $in{'so'} ? ($order = $in{'so'}) : ($order = "ASC");
# $in{'sb'} and ($sortby = "ORDER BY $in{'sb'} $order");
and add:

Code:
my $order;
for (1 .. 6) {
$in{"sb$_"} and ($order .= qq~$in{"sb$_"} $in{"so$_"},~);
}
chop $order;
$order ? ($sortby = "ORDER BY $order") : ($sortby = '');
Cheers,

Alex

--
Gossamer Threads Inc.
Quote Reply
Re: Sorting on multiple fields In reply to
Yes that's a lot neater :-)

Chris

--
http://webarchitects.co.uk/