PHP MySQL Update Data

In this tutorial, we will learn how to update existing records in a MySQL database using PHP. Updating existing records in a database is one of the most common tasks when you build a dynamic web application.

For example, if a user wants to change their email address or password, the application updates the existing record instead of inserting a new one.

In PHP, you can easily update records in a MySQL table using the UPDATE statement. So, let’s understand what the UPDATE statement is and how to use it with PHP and MySQL using practical examples.

What is UPDATE Statement in MySQL?

The UPDATE statement in MySQL modifies or updates the existing records in a database table. It allows you to change the values of one or more columns for specific rows. You can use the UPDATE statement when you want to:

• Modify user information
• Change password
• Update profile details
• Change order status and many more.

Usually, we use the UPDATE statement with the WHERE clause to specify which records should be updated in the table. The WHERE clause helps identify and target specific rows that meet the specified condition.

If you do not use the WHERE clause, the UPDATE statement will update all rows in the table. However, the structure of the table (such as columns and data types) remains unchanged.

Basic Syntax of UPDATE Statement in MySQL

The basic syntax to update a specific record in a table using the MySQL UPDATE statement is:

UPDATE table_name
SET column1 = value1, column2 = value2, . . .
WHERE some_column = some_value;

In the above syntax:

• UPDATE is a command used to update one or more records in a table.
• table_name specifies the name of the table whose records will be updated.
• SET specifies the column or columns whose values will be updated.
• WHERE specifies the condition that determines which record or records should be updated.

[blocksy-content-block id=”12371″]

Database Table Example

Suppose we have a table called students.

Now we want to update the email of the student with id = 2. For this, we will write the following MySQL query:

UPDATE students
SET email = 'smith123@gmail.com'
WHERE id = 2;

Ways to Update Data in PHP MySQL

In PHP, there are three main ways to update data in a MySQL table:

• MySQLi Procedural Method
• MySQLi Object-Oriented Method
• PDO Method (Recommended)

Let us understand each one by one with examples.

Requirements Before Updating Data from a Database Table

Before updating records in a MySQL table using PHP, make sure the following requirements are met:

• You must have a MySQL database created.
• You must establish a successful connection between PHP and the MySQL database.
• The table must exist in the selected database.
• The table should contain records that you want to update.
• The database user must have the required permissions to access and modify the table.

Make sure you meet all these basic requirements before attempting to update records in a MySQL table using PHP.

Update Data Using MySQLi Procedural Method

Inside the htdocs folder, create a new file with the name update_record.php and paste this code into it.
[blocksy-content-block id=”12121″]

Example Code:

<?php
// Include the database connection file.
require_once "connect.php";

// Write an SQL query to update a record in a database table.
$sql = "UPDATE students SET email='smith123@gmail.com' WHERE id=2";

if (mysqli_query($conn, $sql)) {
     echo "Record updated successfully";
} else {
     echo "Error updating record: " . mysqli_error($conn);
}
// Close the connection.
mysqli_close($conn);
?>

Now save this file using Ctrl + S.

Now open the browser and go to http://localhost/update_record.php. If the records exist in the table, you will see the following output in your browser.

Output:

Record updated successfully

Explanation of PHP Code

• The line require_once “connect.php”; loads the database connection file. This file connects PHP to the MySQL database. Without this connection, PHP cannot access or modify data in the database.
• Next, we write an SQL UPDATE statement and store it in a variable named $sql.
• The SQL UPDATE statement modifies the column value of a specific record in the students table.
• The condition WHERE id=2 ensures that only the record with id 2 is updated in the table.
• The mysqli_query() function executes the SQL query. This function takes two parameters:
  • $conn → database connection variable.
  • $sql → SQL query
• The if condition checks whether the query was executed successfully.
• If the query runs successfully, a success message is displayed in the browser.
• If the query fails, the code inside the else block will execute and display the error message returned by MySQL.
• Finally, the function mysqli_close($conn); closes the database connection to free up server resources.

Check Updated Data in Table

Now open http://localhost/phpmyadmin in the browser and check whether the table has been updated with the above data.

Update Data Using MySQLi Object-Oriented Method

Another way to update data in the MySQL table is using the object-oriented approach.

Example Code:

<?php
// Include the database connection file.
require_once "connect.php";
// SQL query to delete a record from the students table.
$sql = "UPDATE students SET age= 18 WHERE id=3";

if ($conn->query($sql) === TRUE) {
     echo "Record updated successfully";
} else {
     echo "Error updating record: " . $conn->error;
}
$conn->close();
?>

Update Data Using PDO Method

PDO (PHP Data Objects) is the most secure and flexible method for database operations, such as inserting, deleting, updating, etc. Inside the htdocs folder, create a new file with the name update_record_pdo.php and paste this code into it.

Example Code:

<?php
// Import database connection
require_once "config.php";

try {
 // SQL query to delete a record from the students table.
    $sql = "UPDATE students SET email='mark12@gmail.com' WHERE id=4";
 // Execute the MySQL query.
    $conn->execute();
    echo "Record updated successfully";
} catch(PDOException $e) {
    echo "Error updating record: " .$sql . "<br>" . $e->getMessage();
}
// Close database connection.
$conn = null;
?>