Want to generate control labels from table column comments field.

[Harry Jamieson]

Hi,

I'm using FormBuilder 3.08 and I would like to have real labels on my form instead of just the column names.  I'm thinking that it should be possible for me to put this label information into the Comments field for each column in the table.  However....FormBuilder is just giving me labels with "1" in them.  Here is my code.  I'm on Windows XP SP3 using MySQL 5.2.44 and Active Perl 5.14.2. I've really tried to dig into this on the internet before asking.....  Here is my code:

#------------------------------------------ 

# Get Label contents.

#------------------------------------------ 

$sqlstatement=qq/SELECT column_name, column_comment FROM information_schema.columns 

   WHERE table_name='preferences'

   AND table_schema = 'hypersort';/;

my $label_sth = $dbh->prepare($sqlstatement);

$label_sth->execute() || 

      die "Could not execute SQL statement ... maybe invalid?";

 

@label_contents=();

while(my($name) = $label_sth->fetchrow_array) {

 push @label_contents, $name;

}

$label_sth->finish;

.

.

.

.

.

. Other stuff....

.

.

#------------------------------------------ 

# Init new form.

#------------------------------------------ 

my $form = CGI::FormBuilder->new(

title => 'HyperSort Default Settings',

             fields => \@pref_fields,

values => $defs,

labels => \@label_contents,

action => '/scripts/conn2.pl',

submit => [qw/Cancel Update/],

stylesheet => '/scripts/conn2.css',

        );

.

.

.

.

I'm pretty sure that my problem lies in how I'm getting the data into the hash.  Any help would be appreciated, as always!

Harry Jamieson

[Norton Allen]

On 10/22/2012 12:42 PM, Harry Jamieson wrote:

#------------------------------------------ 

# Init new form.

#------------------------------------------ 

my $form = CGI::FormBuilder->new(

title => 'HyperSort Default Settings',

             fields => \@pref_fields,

values => $defs,

labels => \@label_contents,

action => '/scripts/conn2.pl',

submit => [qw/Cancel Update/],

stylesheet => '/scripts/conn2.css',

        );

Harry, According to the documentation, the 'labels' option takes a hashref, not an arrayref, presumably pairing the field name with the desired label.

-Norton

[Harry Jamieson]

Hmmmm, down in the quickrefs it says that it can take either...

 # Create form
    my $form = CGI::FormBuilder->new(

       # Important options
       fields     => \@array | \%hash,   # define form fields
       header     => 0 | 1,              # send Content-type?

I've been programming for a long time, but am new to Perl and CGI, and am still confused about the various fetchrow variations.

Harry

[Norton Allen]

On 10/22/2012 2:00 PM, Harry Jamieson wrote:

Hmmmm, down in the quickrefs it says that it can take either...

 # Create form
    my $form = CGI::FormBuilder->new(

       # Important options
       fields     => \@array | \%hash,   # define form fields
       header     => 0 | 1,              # send Content-type?

fields can take either; labels requires a hashref.

I've been programming for a long time, but am new to Perl and CGI, and am still confused about the various fetchrow variations.

I'm not very familiar with the fetch* methods either. I usually skip the prepare() and execute() steps and use one of the selectall_* methods instead to return the complete query.

[Harry Jamieson]

Opps.  My bad!  Trying to finish the post before lunch.  :(  I guess that I will just have to play with this some more.  Playing is the best way to learn...

Harry

[Harry Jamieson]

I've solved this problem, though probably not with the elegance that someone with more Perl knowledge would have.  I simply read it in as an array, and this gave me my column_comment information without the spurious column heading from the schema.

$sql_stmt=qq/SELECT column_comment FROM information_schema.columns 

   WHERE table_schema = 'hypersort'

   AND table_name='preferences';/;

   

$label_sth = $dbh->prepare($sql_stmt);

$label_sth->execute() || 

      die "Could not execute SQL statement ... maybe invalid?";  

while($results=$label_sth->fetchrow_array) {

push @label_contents, $results;

}

$label_sth->finish;

.

.

.

.

.

After this I got my real column names from my actual table into an array, and built my hash from the two arrays together.

.

.

.

$row=0;

while($pref_fields[$row]) {

 $label_hash{$pref_fields[$row]} = $label_contents[$row];

 $row++;

"$pref_fields" contains my real column names and "$label_contents" contains what I want the labels to say from each columns "Comments" field.

Thanks for trying to help!

Harry