I work best with an example, so I'm going to create one that seems to be what you are going for.
My database is about farmers. Each record is the information about one farmer. Each farmer can grow three (or fewer?) fruits and/or vegetables, but no more.
These are the records I currently have:
Name: Old MacDonald
Crop1: Apples
Crop2: Oranges
Crop3: Artichokes
Name: Farmer Inthedell
Crop1: Artichokes
Crop2: Green beans
Crop3: Kumquats
Name: Mr. MacGregor
Crop1: Lettuce
Crop2: Kumquats
Crop3: Apples
Name: Mr. Greenjeans
Crop1: Plums
Crop2: Grapes
Crop3: Bananas
In a search form, I want to have all of the fruits and vegetables from all three fields in a select field and I would want it to search across all three Crop fields.
(This would actually be more efficient and elegant if you used a relational database structure with your Categories in a separate small table. That way, you could have more than three categories for each company. But we'll work with what you have.)
Using my data, the select field would then include:
Apples
Artichokes
Bananas
Grapes
Green beans
Kumquats
Lettuce
Oranges
Plums
And if I did a search for Apples, I should get Old MacDonald and Mr. MacGregor, even though Apples is in a different field in each of the records.
This is one of those things you need to hard-code into the db.cgi file. Or maybe someone else could figure out how to make it less hard-coded.
Code:
sub build_select_field_from_multiple_fields {
# --------------------------------------------------------
# Builds a SELECT field from the database.
my ($value) = @_;
my (@fields, $field, @selectfields, @lines, $line, $ouptut);
my (@fieldnum, $num, $found, $i) = 0;
# can not be the name of any of the fields you are using # Make it something descriptive, though. $name = "Category"; # Define which fields you want to include -- the *numbers* of the fields, not the names
$fieldnum[0] = 4;
$fieldnum[1] = 5;
$fieldnum[2] = 6;
# Theoretically you can use however many you want.
# The more you use, especially with a large database, the more it will slow down your application.
open (DB, "<$db_file_name") or &cgierr("unable to open $db_file_name. Reason: $!");
if ($db_use_flock) { flock(DB, 1); }
LINE: while (<DB>) {
next if /^#/;
next if /^\s*$/;
$line = $_;
chomp ($line);
@fields = &split_decode ($line);
foreach $num (@fieldnum) {
if (!(grep $_ eq $fields[$fieldnum[$num]], @selectfields)) {
push (@selectfields, $fields[$fieldnum[$num]]);
}
}
}
close DB;
$output = qq|<SELECT NAME="$name"><OPTION>---|;
foreach $field (sort @selectfields) {
($field eq $value) ?
($output .= "<OPTION SELECTED>$field") :
($output .= "<OPTION>$field");
}
$output .= "</SELECT>";
return $output;
}
That will make your select field, but that's only part of the battle. Other things that must be done for this to work...
- Use by including &build_select_field_from_multiple_fields($in{Category}) in your form (or whatever you are naming your select field)
- Create a separate form for searching (you don't want this in your "add record" form)
- Set up sub query so that if there is input from this select field, the input will be copied to all of the category fields and $in{'ma'} is set to "on."
JPD
----------------------------------------------------
JPDeni's DBMan-ual How to
ask questions the smart way.
