PDO updating record to MySQL table

PHP PDO & MYsQL We 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.

PHP PDO update query using parameters with user inputs and counting number rows updated.


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 and easy to understand. You can add those features as per your requirement.
error_reporting(E_ERROR | E_PARSE | E_CORE_ERROR);
require "config.php"; // Database connection details. 
//////// End of Database connection /////////
//////////////////////////////////////
$id=2; // member id is fixed here 
$count=$dbo->prepare("select * from pdo_admin WHERE id=:id");
$count->bindParam(":id",$id,PDO::PARAM_INT,1);

if($count->execute()){
echo " Success <br>";
$row = $count->fetch(PDO::FETCH_OBJ);
}else{
print_r($dbo->errorInfo());
}
/// Display the form to collect fresh data /// 
echo "<form name='myForm' action='pdo-update2.php' method=post><input type=hidden name=id value='$id'>
<table class='t1'> <input type=hidden name=todo value='change-data'>
<tr><th colspan=2>Update Profile $row->userid</th></tr>
<tr class='r1'><td>Name</td><td><input type=text name='name' value='$row->name'></td></tr>
<tr class='r0'><td>Password</td><td><input type=text name='password' value='$row->password'></td></tr>
<tr class='r1'><td></td><td><input type=submit value='Submit'></td></tr>

</table></form>
";
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.
error_reporting(E_ERROR | E_PARSE | E_CORE_ERROR);
require "config.php"; // Database connection details. 
//////// End of Database connection /////////

/////////////Collect form data/////////////
$name=$_POST['name'];
$password=$_POST['password'];
$id=$_POST['id'];
///////// End of data collection /// 
error_reporting(E_ERROR | E_PARSE | E_CORE_ERROR);

$step=$dbo->prepare("update pdo_admin set name=:name,password=:password where id=:id");
$step->bindParam(':name',$name,PDO::PARAM_STR, 25);
$step->bindParam(':password',$password,PDO::PARAM_STR, 15);
$step->bindParam(':id',$id,PDO::PARAM_INT, 5);

if($step->execute()){
echo "Successfully updated Profile";
}// End of if profile is ok 
else{
print_r($step->errorInfo()); // if any error is there it will be posted
$msg=" Database problem, please contact site admin ";
}
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 Update

When 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.
$count=$dbo->prepare("update pdo_admin set status='F' where userid='$_SESSION[userid]'");
$count->execute();

Number of records affected by update command

Some to we may be interested to know number of records updated by the query. Here after using $step->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.
if($step->execute()){

if($step->rowCount()!=1){
$db_status='NOTOK';
$msg='Record Not updated';
}else{
$msg='One record updated';
$db_status='OK';
}

}
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()
PDO References rowcount()


Subscribe to our YouTube Channel here


Subscribe

* indicates required
Subscribe to plus2net

    plus2net.com







    Post your comments , suggestion , error , requirements etc here





    PHP video Tutorials
    We use cookies to improve your browsing experience. . Learn more
    HTML MySQL PHP JavaScript ASP Photoshop Articles FORUM . Contact us
    ©2000-2024 plus2net.com All rights reserved worldwide Privacy Policy Disclaimer