PDO updating record to MySQL tableWe will learn how to update a record using PDO. Updating records are required frequently in any script and some of the common examples are updating password, updating profile etc.
PDO has data validation capability so we will use that and update a record. Usually a record will have one unique identification number or string like member id or userid. We will use this identification to use will updating so the required record only is updated. We will use a WHERE clause to update the particular record only.
Let us display a form with few fields name, password . You can add more fields to this as per your requirement. Note that we have not used all password validation , display etc to make the script simple to understand. You can add those features as per your requirment.
This form will submit to pdo-update2.php file where we will collect the posted data and then prepare the query using PDO. We have first collected the existing data of the record by using PDO fetch object. Those data we populate as default data which user can keep like this or change.
WE will receive the data from POST method and then create the query to update the new data.
Above code will update the profile and if any validation or database problem is there then we will get the error message ( check the else condition section ).
Quick UpdateWhen we don't want to validate inputs and sure that they are sanitized or coming from reliable sources then we can use this short update script like this.
Number of records affected by update commandSome to we may be interested to know number of records updated by the query. Here after using $sql->execute() we can ask for rowCount to get the number of records updated.
For example we are expecting a single record to update then here is the code to show a confirmation message once rowCount returns 1 only.
Note that some time there may not be any change in old and new data so rowCount will return 0 only. In such case the query part remain correct but as there is no change in data so rowCount will return us 0 .
No need to use mysqli_real_escape_string() if you are using bindParam()