Beefy Boxes and Bandwidth Generously Provided by pair Networks
We don't bite newbies here... much
 
PerlMonks  

Voting script using MySQL and DBI

by lagrenouille (Acolyte)
on Aug 31, 2002 at 15:20 UTC ( [id://194343]=perlquestion: print w/replies, xml ) Need Help??

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

G'day all

I have begun re-writing the voting script I have been posting about... and to start, I followed the suggestions of posters who recommended I re-design the database.

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)
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; }
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

Replies are listed 'Best First'.
Re: Voting script using MySQL and DBI
by dws (Chancellor) on Aug 31, 2002 at 17:01 UTC
    Several problems/suggestions:
    1. When a DBI call fails, the error is not in $!. See the DBI docs.
    2. There's an option to connect() that will have DBI provide more error information to you. See the DBI docs.
    3. I don't see you issuing a disconnect, so perhaps it's possible to reuse $dbh, and avoid reconnecting from within print_form.
    4. Arrange to print the Content-type: header before you might have to issue error message.
    5. use CGI; and use the routines it provides to help you print the content header and HTML.
    6. Are you sure you aren't off-by-one when indexing into @options ? I would think that the string for "Option 1" would be at $options[0], not $options[1].
    7. You're embedding entirely too many "magic numbers" (including an IP address and background/font colors). That makes using and changing the script error-prone. Lift these values out into a set of configuration variables at the top of the script.
      I don't see you issuing a disconnect, so perhaps it's possible to reuse $dbh, and avoid reconnecting from within print_form.

      That should AFAIK be correct, as long as it is one Db then issuing the connect() command once is sufficient for multiple queries.

      As for the error info try something like:

      my $dbh = DBI->connect ("DBI:${dbtype}:${dbname}", "$dbuser", "$dbpass +") || die $DBI::errstr;
      This will give you more detail as 'to what is going on'.
        Or even better:
        my $dbh = DBI->connect( "DBI:${dbtype}:${dbname}", $dbuser, $dbpass), {RaiseError => 1}, ); # oops! $dbh->prepare('slecet foo from baz');
        Now every database method will raise an error automatically if one is encountered - Laziness! And get out of the habit of putting quotes around variables when you don't need to.

        jeffa

        L-LL-L--L-LL-L--L-LL-L--
        -R--R-RR-R--R-RR-R--R-RR
        B--B--B--B--B--B--B--B--
        H---H---H---H---H---H---
        (the triplet paradiddle with high-hat)
        
      G'day dws

      Cheers for editing the post as well, looks better thanks.

      1. ok, will investigate thanks, I noticed when I first started the error passed to the logs was of a different format, however it still die'ed appropriately.

      2. As for 1

      3. Yeah, I thought so too and added the extra connect line in an attempt to get it to read out the variables... (sorry should have removed it before posting)

      4. Why is this ? If I were to enable carp(fatalsToBrowsers) then obviously a content type header is required first... or is it just to make sure something is printed if the script dies ? I have an error handling subroutine, which prints headers...

      5. I was initially using them, but prefered the output I could generate with raw print commands... I guess it's what you're used to... any immediate advantages, CGI.pm over raw HTML ?

      6. Ah yes, yes I was, I found that soon after I posted that and still no joy... the script still fails to print any of the variables when requested, meaning they were not correctly retrieved from the mySQL database, something I am able to do by copying and pasting commands from the script into shell based mySQL...

      7. I know, to some extent that's just because this script is for a very specific situation, the other reason is, I've just taken it back to step 1 in an attempt to get the mySQL retrieval working...

      Cheers once again
      lagrenouille
        4. Why is this ? If I were to enable carp(fatalsToBrowsers) then obviously a content type header is required first... or is it just to make sure something is printed if the script dies ? I have an error handling subroutine, which prints headers...

        As long as you have a guaranteed way of getting a content type header out first, you're O.K.

        5. I was initially using [CGI methods to emit HTML], but prefered the output I could generate with raw print commands... I guess it's what you're used to... any immediate advantages, CGI.pm over raw HTML ?

        If templating is overkill for you, and if you can emit code by hand that suites your needs, then do so. Some people find that using CGI methods makes it easier to generate correct HTML. If you're going to stick this hand-generated HTML, consider carefully the snippet that jeffa provides elsewhere in this thread.

(jeffa) Re: Voting script using MySQL and DBI
by jeffa (Bishop) on Aug 31, 2002 at 17:43 UTC
    Why are you hard coding each individual option? After you retrieve your opions from the database, you store them in an array, so why not loop like so:
    use strict; my @options = ( 'Perfect for me', 'Okay, but my first choice was taken', 'My real name, blame my parents', 'Cowboy Neal', ); for (0..$#options) { print "<tr>\n<td align='left' bgcolor='#8f4141'>\n"; print "<font color='#eabf12' size='2'>\n"; print qq|<input name="in_vote" type="radio" value="option_$_">$opti +ons[$_]</font>\n|; print "</td>\n</tr>\n"; }
    This is much better, not only because you do not repeat redundant code, but because you are working on N options, not a finite number, like six. But multiple print statements are icky - learn to avoid them now. One alternative is to let CGI.pm do the hard work for you. Also, where are your opening and closing <table> tags? And why are you exiting at the end of the subroutine?

    Lastly, your database schema is, i am sorry to say, not very good at all. At it's very simplest, you only need three tables (not including tables for user info):

    Poll
    1. id (primary key)
    2. user_id (foreign key from user table - user who started poll)
    3. description
    4. date_started
    5. date_ended
    Option
    1. id (primary key)
    2. poll_id (foreign key to poll table - which poll does this option belong to)
    3. number (what order to place in poll)
    4. description
    Vote
    1. user_id (foreign key to user table - who voted on this option)
    2. option_id (foreign key to option table - which option was voted on)
    If you wanted to get a list of the options for the poll whose id is 42, you could issue the following query:
    select option.description from option inner join poll on option.poll_id = poll.id order by option.number
    If you wanted to get the taly of votes from poll 42 ... well, i will leave that as an excercise. >:) Working with properly designed database tables requires a lot of dilligent effort. Maybe you should be working on something a tad bit less challenging, like learning CGI.pm and templating solutions, before you jump right into databases.

    jeffa

    L-LL-L--L-LL-L--L-LL-L--
    -R--R-RR-R--R-RR-R--R-RR
    B--B--B--B--B--B--B--B--
    H---H---H---H---H---H---
    (the triplet paradiddle with high-hat)
    
Re: Voting script using MySQL and DBI
by Baz (Friar) on Aug 31, 2002 at 15:42 UTC
      Cheers, I'll keep that link...
      but I have no problems with the mySQL its the DBI statements to connect to it.

      Namely, the first query works, why don't the second two ? I assume it is DBI related as it checks out fine manually...

      Cheers
      lagrenouille
Re: Voting script using MySQL and DBI
by blokhead (Monsignor) on Aug 31, 2002 at 17:55 UTC
    I highly suggest getting the HTML out of your code and using templates instead. HTML::Template and Text::Template are among many available systems. Using templating has saved me many headaches in CGI/DBI programming.

    blokhead

      G'day blokhead

      yeah, I had considered a template... however this poll script is intended to be sitting within an index page, that has options down each side and has the vote script included via SSI.

      Is it still ok to use templates given that the html I am printing is going to form merely a part of a much bigger html document ?

      Cheers
      lagrenouille
Re: Voting script using MySQL and DBI
by greywolf (Priest) on Aug 31, 2002 at 19:09 UTC
    You might want to replace your $sth->execute() with the following:
        unless ($sth->execute()) { die $dbh->errstr() }
    This will give you an error if your sql statement is bad for some reason.

    If you're not already doing it try:
        use CGI::Carp qw(fatalsToBrowser);
    This will send all your errors to the browser. Remember to remove this line before the script goes live.

    mr greywolf
      G'day greywolf

      ok thanks, i'll try that...
      ( I know the mySQL commands are good, I tested them from the shell by copying and pasting, worked fine... )

      I know about CGI::carp, I am using the script in an SSI situation and to have huge error messages printed out disturbs the tables a lot and is hard to read... so i've just been reading it out of the server logs

      Thanks for your help!

      Cheers
      lagrenouille

Log In?
Username:
Password:

What's my password?
Create A New User
Domain Nodelet?
Node Status?
node history
Node Type: perlquestion [id://194343]
Approved by Mr. Muskrat
help
Chatterbox?
and the web crawler heard nothing...

How do I use this?Last hourOther CB clients
Other Users?
Others pondering the Monastery: (7)
As of 2024-03-28 08:08 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    No recent polls found