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

Hello, I have a question concerning a script that I am working on. How do you create a drop down selection using the first entry in a database? Example Database: 
Company_Name|Email|URL
company1|email1|URL1
company2|email2|URL2
company3|email3|URL3

[download]
So, the drop down would include company1, company2, and company3. Currently, I've set up the program to have an entry field in which the the Company_Name is entered and the email address and url is shown...But I would really like to utilize a drop down instead. Here is the area that I would like to have the drop down: 
    #------------- Company_Name
    print "<tr>\n";
    print "<td align=\"right\">\n";
    print "Userid:\n";
    print "</td>\n";
    
    print "<td>\n";
    print $query->textfield(-name=>'Company_Name',
            -size=>20);
    print "</td>\n";
    print "</tr>\n";

[download]
Thank you

Replies are listed 'Best First'.
Re: Drop Down Selection From Database
by extremely (Priest) on Jan 26, 2001 at 00:57 UTC
    You should be able to use DBI::CSV to parse that file and select the lines you want. Then, you can use the handy-dandy select method from CGI to create a properly formatted select form item.

    Multiple examples of the use of each of these are scattered about the site.

    --
    $you = new YOU;
    honk() if $you->love(perl)

[reply]
      Hope I'm not telling you what you already know, but if you need to pull the column names out of the table, you could go with opening up a filehandle and splitting. But I agree with the above: using DBD::CSV will scale nicely if you expect your app and data to grow beyond the usefulness of a flat text db.
[reply]
        Yeah, I got all kinds of split-fu =) DBD::CSV get's you SQL-style syntax with the files. That is a nice thing to have.

        --
        $you = new YOU;
        honk() if $you->love(perl)

[reply]
Re: Drop Down Selection From Database
by AgentM (Curate) on Jan 26, 2001 at 00:37 UTC
[reply]
      It depends on what you want to do with this - if you are simply building a menu from available entries in a database - I'd put the entries in an array, then do a for loop like: 
      print "<SELECT NAME=Company_Name>\n";
foreach (@company) {
    print "<OPTION VALUE=$_>$_\n";
}
print "</SELECT>\n";

      [download]
      However, if you want to do something more complex, like are editing that Company_Name field by using a drop down menu it's a bit more complex, because you've got to distinguish which option should be checked.
[reply]
[d/l]
      I understand how to use the <SELECT> and <OPTION> tags. My question is how do you pull the first entry, in the example database, to be the OPTIONs? Thank you
[reply]
        You'll need to provide a little more information than this. In specific, what kind of database is it and what method are you using to pull it (eg, it's a MySQL database and I'm using DBI to interface with it)?
[reply]
      something like this: not completely correct i think... 
      
use CGI;
use DBI;
use strict;

my $q = new CGI;
print $q->header();

print "<html><form><select>\n";

my $dbh = DBI->connect(&DSN, &USER, &PASS);

my $sth  = $dbh->prepare("SELECT memberID, membername FROM members");

$sth->execute();

while( my ($memberID, $membername) = $sth->fetchrow_array() ) {
   print "<option value="$memberID">$membername \n";
}
[reply]
        it erased my formating... the first time.. something like this: not completely correct i think... 
        
use CGI;
use DBI;
use strict;

my $q = new CGI;
print $q->header();

print "<html><form><select>\n";

my $dbh = DBI->connect(&DSN, &USER, &PASS);

my $sth  = $dbh->prepare("SELECT memberID, membername FROM members");

$sth->execute();

while( my ($memberID, $membername) = $sth->fetchrow_array() ) {
   print "<option value="$memberID">$membername \n";
}
[reply]
Re: Drop Down Selection From Database
by bfree (Initiate) on Jan 26, 2001 at 07:20 UTC

    Seeing as your using 

    $query->textfield(-name=>'Company_Name',-size=>20);

    [download]
    you must be using the CGI module, so you need to use
    $query->popup_menu(-name=>'Company_Name',-values=>\@first_entries);

    [download]

    values will accept an anonymous array 

    ['company1','company2']

    [download]
    a pointer to an array as above 
    @first_entries=('company1','company2');

    [download]
    and will be ordered as presented. However you can otherwise specify which element should be presented by default 
    -default=>'company1'

    [download]
    finally you could (for example) want the script to call the companies by url while the dropdown box displays the company_name (or more likely to use id fields) in which case 
    @values=('URL1','URL2','URL3');
%labels=('URL1'=>'company1','URL2'=>'company2','URL3'=>'company3');
print $query->popup_menu(-name=>'Company_Name',
  -values=>\@values,-labels=>\%labels,-default=>'URL2');

    [download]

    As for getting the data in there....there's more than one way to do it :-)

[reply]
[d/l]
[select]
Re: Drop Down Selection From Database
by cadfael (Friar) on Jan 26, 2001 at 18:26 UTC
    One thing to consider is whether you want this to be a controlled vocabulary. i.e. Are there a limited number of choices that you want people to have? If this is the case, then they can be hard coded into a pull-down form.

    If, on the other hand, you want to generate the current list of comapny names reflecting recent additions, then you would need to do something like this: 

    select distinct ID_no, Company_Name from Companies
order by CompanyName

    [download]
    (I put in an ID_no assuming that this is a relational database and you are using surrogate keys. You may need them further along in the query form)

    Then you would take the array returned by the SELECT and use it to populate the drop-down list.

    -----
    "Computeri non cogitant, ergo non sunt"

[reply]
[d/l]

Back to Seekers of Perl Wisdom