PHP : MySQL UPDATE Statement

The MySQL UPDATE statement changes the existing records of a table. You can change one or more columns and choose which rows to update with a condition.


Syntax

UPDATE table_name
SET column1 = value1, column2 = value2
WHERE condition;
  • You can update one or more columns in the same statement.
  • The WHERE clause chooses which rows to update.
  • You can update only one table at a time.
Note: Always use WHERE. If you forget it, MySQL updates every row of the table. For example UPDATE users SET mobile = "1" changes the mobile of all users.

Update a record using PHP

The steps are: connect to the database (see PHP : MySQL Connect), run the UPDATE query with mysqli_query(), and then close the connection. The connection code is saved in db-connection.php.

<?php
// db-connection.php
$database_server = 'localhost';
$database_username = 'root';
$database_password = '';
$database_name = 'student';

// Create connection using mysqli_connect()
$conn = mysqli_connect($database_server, $database_username, $database_password, $database_name);

// If $conn is false, connection is failed
if (!$conn) {
    die("Failed to connect to MySQL: " . mysqli_connect_error());
}
?>
<?php
require_once('db-connection.php');

// Change the name and mobile of the user with id 1
$sql = "UPDATE users SET name = 'Ram Kumar', mobile = '9000000000' WHERE id = 1";

if (mysqli_query($conn, $sql)) {
    echo "Record updated. Rows changed: " . mysqli_affected_rows($conn);
} else {
    echo "Error updating record: " . mysqli_error($conn);
}

mysqli_close($conn);
?>

Output:

Tutorials Class - Output Window

Record updated. Rows changed: 1

  • mysqli_query() returns true when the query runs successfully.
  • mysqli_affected_rows() gives the number of rows that were really changed. If the new value is same as the old value, it shows 0.
  • From PHP 8.1, MySQLi throws an exception on error, so the else part may not run. See PHP mysqli_query() for error handling.

Update with a prepared statement

When the new values or the id come from a form or URL, never put them directly in the query. Use a prepared statement, so SQL injection is not possible. Learn more in PHP MySQL Prepared Statements.

<?php
require_once('db-connection.php');

$mobile = "9111111111";
$id = 1;

$stmt = $conn->prepare("UPDATE users SET mobile = ? WHERE id = ?");
$stmt->bind_param("si", $mobile, $id);
$stmt->execute();

echo "Rows updated: " . $stmt->affected_rows;

$stmt->close();
$conn->close();
?>

Output:

Tutorials Class - Output Window

Rows updated: 1


Summary

  • UPDATE changes the existing records of a table.
  • Always add a WHERE clause.
  • Use prepared statements when the data comes from the user.
  • Next chapter: PHP : MySQL DELETE Statement.

Congratulations! Chapter Finished. Learn more about the similar topics:
Exercises & Assignments
Display MySQL data in an HTML table using mysqli_fetch_array()
Interview Questions & Answers
What is the difference between mysqli_fetch_array() and mysqli_fetch_object()?
How to Fix Incorrect format parameter error when importing large database in PHPMyAdmin?
In how many ways can we fetch data from a MySQL result set in PHP?
How to encrypt and decrypt data stored in a MySQL table?
What is the difference between mysqli and PDO in PHP?