How to use PDO to update the database?
1) Write the regular update statement and again, instead of values, assign the named placeholders. For example:
|
1 2 3 |
$sql = "UPDATE `users` SET `city`= :city, `phone` = :tel WHERE `id` = :id"; |
2) Prepare the query:
|
1 |
$query = $dbh->prepare($sql); |
3) Bind the parameters:
|
1 2 3 |
$query -> bindParam(':city', $city, PDO::PARAM_STR); $query -> bindParam(':tel' , $tel , PDO::PARAM_INT); $query -> bindParam(':id' , $id , PDO::PARAM_INT); |
4) Define the bound values:
|
1 2 3 |
$tel = '06901234567'; $city = 'New Delhi'; $id = 1; |
5) Execute the query:
|
1 |
$query -> execute(); |
6) Check that the query has been performed and that the database has been successfully updated.
|
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 |
if($query -> rowCount() > 0) { $count = $query -> rowCount(); echo $count . " rows were affected."; } else { echo "No affected rows."; } All together now: $sql = "UPDATE users SET `city`= :city, `phone` = :tel WHERE `id` = :id"; $query = $dbh->prepare($sql); $query -> bindParam(':city', $city, PDO::PARAM_STR); $query -> bindParam(':tel' , $tel , PDO::PARAM_INT); $query -> bindParam(':id' , $id , PDO::PARAM_INT); $tel = '02012345678'; $city = 'London'; $id = 1; $query -> execute(); if($query -> rowCount() > 0) { $count = $query -> rowCount(); echo $count . " rows were affected."; } else { echo "No affected rows."; } |
