Update Data In a MySQL Table
The SQL UPDATE statement is used to update existing records in a table:
text
UPDATE table_name
SET column1 = value, column2 = value2,...
WHERE some_column = some_value
NOTE: The
WHEREclause specifies which record(s) that should be updated. If you omit theWHEREclause, all records will be updated!
To learn more about SQL, please visit the SQL tutorial.
Update Data With MySQLi
Look at the "MyGuests" table:
| id | firstname | lastname | reg_date | |
|---|---|---|---|---|
| 1 | John | Doe | [email protected] | 2024-10-22 14:26:15 |
| 2 | Mary | Moe | [email protected] | 2024-10-23 10:22:30 |
The following examples update the record with id=2 in the "MyGuests" table:
Example - MySQLi Object-oriented
html
<?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
html
<?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
html
<?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 | [email protected] | 2024-10-22 14:26:15 |
| 2 | Mary | Doe | [email protected] | 2024-10-23 10:22:30 |