PHP MySQL Update Data
Update Data In a MySQL Table
The SQL UPDATE statement is used to update existing records in a table:
UPDATE table_name
SET column1 = value, column2 = value2,...
WHERE some_column = some_value
NOTE: The WHERE clause
specifies which record(s) that should be updated. If you omit the WHERE
clause, all records will be updated!
To learn more about SQL, please visit our SQL tutorial.
Update Data With MySQLi
Look at the "MyGuests" table:
| id | firstname | lastname | reg_date | |
|---|---|---|---|---|
| 1 | John | Doe | john@example.com | 2024-10-22 14:26:15 |
| 2 | Mary | Moe | mary@example.com | 2024-10-23 10:22:30 |
The following examples update the record with id=2 in the "MyGuests" table:
Example - MySQLi Object-oriented
<?php
$servername = "localhost";
$username = "username";
$password = "password";
$dbname = "myDB";
// Create connection
$conn = new mysqli($servername, $username, $password, $dbname);
// Check connection
if ($conn->connect_error) {
die("Connection failed: " . $conn->connect_error);
}
// SQL to update a record
$sql = "UPDATE MyGuests SET lastname='Doe' WHERE id=2";
if ($conn->query($sql) === TRUE) {
echo "Record updated successfully";
} else {
echo "Error updating record: " . $conn->error;
}
$conn->close();
?>
Example - MySQLi Procedural
<?php
$servername = "localhost";
$username = "username";
$password = "password";
$dbname = "myDB";
// Create connection
$conn = mysqli_connect($servername, $username, $password, $dbname);
// Check connection
if (!$conn) {
die("Connection failed: " . mysqli_connect_error());
}
// SQL to update a record
$sql = "UPDATE MyGuests SET lastname='Doe' WHERE id=2";
if (mysqli_query($conn, $sql)) {
echo "Record updated successfully";
} else {
echo "Error updating record: " . mysqli_error($conn);
}
mysqli_close($conn);
?>
Update Data With PDO
The following examples update the record with id=2 in the "MyGuests" table:
Example - PDO
<?php
$servername = "localhost";
$username = "username";
$password = "password";
$dbname = "myDB";
try {
$conn = new PDO("mysql:host=$servername;dbname=$dbname", $username, $password);
// set the PDO error mode to exception
$conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
} catch(PDOException $e){
die("Could not connect. " .
$e->getMessage());
}
try {
// SQL to update a record
$sql = "UPDATE MyGuests SET lastname='Doe' WHERE id=2";
// execute the query
$conn->execute();
echo "Record updated successfully";
} catch(PDOException $e)
{
echo "Error updating record: " .$sql . "<br>" . $e->getMessage();
}
$conn = null;
?>
After the record is updated, the table will look like this:
| id | firstname | lastname | reg_date | |
|---|---|---|---|---|
| 1 | John | Doe | john@example.com | 2024-10-22 14:26:15 |
| 2 | Mary | Doe | mary@example.com | 2024-10-23 10:22:30 |
