PDO insert using an array loop

Question

I've seen a few questions similar to mine but none that use an array loop for both the table columns and values to insert. I'm fairly new to PDO so I'm a bit lost on how to make this work.

I am creating a form where students enter answers to multiple choice questions and their answers are stored in a database. There are 68 questions. Right now I'm hard coding every single column and value like this:

 $stmt = $DBH->prepare("INSERT INTO my_table (student, q1, q2, etc...) 
  VALUES (?, ?, ?, etc...) ");
$stmt->execute(array($student_name, $answer[1], $answer[2], $answer[3], 
     etc... through  $answer[68]));

This works fine but there has to be an easier way to do this without needing to type every single instance. I'd like to be able to loop through the numbers for the table columns (q1, etc. above) as well as the answer variable array. I just have no idea how to approach this.

Even as far as automatically having the correct number of placeholder question marks - is that even possible?

The problem is in your database design: you shouldn't have 68 columns containing similar data, you should have a separate table with a row for each question. — IMSoP, Apr 13 '14 at 19:52
You can build the `?` string programatically; as long as you have full control of the text you're building it from (which in this case is just `,?`), there's no risk of injection. — Dave, Apr 13 '14 at 19:53
Also you can use `?` for escaping column names too. I'm not sure how portable it is, but in MySQL you can even use `?` within the table name! (don't do that though) — Dave, Apr 13 '14 at 19:56
You sure it's OK to use `?` for column names? http://stackoverflow.com/questions/182287/can-php-pdo-statements-accept-the-table-name-as-parameter discusses this. — RobP, Apr 13 '14 at 20:39

score 0 · Answer 1 · answered Apr 13 '14 at 20:41

While @IMSoP is correct on the larger point, it seems to me that all you need here is an array with $student_name followed by all of the $answer array.

$stmt->execute(array_merge(array($student_name), $answer)));

does that. You can use array_slice if you want some but not all of $answer.

PDO insert using an array loop

1 Answers1