Summary: in this tutorial, you will learn how to update data from a table using PHP PDO.
This tutorial begins where the pagination tutorial left off.
We’ll update a row from the Authors table specified by the author id.
Updating data
Step 1. Define the update()
method in the AuthorDB
class, which updates an author:
public function update(Author $author)
{
$sql = 'UPDATE Authors
SET FirstName = :firstName, LastName = :lastName, BirthDate = :birthDate
WHERE AuthorID = :id';
$stmt = $this->conn->prepare($sql);
$stmt->execute([
'firstName' => $author->getFirstName(),
'lastName' => $author->getLastName(),
'birthDate' => $author->getBirthDate(),
'id' => $author->getId()
]);
return $stmt->rowCount() > 0;
}
Code language: PHP (php)
How it works.
First, define a public method update()
that takes an Author
object as the parameter:
public function update(Author $author)
Code language: PHP (php)
Second, construct an UPDATE statement that updates the first name, last name, and birth date of an author specified by an id:
$sql = 'UPDATE Authors
SET FirstName = :firstName, LastName = :lastName, BirthDate = :birthDate
WHERE AuthorID = :id';
Code language: PHP (php)
Third, create a prepared statement for executing the UPDATE statement:
$stmt = $this->conn->prepare($sql);
Code language: PHP (php)
Fourth, execute the prepared statement with values that come from the Author
object:
$stmt->execute([
'firstName' => $author->getFirstName(),
'lastName' => $author->getLastName(),
'birthDate' => $author->getBirthDate(),
'id' => $author->getId()
]);
Code language: PHP (php)
Finally, return true if the number of updated rows is greater than 0:
return $stmt->rowCount() > 0;
Code language: PHP (php)
Step 2. Modify the index.php
file to use the update()
method:
<?php
require_once 'authordb.php';
// Connect to the SQL Server
$conn = require_once 'connect.php';
$authorDB = new AuthorDB($conn);
// Find author by id 1
$author = $authorDB->findById(1);
// change the birth date
$author->setBirthDate('1980-06-30');
// Update the author
$authorDB->update($author);
Code language: PHP (php)
How it works.
First, load the authordb.php
file to use the AuthorDB
class.
require_once 'authordb.php';
Code language: PHP (php)
Second, connect to the SQL Server and assign the returned PDO object to the $conn
variable:
$conn = require_once 'connect.php';
Code language: PHP (php)
Third, create a new AuthorDB
object:
$authorDB = new AuthorDB($conn);
Code language: PHP (php)
Fourth, find the Author
object with id 1:
$author = $authorDB->findById(1);
Code language: PHP (php)
Fifth, update the birth date to 1980-06-30
, assuming that the author with id 1 exists in the Authors
table:
$author->setBirthDate('1980-06-30');
Code language: PHP (php)
Finally, call the update()
method of the AuthorDB
object to update the author:
$authorDB->update($author);
Code language: PHP (php)
Step 3. Verify the update.
Open SQL Server Management Studio (SSMS), connect to SQL Server, and execute the following query:
SELECT * FROM authors
WHERE AuthorID = 1;
Code language: SQL (Structured Query Language) (sql)
Output:
AuthorID | FirstName | LastName | BirthDate
---------+-----------+----------+------------
1 | Anthony | Pham | 1980-06-30
(1 row)
Code language: plaintext (plaintext)
The output indicates that the birth date of the author ID 1 is updated to 1980-06-30
.
Summary
- Execute the
UPDATE
statement to update data in a table.