In my wine database, certain wine names are of the form 'Viñas Viejas de San Alejandro' Garnacha while others have the form Albariño. In the standard sorting, all names that start with an apostrophe come before those that don't. I have read the FAQ on changing characters, but am stuck on where exactly to place the translate/substitute command in the sort/search procedure.

It seems that the command should be placed in the sub query someplace. I am using the multiple field sort which includes the code:

---------------------
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 $

#$sort_func =~ tr/[',<br>]/[, ]/g; # Replace ' with nothing


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]);

}

------------------------

The underlined command, when uncommented causes the script to crash. I also tried

$sort_func =~ tr/'//g; # Replace ' and <br> with nothing

which also didn't work.

What I want is to ignore the apostrophes when sorting/searching but not to permanently remove them from the record or from the display of the information in short/long displays.

In action:

http://www.lechai.com/cgi-bin/dbtest.cgi?db=winestest&uid=default

Go to list all and sort by "Wine Name" go to page six and you will see the problem.

Any help is appreciated. Thanks in advance.

One solution may be to create another field to use as a sort field.

For example if the name of this field is title2. You could add this as a hidden field except to admin within your html_record_form:

if ($per_admin) {
print qq|<TR><TD><$font>Title 2:</font></TD><TD><INPUT TYPE="TEXT" NAME="title2" SIZE="30" VALUE="$rec{'title2'}"></TD></TR>|;
}
else {
print qq|<TR><TD colspan=2><input type="hidden" name="title2" value="$rec{'title2'}"></TD></TR> |;
}
print qq|


Then in your db.cgi file, sub validate_record after

my ($col, @input_err, $errstr, $err, $line, @lines, @data);

ADD:

$in{'title2'} = $in{'Title'};
$in{title2}=~ s/[,!'&quot;]//g; ## remove punctuation
$in{title2} =~ s/\b(\w)/\U$1/g; ## capitalize first letter

What this would do is to copy the value of your Title field into this new hidden field (title2) when you add records - strip out the extra characters and capitalize the first letter; so it will sort A - Z.

Then set this new field as your default sort field.

Unoffical DBMan FAQ

http://creativecomputingweb.com/dbman/index.shtml/