Database Update

The next simplest of the common database calls after delete is update. Where delete only needs to be provided with a field value to identify the row or rows to delete, an update command not only needs a value or values to identify the row to update, it also needs the new values to replace into the fields to be updated.

As with the delete example, we'll look at the object oriented mySQLi version and then at the PDO equivalent.

Here's the mySQLi version:

$stmt = $db->prepare("UPDATE tablename SET username=? WHERE id=?");
$affected_rows = $stmt->affected_rows;

and here is the PDO equivalent:

$stmt = $db->prepare("UPDATE tablename SET username=:user WHERE id=:id");
$stmt->bindParam(':id', $id, PDO::PARAM_INT);
$stmt->bindParam(':user', $username);
$affected_rows = $stmt->rowCount();

Apart from the change in the actual SQL itself , there is very little difference in this code from what we had for the delete command. The only significant difference is that we are now binding two values instead of one, with one of those values being a string and the second an integer. Were both values strings then we would change the 'si' value to 'ss' in the mySQLi version and we could discard the two bindParam calls from the PDO version and pass the variables in the execute command instead.

$stmt->execute(array(':user'=>$username, ':id'=>$id));

In addition to showing how to code a simple update call, this example also shows you how to extend the delete call code should you have more than one variable to bind to the delete. Also with now having seen how to bind one or two variables into your database call it is also fairly obvious how you need to change these calls in order to bind three or more variables.


This article written by Stephen Chapman, Felgall Pty Ltd.

go to top

FaceBook Follow
Twitter Follow