In this guide, you will learn how to use the Update statement to perform modifications to a MySQL table.
The Basics
The update statement is straightforward and provides a simple and easy to use syntax, as shown below:
The statement takes the table on which to perform the modifications. Next, specify the columns and the values to update. You can also include the WHERE clause only to apply the changes in the columns that match a specified condition.
If you do not specify the WHERE clause, the changes will be applied in all the columns specified.
How to Use the Update Statement
Let us illustrate how to use the Update statement on a Database using PHP. We will do this by first defining an SQL query using the UPDATE statement. Finally, we will run the query using the mysqli_query() function.
Suppose we have a table containing simple information as shown below:
If we want to update the email of one of the customers in the database, we can use an example PHP code as shown below:
$SERVERNAME = "localhost";
$USERNAME = "root";
$PASS = "mysql";
$DB = "sakila";
$conn = mysqli_connect($SERVERNAME, $USERNAME, $PASS, $DB);
if ($conn->connect_error) {
die("Connection to server failed: " . $conn->connect_error);
}
$query = "UPDATE customer SET email='[email protected]' WHERE customer_id=1";
if ($conn->query($query) == TRUE) {
echo "Specified Records updated...[OK]", "\n";
}
else {
echo "Record Update Fail...[Error]", "\n";
}
$conn->close();
?>
The above code starts by creating a connection to the MySQL database using the specified credentials.
We then specify an UPDATE query to change the email of the customer with an id of 1.
Once we run the query, we should see an output indicating the records have been updated successfully.
Specified Records updated...[OK]
To verify the changes have been made to the database, you can query the database as shown:
As you can see from the result, the value of the email column where the id=1 was changed.
Conclusion
This guide taught you how to update records in a database’s table using PHP and the update statement. Stay tuned for more tutorials.