PHP & MySQL - Update data in a database Tutorial
In this tutorial we learn how to update records in our database table with PHP and an SQL query.
For this tutorial, please start the MySQL module in your XAMPP control panel.
How to update data in a table
We can update data in our table with the UPDATE SET query. Typically, the UPDATE SET query includes the WHERE clause to update only select records.
As an example, let’s consider a table with user emails. A user may want to update their email address to a different one.
Syntax:
UPDATE table
SET column1=value, column2=value2, ...
WHERE column=valueIf a value to be updated is a string type, the value must be wrapped in quotes.
Example: update data
<?php
$serverName = "localhost";
$userName = "root";
$password = "";
$dbName = "trainingDB";
$conn = mysqli_connect($serverName, $userName, $password, $dbName);
if (!$conn) { die("Connection failed: " . mysqli_connect_error()); }
// update record
$sql = "UPDATE Users SET email='john@newmail.com' WHERE userID=1";
mysqli_query($conn, $sql);
// show data
$sql = "SELECT userID, name, email FROM Users WHERE userID=1";
$result = mysqli_query($conn, $sql);
while($row = mysqli_fetch_assoc($result)){
echo "User ID: " . $row['userID'] . "<br>" .
"User Name: " . $row['name'] . "<br>" .
"User Email: " . $row['email'] . "<br><br>";
}
mysqli_close($conn);
?>
In the example above we update John Doe’s (userID=1) email address.
If we didn’t include the WHERE clause, all the users in the database would have their emails updated.
Summary: Points to remember
- We update entries in our table with the UPDATE SET WHERE query.