Possible to make this code valid/speed up?

Question

I have an enrollment form which takes consumer information, stores it in session, passes from page to page then stores in a database when finished. Originally the table simply listed fields for up to 16 persons but after reading into relational databases, found this was foolish.

I have since created a table named "members" and "managers". Each enrollment will take the information input, store the manager ID in the respective table and place a reference field in each member row containing the manager ID.

While I allow up to 16 members to be enrolled at once, this can range from 1-16.

My best guess is to use a FOR-loop to run though multiple INSERT statements in the event more than 1 member is enrolled.

In the example below, I am using the variable $num to represent the individual member's information and $total to represent the number of all members being enrolled. The code here does not function but am looking for:

a) ways to correct

b) understand if there are more 'efficient' ways of doing this type of INSERT

sample code:

<?php 
    $conn = mysql_connect("localhost", "username", "pw");
    mysql_select_db("db",$conn);

    for ($num=1; $num<=$total; $num++) {

    $sql = "INSERT INTO table VALUES ('', '$clean_f'.$num.'fname', '$clean_f.$num.mi', '$clean_f.$num.lname', '$clean_f.$num.fednum', '$clean_f.$num.dob', '$clean_f.$num.ssn', '$clean_f.$num.address', '$clean_f.$num.city', '$clean_f.$num.state', '$clean_f.$num.zip', '$clean_f.$num.phone', '$clean_f.$num.email')";

    $result = mysql_query($sql, $conn) or die(mysql_error());
    }

    mysql_close($conn);
    header("Location: completed.php");
?>

Answer 1

Generally, putting a query in a loop is bad thing. There is usually a better way. In this case, you should use the multi-insert syntax. Your INSERT isn't working because you didn't specify the fields. I'm assuming the lack of a space between the table name and VALUES is a typo, along with the bad quoting.

INSERT INTO table_name (field1, fname, lname, fednum, ...) 
VALUES ('val1', 'Pete', 'Moss', 1234), 
('val2', 'T.', 'Cupp', 54321), 
('val3', 'Youdid', 'Watt', 787123);

Answer 2

If all of your statements are structurally the same, but with different parameter values, consider using the PDO extension, which supports prepared statements. The benefits of prepared statements can be read here (http://www.php.net/manual/en/pdo.prepared-statements.php), but in general, the same statement will only need to be compiled once, but can be executed as many times as you want with different parameters, which can make your script more "efficient".

Using PDO, your code could look something like:

$db = new PDO('mysql:host=localhost;dbname=db', 'username', 'pw');

$statement = $db->prepare('INSERT INTO tablename (field1, field2, field3, ...) VALUES (?,?,?,?');

for ($num=1; $num<=$total; $num++) {
    $statement->execute(array('val1', 'val2', 'val3', '...'));
}

Answer 3

The solution, if I read you right, is to start with the fixed query string:

$queryString = "INSERT INTO table (field1, field2, ...) VALUES ";

then run a loop to build the malleable part. Putting your values into arrays makes things easier:

$queryInsert = '';
$total = count($value1Array);
while ($i < $total) {
    $queryInsert .= "('$value1Array[$i]','$value2Array[$i]','$value3Array[$i],...), ";
    ++$i;
}

then append to the first query piece:

$queryString = $queryString.$queryInsert;

and trim off the trailing , and you're good to go.