PHP Update Data in MySQL
Update Data In a MySQL Table Using MySQLi and PDOThe UPDATE statement is used to update existing records in a table:
UPDATE table_name
SET column1=value, column2=value2,...
WHERE some_column=some_value Notice the WHERE clause in the UPDATE syntax: The WHERE clause specifies which record or records 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.
Let's look at the "MyGuests" table:
| id | firstname | lastname | email | reg_date | | 1 | John | Doe | john@example.com | 2014-10-22 14:26:15 | | 2 | Mary | Moe | mary@example.com | 2014-10-23 10:22:30 |
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 = "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 = "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);
?> Example (PDO)<?php
$servername = "localhost";
$username = "username";
$password = "password";
$dbname = "myDBPDO";
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);
$sql = "UPDATE MyGuests SET lastname='Doe' WHERE id=2";
// Prepare statement $stmt = $conn->prepare($sql);
// execute the query $stmt->execute();
// echo a message to say the UPDATE succeeded echo $stmt->rowCount() . " records UPDATED successfully";
}
catch(PDOException $e)
{
echo $sql . "<br>" . $e->getMessage();
}
$conn = null;
?>
| id | firstname | lastname | email | reg_date | | 1 | John | Doe | john@example.com | 2014-10-22 14:26:15 | | 2 | Mary | Doe | mary@example.com | 2014-10-23 10:22:30 |
| 
