Comprehensive Guide to Updating Data in a MySQL Database using PHP

Updating data is one of the four core database operations (alongside inserting, selecting, and deleting rows). Any application that lets users edit a profile, mark a task done, or change a price relies on it. This chapter shows how to run an UPDATE query from PHP safely — with prepared statements — and how to confirm exactly how many rows changed.

This chapter assumes you already have a working database connection. The examples use mysqli; an equivalent PDO version is included at the end.

Understanding the PHP MySQL UPDATE Syntax

The UPDATE statement changes the values of existing rows. Its shape is the same whether you run it from the MySQL console or from PHP:

UPDATE table_name
SET column1 = value1, column2 = value2
WHERE some_column = some_value;

• table_name — the table whose rows you want to change.
• SET — the columns to modify and their new values. Columns you do not list are left untouched.
• WHERE — which rows to change. This clause is critical. If you omit it, every row in the table is updated.

The single most common — and most damaging — UPDATE mistake is forgetting the WHERE clause. UPDATE users SET active = 0 deactivates every user in the table, not just one. Always double-check the WHERE condition before running an update against real data.

Updating Data with a Prepared Statement

Never build an UPDATE query by concatenating user input directly into the SQL string — that opens you to SQL injection. Instead, use a prepared statement: write the query with ? placeholders, then bind the values separately so the database treats them strictly as data.

<?php
// Reuse your connection — in practice: require 'db_connect.php';
$conn = mysqli_connect("localhost", "username", "password", "database_name");

if (!$conn) {
    die("Connection failed: " . mysqli_connect_error());
}

$sql  = "UPDATE users SET email = ?, age = ? WHERE id = ?";
$stmt = mysqli_prepare($conn, $sql);

$email = "[email protected]";
$age   = 31;
$id    = 1;

// Type string: s = string, i = integer, d = double, b = blob.
// One letter per placeholder, in order.
mysqli_stmt_bind_param($stmt, "sii", $email, $age, $id);

if (mysqli_stmt_execute($stmt)) {
    echo "Record updated successfully";
} else {
    echo "Error updating record: " . mysqli_stmt_error($stmt);
}

mysqli_stmt_close($stmt);
mysqli_close($conn);
?>

How it works step by step:

1. mysqli_prepare() sends the query template (with ? placeholders) to MySQL.
2. mysqli_stmt_bind_param() binds your PHP variables to those placeholders. The first argument, "sii", declares the type of each value in order: a string, then two integers. The number and order of letters must match the placeholders exactly.
3. mysqli_stmt_execute() runs the query, returning true on success and false on failure.

Checking How Many Rows Changed

mysqli_stmt_execute() returning true only means the query ran without error — it does not mean a row actually changed. If the WHERE condition matched no rows (or the new values were identical to the old ones), zero rows are affected. Use mysqli_stmt_affected_rows() to find out:

<?php
$conn = mysqli_connect("localhost", "username", "password", "database_name");

$sql  = "UPDATE users SET age = ? WHERE id = ?";
$stmt = mysqli_prepare($conn, $sql);

$age = 40;
$id  = 1;
mysqli_stmt_bind_param($stmt, "ii", $age, $id);
mysqli_stmt_execute($stmt);

$rows = mysqli_stmt_affected_rows($stmt);
if ($rows > 0) {
    echo "Updated {$rows} row(s).";
} else {
    echo "No row matched, or the value was already up to date.";
}

mysqli_stmt_close($stmt);
mysqli_close($conn);
?>

This distinction matters in real applications: a "Save" button that reports success even when nothing was found can hide bugs from your users.

Updating Data with PDO

PDO is the more portable alternative to mysqli — the same code works across MySQL, PostgreSQL, SQLite, and others, and named placeholders make longer queries easier to read:

<?php
$pdo = new PDO(
    "mysql:host=localhost;dbname=database_name;charset=utf8mb4",
    "username",
    "password",
    [PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION]
);

$stmt = $pdo->prepare(
    "UPDATE users SET email = :email, age = :age WHERE id = :id"
);

$stmt->execute([
    ':email' => '[email protected]',
    ':age'   => 31,
    ':id'    => 1,
]);

echo "Updated " . $stmt->rowCount() . " row(s).";
?>

With PDO::ERRMODE_EXCEPTION set, a failed query throws an exception you can catch, rather than failing silently. rowCount() is the PDO equivalent of mysqli_stmt_affected_rows().

Best Practices

• Always include a WHERE clause unless you genuinely intend to update every row.
• Use prepared statements for any value that comes from user input — never string concatenation.
• Match the bind type string ("sii", etc.) to your variables' actual types to avoid silent conversions.
• Wrap multi-table updates in a transaction so they either all succeed or all roll back.
• Back up important data before running large or one-off updates against production.

Practice