Can't find error in my SQL using named parameters

Question

In PHP I'm building, at least trying to, a query using named parameters like so...

$answerNumber = "a1";
$questionNumber = "q2";
$answerText = "Test Answer";

//INSERT QUERY
$sql = "INSERT INTO $questionNumber (:answerNumber) VALUES (:answerText)";
$stmt = $db->prepare($sql);
$stmt->bindValue(':answerNumber', $answerNumber);
$stmt->bindValue(':answerText', $answerText);
$stmt->execute();

$errorInfo = $stmt->errorInfo();

if(isset($errorInfo[2])){
    $error =    $errorInfo[2];
    echo $error;
} else {
    echo "No errors.";
};

But I keep getting an error thrown. The error returned...

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''a1') VALUES ('Test Answer')' at line 1

This `$questionNumber (:answerNumber)` should most likely be `$questionNumber ($answerNumber)` — Funk Forty Niner, Jul 21 '14 at 17:31
placeholders can be used ONLY for values values. You canNOT use placeholders for field/table names, or any other SQL keyword — Marc B, Jul 21 '14 at 17:32
Please see the linked question. It mentions table name rather that column but the reasoning is alike. — Álvaro González, Jul 21 '14 at 17:49
Regarding duplication, the answers on this question are much easier to understand than the explanations on the original question. IMHO of course. — Layne, Jul 21 '14 at 19:38

juergen d · Accepted Answer · 2014-07-21T17:36:31.683

Remove the :

INSERT INTO $questionNumber (:answerNumber) VALUES (:answerText) 
             here------------^

use the column name and not the parameter content. You column name is answerNumber, right? Because first you have to name the columns you want to insert into. Then list the values in that order. Example:

insert into users (id, name) values (1, 'John')

and in PDO

insert into users (id, name) values (:id, :name)

Can't find error in my SQL using named parameters

1 Answers1