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.
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.