PDO: Updating MySQL Using Prepared Statements.

This a small tutorial on how to update rows in a MySQL database using prepared statements. In this example, I will be using PHP’s PDO object.

For example, let us say that we have a table called cars and that we want to update the row with the ID “90” (it’s Primary Key). In this particular code example, we will be updating the “model” column to “Civic”.

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37

<?php   //Our MySQL connection details. $host = 'localhost'; $user = 'root'; $pass = ''; $database = 'test';   //PDO options. These are optional. $options = array(     PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,     PDO::ATTR_EMULATE_PREPARES => false );   //Connect to MySQL and create our PDO object. $pdo = new PDO("mysql:host=$host;dbname=$database", $user, $pass, $options);   //Our UPDATE SQL statement. $sql = "UPDATE `cars` SET `model` = :model WHERE id = :id";   //Prepare our UPDATE SQL statement. $statement = $pdo->prepare($sql);   //The Primary Key of the row that we want to update. $id = 90;   //The new model value. $model = 'Civic';   //Bind our value to the parameter :id. $statement->bindValue(':id', $id);   //Bind our :model parameter. $statement->bindValue(':model', $model);   //Execute our UPDATE statement. $update = $statement->execute();

A drill-down of the code above:

1. We connect to MySQL using the PDO extension.
2. We create an UPDATE SQL statement using placeholders. In this case, we used the placeholders :model and :id.
3. We prepare our UPDATE statement.
4. We set $id to 90 and $model to ‘Civic’.
5. We bind our variables to the placeholders that we created in Step 2.
6. We execute the prepared statement. It is at this stage that the row is updated.