How to Update Data in MySQL?

How to Update Data in MySQL?

To change/modify the existing records in a database table, we have to use UPDATE statement.

Syntax:

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

Consider our table has the following records:

+—-+————+———–+————————–+
| id | first_name | last_name | email                    |
+—-+————+———–+————————–+
|  1 | Sabbir     | Rahaman   |sabbirrahaman@mail.com    |
|  2 | John       | Carter    |mahinchowdhury@mail.com   |
|  3 | Sadman     | Faijul    | sadmanfaijul@mail.com    |
|  4 | Mr.        | Zami      | zami009@mail.com         |
|  5 | John       | Potter    | johnpotter@mail.com      |
+—-+————+———–+————————–+

MySQLi Procedural

<?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);
?>

MySQLi Object Oriented

<?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();
?>

PDO 

<?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);
?>

In the above example PHP code will update the email address of a person in the persons table whose id equals to 1. After executing the code the persons table will look like this:

+—-+————+———–+————————–+
| id | first_name | last_name | email                    |
+—-+————+———–+————————–+
|  1 | Peter      | Parker    | peterparker_new@mail.com |

|  2 | John       | Carter    |mahinchowdhury@mail.com   |

|  3 | Sadman     | Faijul    | sadmanfaijul@mail.com    |
|  4 | Mr.        | Zami      | zami009@mail.com         |
|  5 | John       | Potter    | johnpotter@mail.com      |
+—-+————+———–+————————–+
Post a Comment