Syntax error in PDO insert using foreach loop

Question

I'm trying to add some survey results to a database using PDO. Because the survey has 50 questions I thought I would add them using a PHP foreach loop to add the result of each question.

There are two different tables. One is the respondent table that contains users details (name, email etc). And the other table is a responses table that shows the respondents answers to the survey and is linked by their ID.

Everytime I run this I get the error:

Uncaught PDOException: SQLSTATE[42000]: Syntax error or access violation: 1064

The first statement execution runs fine and I can see the users details added to the database, but I cannot get the second statement to run successfully.

//Add respondent to database
$sth = $dbh->prepare("
    INSERT INTO `respondents` (`id`,`firstname`,`lastname`,`email`) VALUES (?,?,?,?);
    INSERT INTO `responses` (`rid`) VALUES (?)
");

$sth->execute([$id, $first, $last, $email, $id]);

//Add survey results
$sth = $dbh->prepare("
    INSERT INTO `responses` (?) VALUES (?)
    WHERE `rid` = ?
");

foreach ($responses as $key => $response) {
    $sth->execute([$key, $response, $id]);
}

I'm a bit new to PDO and MySQL so I'm sure this isn't the best way to do this so any help would be appreciated. Thanks.

Full error:

Fatal error: Uncaught PDOException: SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near ''1') VALUES ('1'); WHERE rid = 'df3f745ad872d72aa6466e571af743eb''

I'm seeing an increasing number of questions about the non-existing `INSERT INTO WHERE` statement. Is there a typo in a popular book or tutorial? — Álvaro González, Apr 27 '17 at 15:37
@ÁlvaroGonzález Maybe from `INSERT INTO abc SELECT * FROM xyz WHERE ....` + `copy&paste` — JustOnUnderMillions, Apr 27 '17 at 15:46
Beside that you cannot bind a column name. though I have no idea why $key is equal to 1 — Your Common Sense, Apr 27 '17 at 15:52
$key should be 1 as far as I'm aware. I'm still trying to wrap my head around how I would do this using switch and case.. — mikey242, Apr 27 '17 at 16:43

Syntax error in PDO insert using foreach loop

0 Answers0