PHP MySQL Update

Previous Page Next Page

Updating Database Table Data

The UPDATE statement is used to modify existing records in a database table. It is often used with the WHERE clause to specify which records should be updated based on certain conditions.

The basic syntax of the UPDATE statement is as follows:

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

Let's create a SQL query using the UPDATE statement and the WHERE clause. After that, we will execute this query by passing it to the PHP mysqli_query() function to update the records in the table. Consider the persons table within the demo database:

+----+------------+-----------+----------------------+
| id | first_name | last_name | email                |
+----+------------+-----------+----------------------+
|  1 | Peter      | Parker    | peterparker@mail.com |
|  2 | John       | Rambo     | johnrambo@mail.com   |
|  3 | Clark      | Kent      | clarkkent@mail.com   |
|  4 | John       | Carter    | johncarter@mail.com  |
|  5 | Harry      | Potter    | harrypotter@mail.com |
+----+------------+-----------+----------------------+

The following PHP example updates the email address of a person in the persons table where the 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='peterparker_new@mail.com' 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='peterparker_new@mail.com' 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='peterparker_new@mail.com' 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 updating, the persons table will appear like this:

+----+------------+-----------+--------------------------+
| id | first_name | last_name | email                    |
+----+------------+-----------+--------------------------+
|  1 | Peter      | Parker    | peterparker_new@mail.com |
|  2 | John       | Rambo     | johnrambo@mail.com       |
|  3 | Clark      | Kent      | clarkkent@mail.com       |
|  4 | John       | Carter    | johncarter@mail.com      |
|  5 | Harry      | Potter    | harrypotter@mail.com     |
+----+------------+-----------+--------------------------+

Caution: The WHERE clause in the UPDATE statement determines which record or records are updated. If omitted, all records in the table will be updated.

Previous Page Next Page