Update Data

Updating Data in MySQL with PHP PDO Prepared Statements

Updating existing database records in PHP uses a parameterized UPDATE ... SET ... WHERE ... query executed through PDO. Listing only changed columns explicitly, validating input first, and verifying ownership inside WHERE keeps your updates safe from injection and mass-assignment bugs.

Safe Prepared UPDATE with Allowed Columns

Never build SET clauses dynamically from raw $_POST keys—an attacker could submit role=admin or is_verified=1 in the request body. Bind only the exact columns that the endpoint permits updating.

<?php
declare(strict_types=1);

function updateProfileName(PDO $pdo, int $userId, string $fullName): bool
{
    $cleanName = trim($fullName);
    if ($cleanName === '') {
        throw new InvalidArgumentException('Name cannot be empty.');
    }

    $stmt = $pdo->prepare(
        'UPDATE users
         SET full_name = :full_name, updated_at = NOW()
         WHERE id = :id
         LIMIT 1'
    );
    $stmt->execute([
        ':full_name' => $cleanName,
        ':id' => $userId,
    ]);

    return $stmt->rowCount() > 0;
}

Understanding rowCount() on UPDATE Queries

By default in MySQL PDO, if you run UPDATE users SET full_name = 'Sadia' when full_name is already 'Sadia', MySQL reports 0 affected rows ($stmt->rowCount() === 0), even though the row matched! Do not assume rowCount() === 0 means "User not found" unless you set PDO::MYSQL_ATTR_FOUND_ROWS => true or verify existence separately.

Common Mistakes to Avoid

• Missing the WHERE clause on an UPDATE: Running UPDATE users SET status = 0 without WHERE updates every single user in your database.
• Dynamically interpolating $_POST keys into SET: Mass-assigning unknown request keys lets users overwrite balance, role, or password_hash.
• Reading-then-writing balances without atomic SQL or locking: Instead of reading $balance = 100 in PHP and writing SET balance = 100 - 20, use atomic SQL SET balance = balance - :amount WHERE id = :id AND balance >= :amount inside a transaction.

Frequently Asked Questions

Why did my UPDATE succeed without changing rowCount()?

MySQL returns the count of modified rows by default, so saving identical values returns 0 changed rows.

How do I prevent two concurrent requests from overwriting each other's edits?

Use optimistic locking by adding a version integer column (WHERE id = :id AND version = :expected_version) and checking if rowCount() === 1.

Should I update updated_at in SQL or PHP?

Either updated_at = NOW() in MySQL (when server timezone is UTC) or passing a UTC DateTimeImmutable timestamp from PHP works cleanly—just stay consistent across your app.