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

Using a checkbox to retrieve values from a mysql database. So far, my form looks like:
<form action="/cgi-bin/listproducts_test.pl" method = "GET"> <input type = "checkbox" name="list" value = 'book'>Book<br> <input type = "checkbox" ...will have extra checkboxes here<br> <input type = "submit" value = "list"> </form>
key parts of listproducts.pl looks like:
#Create an instance of the CGI object $cgiobject = new CGI; #Output HTML header to the Web Browser print $cgiobject -> header; #Grab values submitted by the user my $product=$cgiobject ->param("list"); $dbh=DBI->connect("dbi:mysql:$userdb:$dbserveraddy","$username","$ +password") or die "Connection error: $DBI::errstr\n"; #Define MYSQL query - i think this is where I need assistance $sql = "Select * FROM ?"; $sth=$dbh->prepare($sql); $sth->execute($product) || + #ERROR HERE die "Could not execute SQL statement... maybe invalid syntax?"; print "<table border=2>"; #fetch each row and print results - also need assistance with this + if its wrong while (@results = $sth->fetchrow_array()) { foreach $result (@results){ if ($result eq $results) { print "<tr><td>$results</td></tr>"; } } } print "</table>"; $sth->finish; $dbh->disconnect;
The die catch is telling my my SQL syntax is invalid, but I cant see why. This is a basic version of the syntax to highlight the SQL problem (points to the line indicated by #ERROR HERE). I plan to have additional values in the checkbox and use a foreach loop around the $dbh and $sth handles. Basically, ive got 3 tables of products and I want to be able to list all products in each table if checked in the checkbox. Am I on the right lines?

Replies are listed 'Best First'.
Re: checkbox and mysql select
by moritz (Cardinal) on Apr 13, 2008 at 14:56 UTC
    $sql = "Select * FROM ?";

    Placeholders aren't supported in table and column names. See DBI "drop table" for a recent, related discussion.

    Use $sql = "SELECT * FROM " . $dbh->quote_identifier($product); instead.

    And I recommend the RaiseError option to connect, which automatically catches DB errors and prints useful error messages.

Re: checkbox and mysql select
by Anonymous Monk on Apr 13, 2008 at 14:49 UTC
    print out the statement, so you can see why
      Ah right.. so I was searching for data in a table called '?'. The quote_identifier seems to work, tho im still struggling with sending the data to the browser.
Re: checkbox and mysql select
by breal (Novice) on Apr 13, 2008 at 19:05 UTC
    Ok, ive figured out how to print to a table:
    print "<table border=2>"; while (my @array = $sth->fetchrow_array()){ print "<tr><td>"; print "@array\n"; print "</td></tr>"; } print "</table>";
    Im sure theres better ways, like giving each field its own cell, but that will serve its purpose for now. Can anyone suggest how I can alter this so that I can print out a column of checkboxes next to each row that I can select to delete the row from the table?

      Maybe by printing the appropriate HTML for it?