PHP MySQL UPDATE Query

In this tutorial you'll learn how to update the records in a MySQL table using PHP.

Updating Database Table Data

The UPDATE statement is used to change or modify the existing records in a database table. This statement is typically used in conjugation with the WHERE clause to apply the changes to only those records that matches specific criteria.

The basic syntax of the UPDATE statement can be given with:

UPDATE table_name SET column1=value, column2=value2,... WHERE column_name=some_value

Let's make a SQL query using the UPDATE statement and WHERE clause, after that we will execute this query through passing it to the PHP mysqli_query() function to update the tables records. Consider the following persons table inside the demo database:

+----+------------+-----------+----------------------+
| id | first_name | last_name | email                |
+----+------------+-----------+----------------------+
|  1 | Peter      | Parker    | [email protected] |
|  2 | John       | Rambo     | [email protected]   |
|  3 | Clark      | Kent      | [email protected]   |
|  4 | John       | Carter    | [email protected]  |
|  5 | Harry      | Potter    | [email protected] |
+----+------------+-----------+----------------------+

The PHP code in the following example will update the email address of a person in the persons table whose id is equal to 1.

Example

Procedural Object Oriented PDO

Download

<?php
/* Attempt MySQL server connection. Assuming you are running MySQL
server with default setting (user 'root' with no password) */
$link = mysqli_connect("localhost", "root", "", "demo");
 
// Check connection
if($link === false){
    die("ERROR: Could not connect. " . mysqli_connect_error());
}
 
// Attempt update query execution
$sql = "UPDATE persons SET email='[email protected]' WHERE id=1";
if(mysqli_query($link, $sql)){
    echo "Records were updated successfully.";
} else {
    echo "ERROR: Could not able to execute $sql. " . mysqli_error($link);
}
 
// Close connection
mysqli_close($link);
?>

<?php
/* Attempt MySQL server connection. Assuming you are running MySQL
server with default setting (user 'root' with no password) */
$mysqli = new mysqli("localhost", "root", "", "demo");
 
// Check connection
if($mysqli === false){
    die("ERROR: Could not connect. " . $mysqli->connect_error);
}
 
// Attempt update query execution
$sql = "UPDATE persons SET email='[email protected]' WHERE id=1";
if($mysqli->query($sql) === true){
    echo "Records were updated successfully.";
} else{
    echo "ERROR: Could not able to execute $sql. " . $mysqli->error;
}
 
// Close connection
$mysqli->close();
?>

<?php
/* Attempt MySQL server connection. Assuming you are running MySQL
server with default setting (user 'root' with no password) */
try{
    $pdo = new PDO("mysql:host=localhost;dbname=demo", "root", "");
    // Set the PDO error mode to exception
    $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
} catch(PDOException $e){
    die("ERROR: Could not connect. " . $e->getMessage());
}
 
// Attempt update query execution
try{
    $sql = "UPDATE persons SET email='[email protected]' WHERE id=1";    
    $pdo->exec($sql);
    echo "Records were updated successfully.";
} catch(PDOException $e){
    die("ERROR: Could not able to execute $sql. " . $e->getMessage());
}
 
// Close connection
unset($pdo);
?>

After update the persons table will look something like this:

+----+------------+-----------+--------------------------+
| id | first_name | last_name | email                    |
+----+------------+-----------+--------------------------+
|  1 | Peter      | Parker    | [email protected] |
|  2 | John       | Rambo     | [email protected]       |
|  3 | Clark      | Kent      | [email protected]       |
|  4 | John       | Carter    | [email protected]      |
|  5 | Harry      | Potter    | [email protected]     |
+----+------------+-----------+--------------------------+

Warning: The WHERE clause in the UPDATE statement specifies which record or records should be updated. If you omit the WHERE clause, all records will be updated.

Previous Page Next Page

PHP MySQL UPDATE Query

Updating Database Table Data

Example

About Us

Contact

Interactive Tools