PHP & MySQL - Update data in a database Tutorial

In this tutorial we learn how to update records in our database table with PHP and a SQL query.

Here's a table of contents of what you'll learn in this lesson:
(click on a link to skip to its section)

Let's jump right in.

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=value

If 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.