Update Data in PHP MySQL – Complete Guide with Examples

Introduction

PHP allows developers to interact with MySQL databases and modify stored data using SQL queries. To update records in a database, developers use the UPDATE SQL statement along with PHP database functions such as mysqli_query().

In this tutorial, you will learn how to update records in a MySQL database using PHP, including syntax, examples, real-life use cases, common mistakes, and interview questions.

What is Update Data in PHP MySQL?

Updating data in PHP MySQL refers to the process of modifying existing records in a database table using SQL queries executed through PHP.

The SQL command used for updating data is the UPDATE statement.

The UPDATE statement allows developers to change the value of one or more columns for specific records in a table.

For example, if a user changes their email address, the application must update the corresponding record in the database.

Example SQL query:


UPDATE users SET email='newemail@example.com' WHERE id=1;

In this query:

  • users is the table name
  • email is the column being updated
  • newemail@example.com is the new value
  • WHERE id=1 specifies which record should be updated

PHP executes this query using functions like mysqli_query() after connecting to the database.

Why It Is Used

Updating data is required in many types of web applications.

1. Updating User Profiles

Users may update their name, email, password, or other personal details.

2. Editing Blog Posts

Blog authors often update article content or titles.

3. Updating Product Information

E-commerce websites frequently update product prices, descriptions, or stock availability.

4. Correcting Data Errors

Sometimes incorrect information needs to be corrected in the database.

5. Changing Application Settings

Admins may update configuration values stored in the database.

Without update operations, websites would not be able to modify stored data dynamically.

Syntax

The basic syntax of the SQL UPDATE statement is:


UPDATE table_name
SET column1=value1, column2=value2

WHERE condition;

Explanation:

  • table_name – the name of the table
  • SET – specifies the columns to update
  • WHERE – defines which records should be modified

⚠ If the WHERE clause is omitted, all records in the table will be updated, which can cause serious problems.

In PHP, the query is executed using:


$sql = "UPDATE users SET name='John' WHERE id=1";
mysqli_query($conn, $sql);

Example

Below is a simple example that updates a user’s email in the database.


$servername = "localhost";
$username = "root";
$password = "";
$database = "testdb";
$conn = mysqli_connect($servername, $username, $password, $database);
if (!$conn) {
   die("Connection failed: " . mysqli_connect_error());
}
$sql = "UPDATE users SET email='johnnew@email.com' WHERE id=1";
if (mysqli_query($conn, $sql)) {
   echo "Record updated successfully";
} else {
   echo "Error updating record: " . mysqli_error($conn);
}

Explanation:

  1. The script connects to the MySQL database.
  2. The UPDATE SQL query modifies the email column.
  3. The WHERE clause ensures only the specific record is updated.
  4. mysqli_query() executes the query.
  5. A success or error message is displayed.

Real-Life Example

Consider a user profile system on a website.

When users log into their accounts, they may want to update their personal information such as:

  • Name
  • Email
  • Phone number
  • Address

The process works like this:

  1. The user submits an update form.
  2. The form sends the updated data to a PHP script.
  3. PHP executes an UPDATE query.
  4. The database modifies the existing record.

Example scenario:

A user updates their email address.

Old email in database:


user@email.com

New email entered by the user:


newuser@email.com

The PHP script runs an UPDATE query to replace the old value with the new one.

This type of functionality is used in almost every modern website.

Common Mistakes

Beginners often make several mistakes when updating database records.

1. Forgetting the WHERE Clause

Without a WHERE clause, the query updates all rows in the table.

Example of a dangerous query:


UPDATE users SET email='test@email.com';

This would update every user’s email.

2. Using Incorrect Column Names

If the column name is incorrect, the query will fail.

3. Not Checking Query Results

Developers should always check whether the query was successful.

4. Not Validating User Input

User input should be validated before updating the database.

5. Ignoring SQL Injection Risks

Directly inserting user input into SQL queries can make the application vulnerable to SQL injection attacks.

Conclusion

Updating data in PHP MySQL is an important database operation that allows developers to modify existing records in a table. By using the SQL UPDATE statement along with PHP functions like mysqli_query(), developers can update specific fields based on defined conditions.

Understanding how to update data correctly helps developers build reliable and dynamic web applications.

Related PHP Tutorials