PHP SQL Server: Update

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.