How to update database records in PHP
When we want to update an existing record in database then we are using update statement.
We are using PDO or PHP Data Object to update an existing record.
Let's explain with an example.
First of all we need these four piece of information to establish connection with the MySQL database such as server name, database name, database user name, and database password.
$servername = "localhost";
$username = "username";
$password = "password";
$dbname = "db";
After declaring these values we can write code. The entire code of database connection to update statement comes within try and catch block.
try {
// Database connection and Update statement goes here.
} catch(PDOException $e) {
echo $e-gt;getMessage();
}
Now, we are connecting with the MySQL database. Also, we set PDO error mode to exception.
$conn = new PDO("mysql:host=$servername;dbname=$dbname", $username, $password);
$conn-gt;setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
Now, we are writing the update SQL statement. Here we are updating a record of table users by setting the lastname of a user whose user id is 2. Please notice that string values are quoted, but numeric values are not quoted. Also, the keywords update, set, and where are written in capital letters, though it is optional, but recommended to write like this.
$sql = "UPDATE users SET lastname = 'Doe' WHERE id = 2";
Now, we write the prepared statement with the SQL query.
$stmt = $conn-gt;prepare($sql);
Finally, we execute the update statement and make it applicable so that the record is updated permanently.
$stmt-gt;execute();
Now, we come out of try and catch block and close the connection.
$conn = null;
If we have a look into the MySQL table users now, we can see the record 2 is updated with lastname permanently.