How to create a prepared statement for UPDATE query
Sometimes we need to update not all fields but only certain ones. Say, we have an array with values we want to add to the query.
The task is not very complex, but one have to keep in mind the SQL injection against which prepared statements won't help. To protect from that, all field names should be checked against a pre-compiled list of allowed values. The rest is simple. So the algorithm would be:
- Define an array with allowed values
- Loop over the source array and create a SET statement for SQL dynamically, based on the allowed fields list
- Respective values should be added to the designated array to be used in the execute()
So, imagine we have $_POST array with several fields. Here is the code to insert them into database safely:
// the list of allowed field names
$allowed = ["name","surname","email"];
// initialize an array with values:
$params = [];
// initialize a string with `fieldname` = :placeholder pairs
$setStr = "";
// loop over source data array
foreach ($allowed as $key)
{
if (isset($_POST[$key]) && $key != "id")
{
$setStr .= "`$key` = :$key,";
$params[$key] = $_POST[$key];
}
}
$setStr = rtrim($setStr, ",");
$params['id'] = $_POST['id'];
$pdo->prepare("UPDATE users SET $setStr WHERE id = :id")->execute($params);}
Related articles:
- SELECT query with PDO
- INSERT query using PDO
- UPDATE query using PDO
- PDO Examples
- How to connect to MySQL using PDO
- Authenticating a user using PDO and password_verify()
- Select the number of rows using PDO
- How to check if email exists in the database?
- How to create a WHERE clause for PDO dynamically
- Getting a nested array when multiple rows are linked to a single entry
- DELETE query using PDO
- Adding a field name in the ORDER BY clause based on the user's choice
- How to execute 1000s INSERT/UPDATE queries with PDO?
- INSERT helper function for PDO Mysql
- PDO Examples
- PDO Examples
Got a question?
I am the only person to hold a gold badge in
,
and
on Stack Overflow and I am eager to show the right way for PHP developers.
Besides, your questions let me make my articles even better, so you are more than welcome to ask any question you have.
Add a comment
Please refrain from sending spam or advertising of any sort.
Messages with hyperlinks will be pending for moderator's review.
Markdown is now supported:
>before and an empty line after for a quote