Current layout:

table 1: vote_count
elements:
 current_vote (a counter, to identify which data to use)

table 2: vote_misc
elements:
 vote_no (used as an ID, to search for the data for a specific poll nu
+mber)
 description (the descriptive text above the form)
 begun_by (the user who begun the poll)
 date_begun (the date the poll started)
 date_ended (the date the poll concluded)


table 3: vote_text
elements:
 vote_no (as for vote_misc)
 option_1 (text for the option number)
 (through to)
 option_6 (text for the option number)

table 4: vote_numbers
elements:
 vote_no (as for vote_misc)
 option_1 (numbers for the option number)
 (through to)
 option_6 (numbers for the option number)
[download]

With the advent of multiple tables, and my limited DBI understanding and knowledge I'm having a few problems actually returning the data in my queries...

Here is what I have been doing...

  ## Read the current vote number from the mySQL database.
  my $dbh = DBI->connect("DBI:mysql:$database","$username","$password"
+) || error_fatal ("[ vote.cgi: Admin: Unable to open mySQL database -
+ $! ]");
  my $sth_1 = $dbh->prepare("SELECT current_vote FROM vote_count");
  $sth_1->execute();
  my @vote_no = $sth_1->fetchrow_array;
  $sth_1->finish();
  my $current_vote = $vote_no[0];

# Subroutines.

  ## Form printing subroutine.

  sub print_form
      {
      ### Read the data from the mySQL database.
      my $dbh = DBI->connect("DBI:mysql:$database","$username","$passw
+ord") || error_fatal ("[ vote.cgi: Admin: Unable to open mySQL databa
+se - $! ]");
      my $sth_2 = $dbh->prepare("SELECT description FROM vote_misc WHE
+RE vote_no='$current_vote'");
      $sth_2->execute();
      my $description = $sth_2->fetchrow_array();
      $sth_2->finish();

      my $sth_3 = $dbh->prepare("SELECT option_1,option_2,option_3,opt
+ion_4,option_5,option_6 FROM vote_text WHERE vote_no='$current_vote'"
+);
      $sth_3->execute();
      my @options = $sth_3->fetchrow_array();
      $sth_3->finish();

      ### Print the submission form using the values gathered from the
+ mySQL database.
      print "Content-type: text/html\n\n";
      print "<!-- Begin form - vote.cgi -->\n";

      ### Heading.
      print "<form action='http://192.168.0.2/cgi-bin/ssi/vote3.cgi' m
+ethod='post'>\n";
      print "<tr>\n<td align='center' bgcolor='#8f4141'>\n";
      print "<font color='#ffffff' size='2'>Vote</font>\n";
      print "</td>\n</tr>\n";

      ### Description.
      print "<tr>\n<td align='center' bgcolor='#8f4141'>\n";
      print "<font color='#eabf12' size='2'>$description</font>\n";
      print "</td>\n</tr>\n";
        
      ### Option 1.
      print "<tr>\n<td align='left' bgcolor='#8f4141'>\n";
      print "<font color='#eabf12' size='2'>\n";
      print "&nbsp;<input name='in_vote' type='radio' value='option_1'
+> &nbsp; &nbsp; $options[1]</font>\n";
      print "</td>\n</tr>\n";

      ### Option 2.
      print "<tr>\n<td align='left' bgcolor='#8f4141'>\n";
      print "<font color='#eabf12' size='2'>\n";
      print "&nbsp;<input name='in_vote' type='radio' value='option_2'
+> &nbsp; &nbsp; $options[2]</font>\n";
      print "</td>\n</tr>\n";

      ### Option 3.
      print "<tr>\n<td align='left' bgcolor='#8f4141'>\n";
      print "<font color='#eabf12' size='2'>\n";
      print "&nbsp;<input name='in_vote' type='radio' value='option_3'
+> &nbsp; &nbsp; $options[3]</font>\n";
      print "</td>\n</tr>\n";

      ### Option 4.
      print "<tr>\n<td align='left' bgcolor='#8f4141'>\n";
      print "<font color='#eabf12' size='2'>\n";
      print "&nbsp;<input name='in_vote' type='radio' value='option_4'
+> &nbsp; &nbsp; $options[4]</font>\n";
      print "</td>\n</tr>\n";

      ### Option 5.
      print "<tr>\n<td align='left' bgcolor='#8f4141'>\n";
      print "<font color='#eabf12' size='2'>\n";
      print "&nbsp;<input name='in_vote' type='radio' value='option_5'
+> &nbsp; &nbsp; $options[5]</font>\n";
      print "</td>\n</tr>\n";

      ### Option 6.
      print "<tr>\n<td align='left' bgcolor='#8f4141'>\n";
      print "<font color='#eabf12' size='2'>\n";
      print "&nbsp;<input name='in_vote' type='radio' value='option_6'
+> &nbsp; &nbsp; $options[6]</font>\n";
      print "</td>\n</tr>\n";

      ### Submit button.
      print "<tr>\n<td align='center' bgcolor='#8f4141'>\n";
      print "<font size='2'>\n";
      print "&nbsp; &nbsp; <input type='submit' value=' Vote '> &nbsp;
+</font>\n";
      print "</td>\n</tr>\n";

      ### Ender.
      print "<input name='posted' type='hidden' value='vote'>\n";
      print "</form>\n";
      print "<!-- End form - vote.cgi -->";
        
      exit;
  }
[download]

That is what I have so far, it returns no syntax errors, but the second and third queries return null values, and hence nothing is printed out to the HTML page.
(I am using strict, CGI, DBI and the variables used to open the database are ok)

Furthermore, the first query was working, so I see no reason or difference between the first and subsequent queries...

Is this a suitable design model for the database ?
What is the difference between 1st and subsequent queries ?
How can I get all three queries to work ?
Any other suggestions for general improvements ?

Cheers in advance
lagrenouille

Edit by dws for tag cleanup