PHP MySQL Update Data

In this tutorial, we will learn how to delete the data from the MySQL table from the PHP program. It is as simple as inserting data into the table and can be done in a few lines of code. We will see all three methods i.e., update using MySQLi procedural, MySQLi object-oriented and PDO. So, let’s get started!

Update MySQL Table Data using PHP

If you have basic knowledge of MySQL, you might know that we use the UPDATE statement to update the data of the MySQL table. If you don’t know how to update the data using the MySQL command, take a look below:

For updating single column and all row values-

UPDATE table_name
SET col_name= new_value;Code language: SQL (Structured Query Language) (sql)

The above query will set the new_value to all the rows of that particular specified column. If you want to update the value of a single column and only a specific row/rows then the following query is used:

UPDATE table_name
SET col_name= new_value
WHERE condition;Code language: SQL (Structured Query Language) (sql)

In the above query, we use the WHERE clause to target the particular row or group of rows. The WHERE condition maybe something like – WHERE id=1, WHERE name=”some name”, WHERE id > 10 AND id < 20 or anything depending on your use cases.

To update multiple column values, the following query is used-

UPDATE table_name
SET col_name1= new_value1, col_name2=new_value2,....
WHERE condition;Code language: SQL (Structured Query Language) (sql)

PHP MySQL Update Data Examples

Before proceeding to the examples, let’s have a look at the table data that we will apply update queries. The following screenshots show the table description and the table data.

Update Data Using MySQLi (Procedural)

<!-- update mysql table data -->
<?php
$servername = "localhost";
$username = "root";
$password = "";
$dbname = "journaldev";
$conn = mysqli_connect($servername, $username, $password, $dbname);
if (!$conn) {
    die("Connection failed: " . mysqli_connect_error());
}
$sql = "UPDATE emp SET city='Dubai' WHERE id=4";
if (mysqli_query($conn, $sql)) {
    echo "Record updated successfully";
} else {
    echo "Error occured: " . mysqli_error($conn);
}
mysqli_close($conn);
?>Code language: PHP (php)

Here, we have first assigned the UPDATE query to the variable and executed it using the mysqli_query() method. Here, we will update the city name of the employee having id 4 to the “Dubai”.

Let’s check the table to see if the record is updated.

SELECT * FROM emp;Code language: SQL (Structured Query Language) (sql)

Update Data Using MySQLi (Object-Oriented)

<?php
$serverName = "localhost";
$userName = "root";
$password = "";
$dbName = "journaldev";
$conn = new mysqli($serverName, $userName, $password, $dbName);
if ($conn->connect_error) {
    die("Connection failed: " . $conn->connect_error);
}
$sql = "UPDATE emp SET city='New Delhi' WHERE id=1";
if ($conn->query($sql) === TRUE) {
    echo "Record updated successfully";
} else {
    echo "Error occured: " . $conn->error;
}
$conn->close();
?>Code language: PHP (php)

Here, we will update the city name of the employee having id 1 to the “new Delhi”.

Let’s check the table to see if the record is updated.

SELECT * FROM emp;Code language: SQL (Structured Query Language) (sql)

Update Data Using PDO

<?php
$servername = "localhost";
$username = "root";
$password = "";
$dbname = "journaldev";
try {
    $conn = new PDO("mysql:host=$servername;dbname=$dbname", $username, $password);
    $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    $sql = "UPDATE emp SET name='Jane' WHERE id=3";
    $conn->exec($sql);
    echo "Record updated successfully";
} catch (PDOException $e) {
    echo $sql . "<br>" . $e->getMessage();
}
$conn = null;
?>Code language: PHP (php)

In the above example, we will update the name of the employee having id 3 to the “Jane”. Here, you can use the prepared statement to prepare the query. However, we have directly executed the UPDATE query without using PREPARE statement.

Let’s check the table to see if the record is updated.

SELECT * FROM emp;Code language: SQL (Structured Query Language) (sql)

Conclusion

In this tutorial, we learned to update the records in the MySQL database table. You might have gotten the idea about updating the data which is a very easy task using the PHP program. You can try complex conditions as well in your programs to enhance your command on PHP-MySQL. For an in-detail guide on the MySQL UPDATE statement, read this tutorial.