Need help INSERT record(s) MySQL DB

Question

I have an online form which collects member(s) information and stores it into a very long MySQL database. We allow up to 16 members to enroll at a single time and originally structured the DB to allow such.

For example:

If 1 Member enrolls, his personal information (first name, last name, address, phone, email) are stored on a single row.

If 15 Members enroll (all at once), their personal information are stored in the same single row.

The row has information housing columns for all 'possible' inputs. I am trying to consolidate this code and having every nth member that enrolls put onto a new record within the database.

I have seen sugestions before for inserting multiple records as such:

INSERT INTO tablename VALUES (('$f1name', '$f1address', '$f1phone'), ('$f2name', '$f2address', '$f2phone')...

The issue with this is two fold:

1. I do not know how many records are being enrolled from person to person so the only way to make the statement above is to use a loop
2. The information collected from the forms is NOT a single array so I can't loop through one array and have it parse out. My information is collected as individual input fields like such: Member1FirstName, Member1LastName, Member1Phone, Member2Firstname, Member2LastName, Member2Phone... and so on

Is it possible to store information in separate rows WITHOUT using a loop (and therefore having to go back and completely restructure my form field names and such (which can't happen due to the way the validation rules are built.)

Answer 1

What you're trying to do could be simpler, but to solve the problem, you can join the user information into one variable, separated by a char of your choice and send it to Mysql DB...

$user1 = $f1name . ';' . $f1address . ';' . $f1phone;
$user2 = $f2name . ';' . $f2address . ';' . $f2phone;
$user3 = $f3name . ';' . $f3address . ';' . $f3phone;

INSERT INTO table-name VALUES('$user1','$user2','$user3')

To extract, just "explode" the value by the ";". If you use the same order for all users data, and if you send a verification string in case one user leaves a field blank, works just fine :) humm... this work's just fine if the user isn't allowed to use ";" as "personal data" :)

Hope it helps U!

Answer 2

If you form's structured so that all the fields are numbered properly, so that a "firstname #1" is matched up with all the other "#1" numbered fields, then a loop is the simplest solution.

start_transaction();
$errors = false;
for ($i = 1; $i <= 16; $i++) {
   if (... all $i fields are properly filled in ...) {
      $field = $_POST["field$i"];
      $otherfield = $_POST["otherfield$i"];
      etc...

      ... insert into database ...
   } else {
      ... handle error condition here
      $errors = true;
   }
}
if (!$errors) {
   commit_transaction();
} else {
   rollback();
}

If they're numbered randomly, so that firstname1 is matched with lastname42 and address3.1415927, then you'd have to build a lookup table to map all the random namings together, and loop over that

followup per comment:

well, if you absolutely insist on maintaining this database structure, where each row contains 16 sets of repeated firstname/lastname/etc.. records, then you'd do something like this:

$first = true;
for ($i = 1; $i <= 16; $i++) {
    if (fields at position $i are valid) {
       $firstname = mysql_escape_real_string($_POST["F{$i}name"]);
       $lastname = mysql_real_escape_string($_POST["F{$i}lastname"]);
       if ($first) {
          $dbh->query("INSERT INTO table (f{$i}name, f{$i}lastname) VALUES ($firstname, $lastname);"
          $recordID = $dbh->query("SELECT last_insert_id();");
          $first = false;
       } else {
          $dbh->query("UPDATE table SET f{$i}name=$firstname, f{$i}lastname=$lastname WHERE idfield=$recordID");
       }
    }
}

It's ugly, but basically:

1. loop through the form field sets until you find a valid set (all required fields filled in, valid data entered, etc..
2. Insert that data set into the database to create the new record
3. retrieve ID of that new record
4. continue looping over the rest of the fields
5. for every subsequent set of valid records, do an update of the previously created record and add in the new fieldset data.

Though, honestly, unless you've got some highly offbeat design need to maintain a single table with 16 sets of repeated columns, you'd be better off normalizing a bit, and maintain two seperate tables. A parent "enrollment" table, and a child "members" table. That way you can create the parent enrollment table, then just insert new children as you encounter them in the form.

update #2:

well, a simplified form of a normalized layout would be:

signups (id, name, etc...)
signup_members (id, signup_id, firstname, lastname)

and you'd pull the full signup record set with the following query:

SELECT signups.id, signups.name, signup_members.id, firstname, lastname
FROM signups
LEFT JOIN signup_members ON signups.id = signup_members.signup_id
ORDER BY ...

That would give you a series of rows, one for each 'member' signup. To build the CSV, a simple loop with some state checking to see if you've reached a new signup yet:

$oldid = null;
$csv = ... put column headers here if you want ...
while ($signup = $result->fetchrow()) {
    if ($signup['signups.id'] != $oldid) {
        // current signup doesn't match previous seen id, so got a new signup record
        $csv .= "\n"; // start new line in CSV
        $csv .= ... add first few columns to new csv row ...
        $oldid = $signup['signups.id']; // store new record id
    } else {
        $csv .= ... add extra member columns to current csv row ...
    }
 }   

Answer 3

I think you might want to look at "variable variables":

http://php.net/manual/en/language.variables.variable.php

Then you could conceivably loop through from 1 to 15, without having to rename your form fields.