使用PDO命名参数抛出错误：SQLSTATE [HY000]：常规错误：1接近“（”：语法错误

Question

I am attempting to update my SQLite database using:

$insertion = $db->prepare("UPDATE roles SET ($set_string) WHERE entryDate = :entryDate AND name = :name");
$insertion->execute($assoc_data);   

Here $set_string is a string of attributes and parameters, which are what I want. A print_r() of $set_string yields:

name = :name,
entryDate = :entryDate,
development = :development,
adhoc = :adhoc,
mentoring = :mentoring,
documentation = :documentation,
codeReview = :codeReview,
meetings = :meetings,
learning = :learning,
waste = :waste,
workPrioritization = :workPrioritization,
management = :management,
applicationTraining = :applicationTraining

$assoc_data is an associative array with all the data that I want to update the database with:

Array (
    [name] => Bobby Bob
    [entryDate] => 2015-08-11
    [development] => 0
    [adhoc] => 0
    [mentoring] => 0
    [documentation] => 0
    [codeReview] => 0
    [meetings] => 0
    [learning] => 0
    [waste] => 0
    [workPrioritization] => 0
    [management] => 0
    [applicationTraining] => 100
)

I get the following error:

Exception: SQLSTATE[HY000]: General error: 1 near "(": syntax error

Could someone pinpoint what the error is? I guess I don't quite understand how to use named parameters.

Answer 1

So your first problem is, that your SQL UPDATE syntax is wrong. You don't need to use parentheses, as you can see from the documentation:

UPDATE [LOW_PRIORITY] [IGNORE] table_reference
    SET col_name1={expr1|DEFAULT} [, col_name2={expr2|DEFAULT}] ...
    [WHERE where_condition]
    [ORDER BY ...]
    [LIMIT row_count]

Second placeholders have to be unique. This means you can only use them once for one value. As you can read in the PHP manual:

You must include a unique parameter marker for each value you wish to pass in to the statement when you call PDOStatement::execute().

And last name is a mysql reserved word, so you should put ` around it.

Means first remove your parentheses and second use unique placeholders (You can't have 2x :name and :entryDate) and put your backticks around reserved words.