PHP MySQL Update Data
The UPDATE statement is used to modify existing records in a table. It is one of the most common database operations, used for everything from changing a user's password to updating a product's price.
1. The UPDATE Syntax
UPDATE table_name
SET column1 = value1, column2 = value2, ...
WHERE condition;
CRITICAL WARNING: Notice the WHERE clause in the syntax. If you omit the WHERE clause, ALL records in the table will be updated!
2. Update Data using MySQLi (Object-Oriented)
In this example, we update the record in the "MyGuests" table where the id is 2.
<?php
$servername = "localhost";
$username = "root";
$password = "";
$dbname = "myDB";
// Create connection
$conn = new mysqli($servername, $username, $password, $dbname);
// Check connection
if ($conn->connect_error) {
die("Connection failed: " . $conn->connect_error);
}
$sql = "UPDATE MyGuests SET lastname='Doe' WHERE id=2";
if ($conn->query($sql) === TRUE) {
echo "Record updated successfully";
} else {
echo "Error updating record: " . $conn->error;
}
$conn->close();
?>
3. Update Data using PDO
PDO's rowCount() method is very useful here to tell you exactly how many rows were affected by your update.
<?php
try {
$conn = new PDO("mysql:host=$servername;dbname=$dbname", $username, $password);
$conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$sql = "UPDATE MyGuests SET lastname='Doe' WHERE id=2";
// Prepare statement
$stmt = $conn->prepare($sql);
// execute the query
$stmt->execute();
// echo a message to say the UPDATE succeeded
echo $stmt->rowCount() . " records UPDATED successfully";
} catch(PDOException $e) {
echo $sql . "<br>" . $e->getMessage();
}
$conn = null;
?>
4. Best Practices for Updating
- Always use a WHERE clause: Usually, you want to target a specific record using its
id (Primary Key).
- Verify before Update: Run a
SELECT query with the same WHERE clause first to ensure you are targeting the correct rows.
- Use Prepared Statements: Especially when the data being updated comes from a user form.
Tip: If you use $stmt->rowCount() in PDO or $conn->affected_rows in MySQLi and it returns 0, it means either no record matched your WHERE clause OR the record already had the value you were trying to set.