The easiest way to overcome this is to use a (little or not at all) documented feature called &build_select_field_from_db.
Set it up as you would a &build_select_field --
|; print &build_select_field_from_db("FieldName","$rec{'FieldName'}"); print qq|
It takes all the unique entries in the field, sorts them and makes a select list out of them.
You might want to create a new subroutine for a search form, though.
Another way you could do this would be to make a change to sub query in db.cgi. Something like
if (($in{'Country'} eq "United Kingdom") or ($in{'Country'} eq "UK") or ($in{'Country'} eq "England")) {
$in{'Country'} = "United Kingdom|UK|England|":
$in{'re'} = 1;
}
The only problem with this type of thing is that you'd have to come up with every possibile entry for a country. People also use "Great Britian" and "Britian" to refer to England. And you would have to have one of these structures for every other country that you want to allow alternative spellings for --
US, USA, United States, United States of America
--
comes to mind. I'm sure there are others.
There's a kinda fancy way you could use the &build_select_field_from_db, without having to make a separate form for searching. (This, of course, assumes you are not using autogenerate for your form.)
Use the following for the field where you enter the country on your form. (I'm assuming that this will go within an existing print qq| statement.)
Code:
<TR><TD>Country:</TD>
<TD>|;
print &build_select_field_from_db("Country","$rec{'Country'}");
unless ($in{'view_search'}) {
print qq|<BR>If your country is not listed above, please enter it below<BR>
<input type="text" name="Country" size=20>|;
}
print qq|</TD></TR>
(You would, of course, change "Country" to the exact name of the field in your database.
)
and then go on with the rest of your form.
You will have to make one minor change to db.cgi for this to work. Find sub parse_form -- it's almost at the end of the script. After
if ($value eq "---") { next PAIR; }
add
unless ($value) { next PAIR; }
The only way you could get into trouble would be if someone selected a country from the select list and entered a country in the field. If you want to be sure that doesn't happen (and you don't have any multiple selections possible elsewhere in your database), change
Code:
(exists $in{$name}) ?
($in{$name} .= "~~$value") : # If we have multiple select, then we tack on
($in{$name} = $value); # using the ~~ as a seperator.
to
Code:
($in{$name} = $value);
This specific code is not tested, but I have used something similar and it works just fine.
------------------
JPD
