Gossamer Forum
Home : Products : DBMan : Customization :

Sort on.......

Quote Reply
Sort on.......
Is there a way to sort on multiple fields?

(i.e sb=2,3 so=ascend)

----------------
donm
Quote Reply
Re: Sort on....... In reply to
Not at this time. DBMAN only sorts by one field.

Regards,

------------------
Eliot Lee
Founder and Editor
Anthro TECH, L.L.C
http://www.anthrotech.com/
info@anthrotech.com
==========================
Coconino Community College
http://www.coco.cc.az.us/
Web Technology
Coordinator
elee@coco.cc.az.us
Quote Reply
Re: Sort on....... In reply to
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 &#0124; &#0124; (!($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" . " &#0124; &#0124; ";
$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
Quote Reply
Re: Sort on....... In reply to
Just me again.

I tried to email you directly, Don but the address isn't functioning.

Anyways, I neglected to mention you can also delete the sort subroutine in db.cgi as they are no longer needed with the above mod. I build the sort function on the fly to eliminate hard-coding the compare functions based on how many sorts there are.

Delete this
Code:
# These are the sorting functions used in &query.
# --------------------------------------------------------
sub alpha_ascend { lc($sortby{$a}) cmp lc ($sortby{$b}) }
sub alpha_descend { lc($sortby{$b}) cmp lc ($sortby{$a}) }
sub numer_ascend { $sortby{$a} <=> $sortby{$b} }
sub numer_descend { $sortby{$b} <=> $sortby{$a} }
sub date_ascend { &date_to_unix($sortby{$a}) <=> &date_to_unix($sortby{$b}) }
sub date_descend { &date_to_unix($sortby{$b}) <=> &date_to_unix($sortby{$a}) }


------------------
Don Mitchinson
Quote Reply
Re: Sort on....... In reply to
mitch,

Decent workaround. However, not very seamless in my opinion. It forces users to select fields to sort by rather than specifying certain fields as hidden fields.

But good start to say the least.

Smile

Regards,

------------------
Eliot Lee
Founder and Editor
Anthro TECH, L.L.C
http://www.anthrotech.com/
info@anthrotech.com
==========================
Coconino Community College
http://www.coco.cc.az.us/
Web Technology
Coordinator
elee@coco.cc.az.us
Quote Reply
Re: Sort on....... In reply to
Mitch - thanks for the post and mod. However, I am looking for something that will perform the search using hidden fields such as Eliot mentions.

But thanks again for posting your mod !

-----------------
donm
Quote Reply
Re: Sort on....... In reply to
Sorting on hidden fields should be even easier, shouldn't it?

Now you don't need to create a select field that fills the options from the database's displayed fields.

Instead you create your two hidden fields for each sort (sb#, so#) where # indicates the order of the sort. Set the value of sb# to the hidden field name. eg.
Code:
<input type="hidden" name="sb1" value="ID">
<input type="hidden" name="so1" value="ascend">

Hope that's what you're trying to do. Let me know if I'm missing something obvious.




------------------
Don Mitchinson
Quote Reply
Re: Sort on....... In reply to
Hi,
How do this mod sort my cars alpha by 'make' and than 'types' of the 'makes' also alpha, something like this:
Alfa 145
Alfa 164
Alfa 175
BMW 316
BMW 320
BMW 518
BMW 535
Ford Escort
Ford Mustang
Ford Thunderbird
Mercedes 190
Mercedes 190 D
Mercedes 200
Mercedes 320
Mercedes SL 320
Mercedes SL 500
....
....
because thats what I must do.
The 'Make-field' = 2
The 'Type_field' = 4

Thanks in advance.
-------
Mart.

Quote Reply
Re: Sort on....... In reply to
Do anyone knows???
Quote Reply
Re: Sort on....... In reply to
How far have you got? Have you tried to copy and paste any of the above code?

It works with up to three fields and lets you select from any non-admin database fields.

Do you have a sample page where your DBMAN is implemented? Give us something to work with.

Quote Reply
Re: Sort on....... In reply to
If you just want to sort on two fields without input from the user, you need to
replace the select boxes in html.cgi with hidden input fields as I mentioned previously.
e.g.
Code:
<input type="hidden" name="sb1" value="Make">
<input type="hidden" name="so1" value="ascend">
<input type="hidden" name="sb2" value="Type">
<input type="hidden" name="so2" value="ascend">

But make sure to follow the other mod instructions for db.cgi above:
Quote Reply
Re: Sort on....... In reply to
Thank you.
I'll try today.

Greetings,
---------
Mart.
Quote Reply
Re: Sort on....... In reply to
Well, got it working now, only the max_hits do not work. It displays all the records I've searched for, even if its more than 500.
Any idea's??
Quote Reply
Re: Sort on....... In reply to
Solve it Smile :
In the sub query, somewhere at the end, there is another $in{'sb'} to replace with $in{'sb1'}, thats all!!

THANKS FOR THIS GREAT M O D ! ! !

Greatings,
----------
Mart.
Quote Reply
Re: Sort on....... In reply to
Glad you found the mystery bug - and hope that the mod works out okay for you.
Thanks for your comments.
Quote Reply
Re: Sort on....... In reply to
I've just implemented all of the code you have supplied here and everything seems to be sorting just fine ... NORMALLY .... it's when I attempt to do a "sb2" or "sb3" I get "Error: Runtime exception"

I have checked and rechecked the coding .. it matches what's here... including the
Code:
$in{'sb'} to replace with $in{'sb1'}
at the bottom.

Any idea where the problem could be ?

Thanks in advance,

-keienb
Quote Reply
Re: Sort on....... In reply to
I'm running into a problem with this.

sorting by one field works. sb1
sb1=11&so1=descend&view_records=1&ID=*&nh=1

sorting by two fields gives me a 500 error.
sb1=11&so1=descend&sb2=1&so2=ascend&view_records=1&ID=*&nh=1

I am also having a problem with spanning pages. Every page is a duplicate of the first.

Any ideas?
Thanks,
Adam
Quote Reply
Re: Sort on....... In reply to
ok...i think i might have fixed the spanning pages problem. What was mentioned before about the extra $in{'sb'} needing to change to $in{'sb1'} works.

However, until i get the sb2 fixed i won't know if it works entirely.

Adam
Quote Reply
Re: Sort on....... In reply to
Well I found it. Everything is working with the sort now. In the mod there are &#0124; &#0124;. It seems when I copied it I ended up with | |.
A lousy space was the culprit! Probably the hardest thing to see.

Adam
Quote Reply
Re: Sort on....... In reply to
Sorry you were on your own. I just didn't know enough about the concept to even start. I'm glad you got it figured out.

The forum software adds a space between two pipe characters. Nothing we can do about it unelss we can remember to use "or" instead of "&#0124; &#0124;".


------------------
JPD





Quote Reply
Re: Sort on....... In reply to
It's funny, I didn't even realize that in my last message the &#0124; &#0124; with the space and without the space came out the same. You actually helped me anyway now because I'll know to look at this first in the future.

Adam
Quote Reply
Re: Sort on....... still not searching accross In reply to
Having followed this forum through, and made the changes throughout, it would appear that my database still only searches on sb1, and not sb2 or sb3. It simply ignores the sb2 and sb3 fields. Has anyone managed to make it work? I have checked and rechecked my code

Quote Reply
Re: Sort on....... still not searching accross In reply to
I got it to work, although I have to look over the file to see exactly how. After dinner I'll come back and let you know what I did.

Adam

Quote Reply
Re: Sort on....... still not searching accross In reply to
If you followed everything here, then there is nothing else I can offer. I just looked over my db.cgi and the only changes I made are listed here exactly. Keeping in mind the extra space I found and the extra sb => sb1 that had to be changed.

Adam

Quote Reply
Re: Sort on....... still not searching accross In reply to
Well I figured it out in the end! And now, it works!

The answer (for me) was not to search by the names of the fields, but the field number as defined in the .cfg file ie if your cfg file is laid out something like this (this is a snippet of mine)

Date => [4, 'numer', 40, 255, 1, '', ''],
Media => [5, 'alpha', 0, 255, 1, '', ''],
Description => [6, 'alpha', '40x3', 2000, 0, '', ''],

Then you would define sb1 as 4 and not 'Date', sb2 as 5 and not 'Media' and so on (in any order you want).

It now works, and yes it works accross 3 levels of sb.

Hope this is clear!

Clive