Beefy Boxes and Bandwidth Generously Provided by pair Networks
laziness, impatience, and hubris

Array values into a database help!

by Anonymous Monk
on Nov 28, 2010 at 04:29 UTC ( [id://874060]=perlquestion: print w/replies, xml ) Need Help??

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

Hi Monks!
I have this array with user numbers that will be passed by a html form. I have to insert them into a database, but I am afraid that instead of the expected 4 user's numbers I could be getting 2 or 3 or 1, in that case the bindings "?" will fail right? How could I pass a null to the database if I dont get the 4 numbers without breaking the code?
... my $dbh_in = test->connect_mysql(); my $sth_in = $dbh_in->prepare("insert into my_table(name_1,name_2,name +_3,name_4) values(?, ?, ?, ?) where user='$user'")" + or &die("Can't add data, please try again later! ",$db +h_in->errmsg); foreach my $name (@names) { #got this array from the form my @fields = $name; $sth->execute(@fields); } $dbh_in->disconnect();

Thanks for looking!

Replies are listed 'Best First'.
Re: Array values into a database help!
by ikegami (Patriarch) on Nov 28, 2010 at 05:03 UTC
    You can either adjust the values or adjust the query.

    Three ways to adjust the values:

    my $sth = $dbh_in->prepare(" INSERT INTO my_table ( name_1, name_2, name_3, name_4 ) VALUES ( ?,?,?,? ) "); $sth->execute($fields[0], $fields[1], $fields[2], $fields[3]); $sth->execute(@fields[0..3]); $sth->execute((@fields, undef, undef, undef, undef)[0..3]);

    One way to adjust the query:

    my $sth = $dbh_in->prepare(" INSERT INTO my_table ( " . join(', ', map 'name_'.$_, 1..@fields) ." ) VALUES ( " . join(',', ('?') x @fields) ." ) "); $sth->execute(@fields);

    (I'm assuming WHERE user=... shouldn't be there? If it is, be sure to use a placeholder for that too!!!)

    Update: Added more ways of adjusting the values.

      On this line:
      $sth->execute((@fields, undef, undef, undef, undef)[0..3], $user);
      If any of the 4 values are not true it will send a null/empty value to the database, or if some are true, it will send that value. else the rest will be null/empty, right? If its yes, thank you!!
        Exactly, although I've added simpler alternatives to the list slice to my original post. The list slice approach is useful if you want a default of something other than undef (NULL).
      How would this code work with an SQL UPDATE instead of an INSERT?
Re: Array values into a database help!
by chrestomanci (Priest) on Nov 28, 2010 at 21:04 UTC

    The normal way of storing lists in a database, is to store the members in a separate table from the table they relate to, and then having a foreign key relationship to join each list entry with table row it relates to. (This is why it is called a relational database.)

    The way you have done it with four columns for the four possible array entries will be fine, but if you later discovered that there could be many more entries in the array, you would have to modify that table, and you would waste a lot of space with extra slots that are unused.

    On the other hand, the way you have done it will be faster, as it avoids the need to do a database join, and if you are sure there will only ever be four entries in the array, then the small penalty of wasted slots will be small.

      but on the other other hand, maintaining data in unnormalised table is horrible and easy to you end up essentially having to enforce a relational join in code, rather than at the structural/DB level. any and every operation on the table will have the capacity to ruin/corrupt the data.
      i think it essential that the relational model and normalisation is understood and first tried, before if/any decision to de-normalise. such de-normalisation decisions should be based on quantitative performance measurements; not on mere pondering. As a possible guideline using current technology, millions of rows in a normalised table with two or more relationships (joins) would make for a possible candidate for de-normalisation. And if performance metrics and other factors dictated de-normalisation, central functions are developed that will always be used to Create Read Update Delete the table, to avoid data corruption.
      not trying to be too hard-up about this..but maintaining a relational database through code is not easy or desirable at the outset. Better chance of success using normalised tables instead.
      the hardest line to type correctly is: stty erase ^H

Log In?

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

How do I use this?Last hourOther CB clients
Other Users?
Others learning in the Monastery: (3)
As of 2024-05-22 17:35 GMT
Find Nodes?
    Voting Booth?

    No recent polls found