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
WHEREclause 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() |