randallmarquis has asked for the wisdom of the Perl Monks concerning the following question:

Here is the code from a page that creates a select box list from records in a database:
<center> <table width=450 CELLPADDING=0 CELLSPACING=0 BORDER=0 BGCOLOR="#FFFFf +f"> <tr><td><img src="http://lender-reviews.com/img/logo.png"> <br> <P align=center><font face=Arial size=2>Pick which Company you wish +to modify: <P> <FORM ACTION="$db_script_url" METHOD="GET"> <P><center>|; print &build_select_field_from_db ("name", "record-nu +mber", "modify"); print qq|</center> <P><INPUT TYPE="HIDDEN" NAME="modify_form_record" VALUE="1"> <center><INPUT TYPE="SUBMIT" VALUE="Modify"> <INPUT TYPE="RESET" VA +LUE="Reset Form"></center> </FORM> $html_menu $html_footer </font> </td></tr> </table> </center>
The select box output looks like this:
<P> <FORM ACTION="http://lender-reviews.com/cgi/dbmod.cgi/lender/lynn909 +67.htm" METHOD="GET"> <P><center><SELECT NAME="modify"><OPTION>---<OPTION>1ST ALLIANCE LE +NDING, LLC<OPTION>1ST COMMONWEALTH BANK OF VIRGINIA<OPTION>1ST MARYLA +ND MORTGAGE CORPORATION<OPTION>A PLUS MORTGAGE SERVICES INC<OPTION>AC +CESS NATIONAL MORTGAGE CORPORATION<OPTION>ACHIEVA CREDIT UNION<OPTION +>ACOPIA LLC</SELECT><P><INPUT TYPE="HIDDEN" NAME="modify_form_record" + VALUE="1"> <br> <center><INPUT TYPE="SUBMIT" VALUE="Modify"> <INPUT TYPE="RESET" VA +LUE="Reset Form"></center><br> </FORM>

The routine that creates the content is "build_select_field_from_db"

Here is the routine in full:

sub build_select_field_from_db { # -------------------------------------------------------- # Builds a SELECT field from the database. my ($column, $value, $name) = @_; my (@fields, $field, @selectfields, @lines, $line, $ouptut); my ($fieldnum, $found, $i) = 0; for ($i = 0; $i <= $#db_cols; $i++) { if ($column eq $db_cols[$i]) { $fieldnum = $i; $found = 1; last; } } if (!$found) { return "error building select field: no fields specified!"; } open (DB, "<$db_file_name") or &cgierr("unable to open $db_file_na +me. Reason: $!"); @lines = <DB>; close DB; LINE: foreach $line (@lines) { if ($line =~ /^#/) { next LINE; } # Skip comment lines. + if ($line =~ /^\s*$/) { next LINE; } # Skip Blank Lines. chomp ($line); @fields = &split_decode ($line); if (!(grep $_ eq $fields[$fieldnum], @selectfields)) { push (@selectfields, $fields[$fieldnum]); } } if ($name) { $output = qq|<SELECT NAME="$name"|; } else { $output = qq|<SELECT NAME="$column"|; } $output .= "><OPTION>---"; foreach $field (sort @selectfields) { if ($field eq $value) { $output .= "<OPTION SELECTED>$field"; } else { $output .= "<OPTION>$field"; } } $output .= "</SELECT>"; return $output; }
And therein lies my problem. I need the select box content changed to include the value of the database key, which is the first column in the database (known as 'record-number')

This line: $output .= "<OPTION>$field"; Needs to be changed to this somehow: $output .= "<OPTION value=\"record-number\">$field";

Where the above 'record-number' actually pulls the data from that record.

Here's what a line out of the database looks like:

5111|ACOPIA LLC|BRANT PHILLIPS|306 NORTHCREEK BLVD STE

The actual desired outcome is to create a 'SELECT' tag that looks like this:

<OPTION value="5111">ACOPIA LLC

Simple? I don't know... I tried $db_key but it only returned the name of the field (record-number) and not the data.

Randall Marquis

Replies are listed 'Best First'.
Re: Help syntax?
by ikegami (Patriarch) on Jul 12, 2010 at 01:19 UTC

    Please wrap computer text (code, data, output) in <c>...</c> tags. It'll even handle escaping for you.

    @selectfields needs to hold two values per record, the id and the value of the selected column.

    sub text_to_html { my $s = shift; $s =~ s/&/&amp;/g; $s =~ s/</&lt;/g; $s =~ s/"/&quot;/g; return $s; } sub build_select_field_from_db { # -------------------------------------------------------- # Builds a SELECT field from the database. my ($column, $value, $name) = @_; $name ||= $column; my $fieldnum; for my $i (0..$#db_cols) { if ($column eq $db_cols[$i]) { $fieldnum = $i; last; } } if (!defined($fieldnum)) { return "error building select field: no fields specified!"; } open (my $db_fh, "<", $db_file_name) or &cgierr("unable to open $db_file_name. Reason: $!"); my @lines = <$db_fh>; close $db_fh; my @selectfields; foreach my $line (@lines) { next if $line =~ /^#/; # Skip comment lines. next if $line =~ /^\s*$/; # Skip Blank Lines. chomp ($line); my @fields = &split_decode ($line); my ($row_id, $row_value) = @fields[0, $fieldnum]; if (!(grep $_[1] eq $fields[$fieldnum], @selectfields)) { push @selectfields, [ $fields[0], $fields[$fieldnum] ]; } } my $output = qq|<SELECT NAME="$name">|; $output .= "<OPTION>---"; foreach (sort { $a->[1] cmp $b->[1] } @selectfields) { my ($row_id, $row_value) = @$_; my $esc_id = text_to_html($row_id); my $esc_value = text_to_html($row_value); my $selected = $value eq $row_value ? ' selected' : ''; $output .= qq{<option$selected value="$esc_id">$esc_value}; } $output .= "</SELECT>"; return $output; }

    Update: That grep bothers me.

    sub build_select_field_from_db { # -------------------------------------------------------- # Builds a SELECT field from the database. my ($column, $value, $name) = @_; $name ||= $column; my $fieldnum; for my $i (0..$#db_cols) { if ($column eq $db_cols[$i]) { $fieldnum = $i; last; } } if (!defined($fieldnum)) { return "error building select field: no fields specified!"; } open (my $db_fh, "<", $db_file_name) or &cgierr("unable to open $db_file_name. Reason: $!"); my @lines = <$db_fh>; close $db_fh; my %selectfields; foreach my $line (@lines) { next if $line =~ /^#/; # Skip comment lines. next if $line =~ /^\s*$/; # Skip Blank Lines. chomp ($line); my @fields = &split_decode ($line); my ($row_id, $row_value) = @fields[0, $fieldnum]; $selectfields{$row_value} = $row_id; } my $output = qq|<SELECT NAME="$name">|; $output .= "<OPTION>---"; foreach my $row_value (sort keys (%selectfields)) { my $row_id = $selectfields{$row_value}; my $esc_id = text_to_html($row_id); my $esc_value = text_to_html($row_value); my $selected = $value eq $row_value ? ' selected' : ''; $output .= qq{<option$selected value="$esc_id">$esc_value}; } $output .= "</SELECT>"; return $output; }
      I tried your solution and it returns error codes... so it no work.

      This original DB was written back in the 90's, and could be updated I'm sure, but I need it to work for this site "lender-reviews.com" until we get funded.

      So it's critical to me. Here's a sample output of the data:
      http://lender-reviews.com/cgi/dbmod.cgi/lender/default.htm?view_records=1&ND=ND&option11=W&format=short&sb=26

      The 'fix' we are attempting to work on here is to assist in the editing of the data by admin and clients.

      I'm in Orange County, Southern California, and will take phone calls regarding this gladly: 949-722-7005

        I tried your solution and it returns error codes...

        Fix them, or let us know what they are. It's not like we can run the program.

        Update: $row_value was misspelled as $f_value in one place. Fixed in original. Is that all there was?

Re: Help syntax?
by kejohm (Hermit) on Jul 12, 2010 at 09:36 UTC

    On a side note, might I suggest using the CGI module or similar to output HTML, instead of printing it yourself. It will probably make things a lot simpler. Here is an sample of your code rewritten using CGI (plus a few style changes):

    use CGI qw(:standard); # use functions instead of OO interface sub build_select_field_from_db { # -------------------------------------------------------- # Builds a SELECT field from the database. my ( $column, $value, $name ) = @_; my ( $fieldnum, $found ) = ( 0, 0 ); for my $i (0 .. $#db_cols) { if ($column eq $db_cols[$i]) { $fieldnum = $i; $found = 1; last; } } if (!$found) { return q(error building select field: no fields specified!); } open my $DB, q(<), $db_file_name or cgierr(qq(unable to open $db_f +ile_name. Reason: $!)); my @lines = <$DB>; close $DB; my @selectfields; LINE: foreach my $line (@lines) { if ($line =~ /^#/) { next LINE; } # Skip comment lines. if ($line =~ /^\s*$/) { next LINE; } # Skip Blank Lines. chomp $line; my @fields = split_decode ($line); if(!(List::MoreUtils::any { $_ eq $fields[$fieldnum] } @select +fields)){ push @selectfields, $fields[$fieldnum]; } } my $output = popup_menu( -name => defined $name ? $name : $column, -values => [ '---', sort @selectfields ], -default => $value, ); return $output; }