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

I'm using Perl DBI, CGI.pm and a MySql database that is updated from an html form.. What I want to do is to give users the ability to enter multiple values for the same field in the html form, and each of these should insert a new record into a mysql table. For instance, I have up to five "member" (name="member") input fields in the html form. The user can enter 1-5 members. Each of these should create a new, unique row in the mysql table "member". I believe there has to be some kind of array in the perl DBI script that handles this, i.e something that loops through all "member" values that it receives from the html form and creates an INSERT INTO statement for each. But I am not sure how this is done in practice. And I am not sure exactly what the form should look like in order to accomplish this. *Many* thanks for any help!

Replies are listed 'Best First'.
Re: Perl DBI, multiple values question
by UnderMine (Friar) on Nov 22, 2002 at 15:06 UTC
    Please can you clarify exactly what you want to do?

    Are you saying that you have a form with multiple values (ie. member, username, firstname, surname, password and you wish to enter) these then need to be split out into one row per key pair.

    Or

    Are you saying that your have five member only records each to be added as a seperate record

    Or

    A combination of the two above (ie. member, username, firstname, surname, password five times over)
    This will require that there is some relationship between each of the fields in a row. ie name=member_1, name=username_1, name=member_2, name=username_2 in the form input fields.

    Some additional information about the database schema would also be helpful.

    Hope it helps
    UnderMine

[reply]
      Sorry. I'll try to explain:

      The form has 5 input fields for "subject" (forget "member"):
      input type="text" name="subject" value="" ... repeated 5 times

      The user can enter data in 1-5 five of these.

      This gets passed on to the DBI script.
      The script should produce one INSERT INTO statement for each value entered, such as:
      "INSERT INTO subjects VALUES ($FORM{'subject'})";

      (the table "subjects" consists simply of auto-incremented id and subject).

      An easy way would of course be to name the input fields subject1, subject2 etc., but it would be better to solve it with some kind of loop.

      Again, many thanks for your help!

[reply]
        The easiest way is to do this is probably :- 
        my $dbh=DBI->connect(.....);
my $query = "INSERT INTO subjects VALUES (?)"; 
my $sth=dbh->prepare($query);

foreach my $subject (split /\0/,$form{'subject'}) {
     $rv  = $sth->execute($subject);
}
$sth->finish();
$dbh->disconnect();

        [download]
        Hope this helps
        UnderMine
[reply]
[d/l]
Re: Perl DBI, multiple values question
by rdfield (Priest) on Nov 22, 2002 at 15:04 UTC
    split the CGI param with \0 and iterate over the resulting array, inserting as you go.

    rdfield

[reply]
[d/l]

Back to Seekers of Perl Wisdom