Gossamer Forum
Home : Products : DBMan : Customization :

Fancy select field extract from db

Quote Reply
Fancy select field extract from db
Hi Guys,

I hope this has been done before. I cannot seem to find a posting for this though, so if anyone can help I would really appreciate the assistance.

I need to create a dynamic drop down menu based on fields within the existing db.

In other words the drop down has $rec{'field1'} as a value and $rec'{field2'} as the select text. All except blank fields must be filled in. If at all possibe the fields should be alphabetically sorted to make the drop down easy to read.

Thanks in advance
G

Quote Reply
Re: Fancy select field extract from db In reply to
So you want

"build_fancy_select_field_from_db"

right?

I'm afraid my brain isn't working too well tonight and this is kinda confusing. I'll give it a try later.


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






Quote Reply
Re: Fancy select field extract from db In reply to
Maybe I have it. Smile

Try adding the following to db.cgi:

Code:
sub build_fancy_select_field_from_db {
# --------------------------------------------------------
# Builds a fancy SELECT field from the database.

my ($column, $select, $value) = @_;
my (@fields, $field, @selectfields, %values, @lines, $line, $ouptut);
my ($fieldnum1, $found1, $fieldnum2, $found2, $i) = 0;

$name or ($name = $column);

for ($i = 0; $i <= $#db_cols; $i++) {
if ($column eq $db_cols[$i]) {
$fieldnum1 = $i; $found1 = 1;
}
if ($select eq $db_cols[$i]) {
$fieldnum2 = $i; $found2 = 1;
}
}
if (!$found1 or !$found2) {
return "error building select field: no fields specified!";
}

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);
if (!(grep $_ eq $fields[$fieldnum1], @selectfields)) {
push (@selectfields, $fields[$fieldnum1]);
$values{$fields[$fieldnum1]} = $fields[$fieldnum2]
}
}
close DB;

$output = qq|<SELECT NAME="$name"><OPTION>---|;
foreach $field (sort @selectfields) {
if ($values{$field} eq $value) {
$output .= qq|<OPTION VALUE="$values{$field}" SELECTED>$field) |;
$output .= qq|<OPTION VALUE="$values{$field}">$field|;
}
}
$output .= "</SELECT>";

return $output;
}

To use it in sub html_record_form, enter

Code:
|;
print &build_fancy_select_field_from_db("FieldnameA","FieldnameB","$rec{'FieldnameB'}"); print qq|

where FieldnameA is the name of the field where the values you want to show in your select field are and FieldnameB is the name of the field which holds the values you want to be added to your database.

I'm not sure about when you said
Quote:
All except blank fields must be filled in.

Might there be blank entries in the fields you're pulling from?


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






Quote Reply
Re: Fancy select field extract from db In reply to
Hi Carol,

Thank for the prompt response. I will try your code and let you know, but here is what I am trying to do.


I am trying to get a drop down based on some fields within the existing db. The alternative is to build another db. I already have 3 for this and the edit menus are already a pain. All the data I need is in an existing db. All I need to do is exctract it. I am actually using the add form for this to double as a frontpage plus as a data capture form and therefore yes some fields may be blank.

The point is to create a dropdown of filenames (values) and short descriptions (selections) for download. At time of edit the password client will be able to fill in the select field indicating the file is available for download. There are numerous files for various other purposes, but not all are available for download. Only about 20 out of about 50. The dynamic dropdown will enable password client to update the downloadable files in the normal edit manner and upload his file via FTP.

When a default user calls the page for download the dynamic drop down will give him a list for download.

Hope this helps.

Much appreciated.
G

Quote Reply
Re: Fancy select field extract from db In reply to
Can you give me an example of how you would use this? I can't figure out how it would work. You would be pulling information from two fields, but only one field would be written to.

Maybe I don't understand (obviously! Smile ).


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






Quote Reply
Re: Fancy select field extract from db In reply to
Hi Carol,

I tried the code, but had no luck. In trying to debug the code I found out what could be part of the problem. As this application involves more than one database the data I need to extract does not exist as the current cfg designated db.

The extracted value for the dropdown should be field 4 and the select text should be field 7 with the 'name' being set to "Module"

Thanks for the input so far.
G
Quote Reply
Re: Fancy select field extract from db In reply to
Ah. So you want a fancy select field with the data source being *another* .db file?

Forgive me for not giving the code right now, but I want to make sure I understand what you're doing before I take the time to write the code. Smile


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






Quote Reply
Re: Fancy select field extract from db In reply to
Hi Carol,

Please accept my apologies for having missed that, but I only realised this after the posting. All the more reason why not to create yet another db. The db edits are getting rather too messy. I have pointed all cfg files to the same password file and have selected db menu on all edit pages which makes it easier. So far it works great.

I have 3 db's. One for news, one that records contact details for download records and one that is the main records db, which contains the actual filename size, description and other data etc.

I allow a default user add access and use the add_record in the one pl to capture client details to tell the client who downloaded what.

It is in this add form that I need to extract from the main db to build the available files for download. The main db has fields $rec{'Module'}(field[4] as file name as a value required) and $rec{'Drop'} (field[7] as short description text for select text required).

If this works Ok I would like to have a single db edit screen that allows the password user to just include the short decription text ($Drop), upload the file and it hopefully becomes available on the drop down to default users to the site. All other data is already in the main db including file name, size, date, long description etc., which is used in the normal html.pl manner to 'sell' the software.

This is why I need the build to fill in only fields that are valid. Not all files are available for download. Some are included within other modules and are only used as descriptives.

Am I making sense here?

Really appreciate the inpout form you.
G

Quote Reply
Re: Fancy select field extract from db In reply to
I'm still not real clear, so I'm going to ask some more questions. Smile

Actually, the use of it isn't too important, now that I understand what you're doing (basically). But what is important is

Quote:
This is why I need the build to fill in only fields that are valid.

What determines whether a field is valid? Is it just that both fields have entries? Or is there some other field within the record that needs to be checked?


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






Quote Reply
Re: Fancy select field extract from db In reply to
Hi Carol,

A valid field would be one containing actual data. A blank field would not be a valid field. The dop down would therefore be built only from fields that contain data meaning that they are available for download. At the time of data edit, to make the file available for download all the password user would need to do is fill in the drop down text data to make this a valid file for download. Deletion of data from this field would remove the file from the drop down.
Hope this make sense. If not I would need to mail you the URL's so you can take a look.

Thanks
G
Quote Reply
Re: Fancy select field extract from db In reply to
Okay. I think this may be it. Smile

Code:
sub build_fancy_select_field_from_other_db {
# --------------------------------------------------------
# Builds a fancy SELECT field from the database.
my ($column, $value) = @_;
my (@fields, $field, @selectfields, %values, @lines, $line, $ouptut);
my ($fieldnum1, $fieldnum2, $i) = 0;

$name or ($name = $column);

$other_db_file_name = $db_script_path . "other.db";

open (DB, "<$other_db_file_name") or &cgierr("unable to open $other_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);
if ($fields[4] and $fields[7]) {
if (!(grep $_ eq $fields[7], @selectfields)) {
push (@selectfields, $fields[7]);
$values{$fields[7]} = $fields[4]};
}
}
close DB;

$output = qq|<SELECT NAME="$name"><OPTION>---|;
foreach $field (sort @selectfields) {
if ($values{$field} eq $value) {
$output .= qq|<OPTION VALUE="$values{$field}" SELECTED>$field) |;
}
else {
$output .= qq|<OPTION VALUE="$values{$field}">$field|;
}
}
$output .= "</SELECT>";
return $output;
}

You will need to make sure you set the name of the external .db file in the line

$other_db_file_name = $db_script_path . "other.db";





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






Quote Reply
Re: Fancy select field extract from db In reply to
Hi Carol,

What can I say? Absoultely perfect. Thanks a million. If I were'nt living on a different continent I would give you a big sloppy kiss and a box of chocalates.

Much obliged.
G
Quote Reply
Re: Fancy select field extract from db In reply to
 

Virtual kisses and chocolates are almost as good as the real thing. Glad I could get it for you.


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






Quote Reply
Re: [JPDeni] Fancy select field extract from db In reply to
Carol, I would also like to thank you. I'm not sure when you created this code, but Its exactly what I've been looking for.

I recently found DBMan and realized I had found one of the best Flat File Database scripts out there. Had 3 databases setup within 2 days. Than I realized I needed a partial relational database. This script fixes that problem up real quick