PHP Pdo updating record to MySQL database table

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, Class, Mark, Gender .

The connection object $dbo is taken from config.php file.
Sample Student table SQL dump

error_reporting(E_ERROR | E_PARSE | E_CORE_ERROR);
require "config.php"; // Database connection details. 
//////// End of Database connection /////////
//////////////////////////////////////
$id=2; // Fixed member ID for example
$count=$dbo->prepare("SELECT * FROM student 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 updated 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 for ID: $row->id</th></tr>
<tr class='r1'><td>Name</td><td><input type='text' name='name' value='$row->name'></td></tr>
<tr class='r1'><td>Class</td><td><input type='text' name='class' value='$row->class'></td></tr>
<tr class='r1'><td>Mark</td><td><input type='number' name='mark' value='$row->mark'></td></tr>
<tr class='r1'><td>Gender</td><td>
    <input type='radio' name='gender' value='Male' " . ($row->gender == 'Male' ? 'checked' : '') . "> Male
    <input type='radio' name='gender' value='Female' " . ($row->gender == 'Female' ? 'checked' : '') . "> Female
    <input type='radio' name='gender' value='Others' " . ($row->gender == 'Others' ? 'checked' : '') . "> Others
</td></tr>
<tr class='r1'><td></td><td><input type='submit' value='Submit'></td></tr>
</table></form>";

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.
Pre-populated form for updating student details

Pre-populated form for updating student details

This form will submit to pdo-update2.php file where we will collect the posted data and then prepare the query using PDO.
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 /////////

// Form validation and updating data
if ($_SERVER['REQUEST_METHOD'] == 'POST') {
    $id = $_POST['id'];
    $name = $_POST['name'];
    $class = $_POST['class'];
    $mark = $_POST['mark'];
    $gender = $_POST['gender'];

    // Validation
    if (empty($id) || empty($name) || empty($class) || empty($mark) || empty($gender)) {
        echo "<div class='alert alert-danger'>All fields are required!</div>";
    } elseif (!is_numeric($mark) || !is_numeric($id)) {
        echo "<div class='alert alert-danger'>ID and Mark must be numbers!</div>";
    } else {
        try {
            // Update query
            $query = "UPDATE student SET name = :name, class = :class, 
			  mark = :mark, gender = :gender WHERE id = :id";
            $stmt = $dbo->prepare($query);
            $stmt->bindParam(':id', $id, PDO::PARAM_INT);
            $stmt->bindParam(':name', $name);
            $stmt->bindParam(':class', $class);
            $stmt->bindParam(':mark', $mark);
            $stmt->bindParam(':gender', $gender);

            if ($stmt->execute()) {
                echo "<div class='alert alert-success'>
				Record updated successfully for Student ID: $id</div>";
            }
        } catch (PDOException $e) {
            echo "<div class='alert alert-danger'>Error: " . $e->getMessage() . "</div>";
        }
    }
}

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 student set mark=80 where userid='$_SESSION[userid]'");
$count->execute();

Using Transactions and Rollback for Secure Updates in PHP PDO

Transaction: Begins with beginTransaction().
Update Query: Updates a student's mark where id matches.
Commit: If successful, changes are committed with commit().
Rollback: On failure, the transaction is rolled back to prevent partial updates. More on PDO Transactions

try { 
    $dbo->beginTransaction();   // Start the transaction

    // Prepare the update query
    $stmt = $dbo->prepare("UPDATE student SET mark = :mark WHERE id = :id");
    
    // Bind parameters
    $stmt->bindParam(':mark', $mark);
    $stmt->bindParam(':id', $id);

    // Values for the update
    $mark = 85;
    $id = 3;

    // Execute the update
    $stmt->execute();
    
    // Commit the transaction if successful
    $dbo->commit();
    echo "Transaction committed, student record updated.";

} catch (PDOException $e) {
    // Rollback if any error occurs
    $dbo->rollBack();
    echo "Transaction failed: " . $e->getMessage();
}

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() Delete Record

Subscribe to our YouTube Channel here

PDO updating record to MySQL table

Quick Update

Using Transactions and Rollback for Secure Updates in PHP PDO

Number of records affected by update command

Subscribe