PHP PDO UPDATE - Updating Data

Updating Data (UPDATE)

We currently have 10 active users in our table (ID 6 is missing because we deleted it). Our goal is to update the last name of the user with ID 11. The UPDATE command is used to modify existing data in a table. When using PDO, it is critical to use prepared statements, preventing unauthorized parties from modifying the wrong data.

EXAMPLE

<?php
$server = "localhost";
$username = "root";
$password = "";
$database = "pdo_database";

try {
    $connection = new PDO("mysql:host=$server;dbname=$database;charset=utf8mb4", $username, $password);
    $connection->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

    // STEP 2: Preparing data for UPDATE
    $new_last_name = "Green";
    $user_id = 11;

    // STEP 3: Preparing and executing the UPDATE statement
    $sql = "UPDATE users SET last_name = :last_name WHERE id = :id";
    $statement = $connection->prepare($sql);
    $statement->execute(['last_name' => $new_last_name, 'id' => $user_id]);

    echo "<div style='font-family: monospace; color: #2e7d32; margin-bottom: 12px;'>User data for ID 11 was successfully updated!</div>";

    // SIMULATOR TRICK: Manually insert ID 11 so it appears in the table below
    $connection->exec("INSERT INTO users (id, first_name, last_name, email) VALUES (11, 'Johnny', 'Green', 'johnny@example.com')");

    // STEP 4: Displaying the refreshed table
    $query = $connection->query("SELECT id, first_name, last_name, email FROM users");
    $results = $query->fetchAll(PDO::FETCH_ASSOC);

    echo "<table border='1' style='border-collapse: collapse; width: 100%; font-family: monospace;'>";
    echo "<tr><th>ID</th><th>First Name</th><th>Last Name</th><th>Email</th></tr>";

    foreach ($results as $row) {
        echo "<tr>";
        echo "<td>" . $row['id'] . "</td>";
        echo "<td>" . $row['first_name'] . "</td>";
        echo "<td>" . $row['last_name'] . "</td>";
        echo "<td>" . $row['email'] . "</td>";
        echo "</tr>";
    }
    echo "</table>";

} catch (PDOException $e) {
    echo "Error: " . $e->getMessage();
}

$connection = null;
?>

RESULT

The result will be displayed here...

In the example above, we used the named parameters :last_name and :id. The execute() method will securely insert these values into the SQL command and update the selected row.

Always specify a WHERE condition, otherwise you will update every row in the table!

Prepared statements ensure that values are treated as data, not as part of the command.

The method returns the number of rows that were actually modified.