in reply to Inserting values into a MySQL database

You don't _have_ to specify the columns as gav^ rightly points out. BUT! You don't have to use strict either. ;)

The problem is this line: 

my($TN,$Question,$AnswerA,$AnswerB,$AnswerC,$AnswerD,$Correct)=...

[download]
What happened to Answer E? :)

What you end up with is trying to insert only 7 variables where DBI is expecting 8.

In the long run though, follow the advice of Chmrr, dws, and Ovid. It will keep you out of trouble.

jeffa

L-LL-L--L-LL-L--L-LL-L--
-R--R-RR-R--R-RR-R--R-RR
F--F--F--F--F--F--F--F--
(the triplet paradiddle)

P.S. Brownie points to adamsj for spotting this error too.

P.P.S Or i could be completely wrong about Answer E. I now see that you are indeed assigning the variable $AnswerE to "NULL", which is not the same as NULL. Use undef instead, but you really should specify $AnswerE in the split, i think. How about this instead: 

foreach my $element(@Array){
   chomp $element;
   # define $STH as you did
   $STH->execute(split/\|/,$element,8);
}

[download]
As long as you really include Answer E with the rest in the data file, this code allows you to avoid using temp variables - just pass the output of split. Of course, you might want to do some validation, passing the contents without validating them is not a good thing.

Replies are listed 'Best First'.
Re: (jeffa) 2Re: Inserting values into a MySQL database
by gav^ (Curate) on Jan 14, 2002 at 07:48 UTC
    Good point. I make sure I specify the columns all the time because I have been caught out by this before.

    I had a quick check through the MySQL docs and I noticed for versions > 3.22.10 you can use SQL like: 

    INSERT INTO table SET x = 1, y = 2, z = 3;

    [download]
    Which is interesting, but I'm not sure if it is better, and I'm pretty sure it isn't portable. There is a couple of other things that are cool (I never noticed this stuff before!) such as inserting multiple records in 1 statement: 
    INSERT INTO table (x,y,z) VALUES (1,2,3), (4,5,6), (7,8,9);

    [download]
    Of course this is only really handy if you know in advance how many records you want to insert.

    What I forgot to mention is that you should really be using: 

    INSERT DELAYED INTO table (x,y,z) VALUES (1,2,3);

    [download]
    if you are doing multiple inserts as this can give you quite a bit of a performance boost.

    gav^

[reply]
[d/l]
[select]
      just a note that i've found the "insert into table set" syntax more reliable than the standard forms for mysql. not portable, of course, but you can use placeholders in the statement. one of the reasons i started moving away from mysql.
[reply]

In Section Seekers of Perl Wisdom