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

Dear monks, I am really new in cgi, although I have some experience in perl, databases and the DBI module. I have a perl script that connects to a database and for the time being it prints the names of the columns of my table of choice the script is the following: 
print "Content-type: text/html;\n\n";


use DBI;

# Connect to the database
# See footnote 1
my $dbh = DBI->connect('DBI:mysql:mirnas', 'user', 'pass')
    or die "Couldn't open database: $DBI::errstr; stopped";
    
    
    
# Getting all the column names

my $table = '07_11_09';
my $sth = $dbh->prepare("SELECT * FROM $table WHERE 1=0;");
$sth->execute;
my @cols = @{$sth->{NAME}}; # or NAME_lc if needed
$sth->finish;
foreach ( @cols ) {
printf( " %s", $_ );
}

[download]
What i want to know is there any method in CGI by which I can create a list of checkboxes which have the names of the columns. And if this is possible can I pass these names, when the checkboxes are checked as parameters in a mysql search later on ? thank you all in advance for your help

Replies are listed 'Best First'.
Re: perl, DBI and CGI checkboxes
by wfsp (Abbot) on Mar 22, 2010 at 16:23 UTC
[reply]
      Thanks everyone for the answer. I used the second reply and it worked. Now my code looks like this: 
      #!/usr/bin/perl

use DBI;
use CGI ':standard';
use strict; use warnings;

my $q= CGI->new();

print header, start_html('Advanced Search');

# Connect to the database
# See footnote 1
my $dbh = DBI->connect('DBI:mysql:mirnas', 'root', 'pass')
    or die "Couldn't open database: $DBI::errstr; stopped";
    
    
# Getting all the column names and creating checkboxes for them
my $table = '07_11_09';
my $sth = $dbh->prepare("SELECT * FROM $table;");
$sth->execute;
my @cols = @{$sth->{NAME}}; # or NAME_lc if needed
$sth->finish;

#printing the checkboxes
print
checkbox_group(-name=>'fields_to_search',
               -values=>\@cols # Your array above
              );
#storing the parameters from the checkboxes
my @fields_to_search=param('fields_to_search');



#creating the submit boxes

print'<FORM 
    ACTION="http://sirocco/cgi-bin/advanced_search.pl"
    METHOD="POST">';                          
print br, '<INPUT TYPE = "SUBMIT"VALUE="Click To Submit">';
print '<INPUT TYPE="RESET"VALUE=Clear>';
print '</FORM>', end_html;

      [download]
      But when I do that I don't get anything. Also how do I pass the @params to an sql statement Would it be something like my $sth = $dbh->prepare("SELECT * FROM $table WHERE tissue = @fields to search;"); THanks for any possibble answers in advance
[reply]
[d/l]
Re: perl, DBI and CGI checkboxes
by spazm (Monk) on Mar 22, 2010 at 15:53 UTC
    yes.

    In fact, there are lots of ways. You might want to consider a framework and an ORM layer to simplify your HTML and DB interaction. But here's a nice barebones solution in the vein of your original code.

    See CGI for more information. ( http://search.cpan.org/~lds/CGI.pm-3.49/lib/CGI.pm ) 

    
#!/usr/bin/perl

use DBI;
use CGI;
use strict; use warnings;

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


# Connect to the database
# See footnote 1
my $dbh = DBI->connect('DBI:mysql:mirnas', 'user', 'pass')
    or die "Couldn't open database: $DBI::errstr; stopped";
    
    
# Getting all the column names
my $table = '07_11_09';
my $sth = $dbh->prepare("SELECT * FROM $table WHERE 1=0;");
$sth->execute;
my @cols = @{$sth->{NAME}}; # or NAME_lc if needed
$sth->finish;
 print $q->checkbox_group(          -name=>'databases',
                                -values=>[@cols],
                                -linebreak=>'true',
                                #-labels=>\%labels,
                                #-attributes=>\%attributes
);

print $q->end_html();

    [download]
[reply]
[d/l]
Re: perl, DBI and CGI checkboxes
by Utilitarian (Vicar) on Mar 22, 2010 at 16:01 UTC
    hi, If I understand you correctly you want to read the table headers of a table in the DB (or even describe the table ;) into an array and for every entry in the array add a checkbox, then on reading if that entry is checked you wish to use the associated field in the SELECT statement?

    That is a spec in itself, in CGI you can use a checkbox_group as follows 

    print
checkbox_group(-name=>'fields_to_search',
               -values=>\@cols # Your array above
              );

    [download]
    And when processing 
    @fields_to_search=param('fields_to_search');

    [download]

    print "Good ",qw(night morning afternoon evening)[(localtime)[2]/6]," fellow monks."
[reply]
[d/l]
[select]
      Thanks a lot for the answer, it did work Now I want to change the script so instead of selecting the columns, selecting the rows so my script looks as follows: 
      #!/usr/bin/perl

use DBI;
use CGI ':standard';
use strict; use warnings;

my $q= CGI->new();

print header, start_html('Advanced Search');

# Connect to the database
# See footnote 1
my $dbh = DBI->connect('DBI:mysql:mirnas', 'root', 'pass')
    or die "Couldn't open database: $DBI::errstr; stopped";
    
    
# Getting all the column names and creating checkboxes for them
my $table = '07_11_09';
my $sth = $dbh->prepare("SELECT * FROM $table;");
$sth->execute;
my @cols = @{$sth->{NAME}}; # or NAME_lc if needed
$sth->finish;

#printing the checkboxes
print
checkbox_group(-name=>'fields_to_search',
               -values=>\@cols # Your array above
              );
#storing the parameters from the checkboxes
my @fields_to_search=param('fields_to_search');



#creating the submit boxes

print'<FORM 
    ACTION="http://sirocco/cgi-bin/advanced_search.pl"
    METHOD="POST">';                          
print br, '<INPUT TYPE = "SUBMIT"VALUE="Click To Submit">';
print '<INPUT TYPE="RESET"VALUE=Clear>';
print '</FORM>', end_html;

      [download]
      But when I do that I don't get anything. Also how do I pass the @params to an sql statement Would it be something like my $sth = $dbh->prepare("SELECT * FROM $table WHERE tissue = @fields to search;"); THanks for any possibble answers in advance
[reply]
[d/l]

Back to Seekers of Perl Wisdom