{"id":4416,"date":"2024-07-31T08:38:08","date_gmt":"2024-07-31T01:38:08","guid":{"rendered":"https:\/\/www.sqlservertutorial.net\/?page_id=4416"},"modified":"2024-08-18T10:13:35","modified_gmt":"2024-08-18T03:13:35","slug":"php-sql-server-update","status":"publish","type":"page","link":"https:\/\/www.sqlservertutorial.net\/php-sql-server\/php-sql-server-update\/","title":{"rendered":"PHP SQL Server: Update"},"content":{"rendered":"\n

Summary<\/strong>: in this tutorial, you will learn how to update data from a table using PHP PDO.<\/p>\n\n\n\n

This tutorial begins where\u00a0the\u00a0pagination tutorial<\/a> left off.<\/p>\n\n\n\n

We’ll update a row from the Authors table specified by the author id.<\/p>\n\n\n\n

Updating data #<\/a><\/h2>\n\n\n\n

Step 1. Define the update()<\/code> method in the AuthorDB<\/code> class, which updates an author:<\/p>\n\n\n

public<\/span> function<\/span> update<\/span>(Author $author)<\/span>\n<\/span>{\n    $sql = 'UPDATE Authors \n            SET FirstName = :firstName, LastName = :lastName, BirthDate = :birthDate \n            WHERE AuthorID = :id'<\/span>;\n\n    $stmt = $this<\/span>->conn->prepare($sql);\n    $stmt->execute([\n        'firstName'<\/span> => $author->getFirstName(),\n        'lastName'<\/span> => $author->getLastName(),\n        'birthDate'<\/span> => $author->getBirthDate(),\n        'id'<\/span> => $author->getId()\n    ]);\n\n    return<\/span> $stmt->rowCount() > 0<\/span>;\n}<\/code><\/span>Code language:<\/span> PHP<\/span> (<\/span>php<\/span>)<\/span><\/small><\/pre>\n\n\n
How it works.<\/p>\n\n\n\n
First, define a public method update()<\/code> that takes an Author<\/code> object as the parameter:<\/p>\n\n\n
public<\/span> function<\/span> update<\/span>(Author $author)<\/span><\/span><\/code><\/span>Code language:<\/span> PHP<\/span> (<\/span>php<\/span>)<\/span><\/small><\/pre>\n\n\n
$sql = 'UPDATE Authors \n        SET FirstName = :firstName, LastName = :lastName, BirthDate = :birthDate \n        WHERE AuthorID = :id'<\/span>;<\/code><\/span>Code language:<\/span> PHP<\/span> (<\/span>php<\/span>)<\/span><\/small><\/pre>\n\n\n
$stmt = $this<\/span>->conn->prepare($sql);<\/code><\/span>Code language:<\/span> PHP<\/span> (<\/span>php<\/span>)<\/span><\/small><\/pre>\n\n\n
Fourth, execute the prepared statement with values that come from the Author<\/code> object:<\/p>\n\n\n
$stmt->execute([\n   'firstName'<\/span> => $author->getFirstName(),\n   'lastName'<\/span> => $author->getLastName(),\n   'birthDate'<\/span> => $author->getBirthDate(),\n   'id'<\/span> => $author->getId()\n]);<\/code><\/span>Code language:<\/span> PHP<\/span> (<\/span>php<\/span>)<\/span><\/small><\/pre>\n\n\n
Finally, return true if the number of updated rows is greater than 0:<\/p>\n\n\n
return<\/span> $stmt->rowCount() > 0<\/span>;<\/code><\/span>Code language:<\/span> PHP<\/span> (<\/span>php<\/span>)<\/span><\/small><\/pre>\n\n\n
Step 2. Modify the index.php<\/code> file to use the update()<\/code> method:<\/p>\n\n\n
<?php<\/span>\n\nrequire_once<\/span> 'authordb.php'<\/span>;\n\n\/\/ Connect to the SQL Server<\/span>\n$conn = require_once<\/span> 'connect.php'<\/span>;\n\n$authorDB = new<\/span> AuthorDB($conn);\n\n\/\/ Find author by id 1<\/span>\n$author = $authorDB->findById(1<\/span>);\n\n\/\/ change the birth date<\/span>\n$author->setBirthDate('1980-06-30'<\/span>);\n\n\/\/ Update the author<\/span>\n$authorDB->update($author);<\/code><\/span>Code language:<\/span> PHP<\/span> (<\/span>php<\/span>)<\/span><\/small><\/pre>\n\n\n
How it works.<\/p>\n\n\n\n
First, load the authordb.php<\/code> file to use the AuthorDB<\/code> class.<\/p>\n\n\n
require_once<\/span> 'authordb.php'<\/span>;<\/code><\/span>Code language:<\/span> PHP<\/span> (<\/span>php<\/span>)<\/span><\/small><\/pre>\n\n\n
$conn = require_once<\/span> 'connect.php'<\/span>;<\/code><\/span>Code language:<\/span> PHP<\/span> (<\/span>php<\/span>)<\/span><\/small><\/pre>\n\n\n
Third, create a new AuthorDB<\/code> object:<\/p>\n\n\n
$authorDB = new<\/span> AuthorDB($conn);<\/code><\/span>Code language:<\/span> PHP<\/span> (<\/span>php<\/span>)<\/span><\/small><\/pre>\n\n\n
Fourth, find the Author<\/code> object with id 1:<\/p>\n\n\n
$author = $authorDB->findById(1<\/span>);<\/code><\/span>Code language:<\/span> PHP<\/span> (<\/span>php<\/span>)<\/span><\/small><\/pre>\n\n\n
Fifth, update the birth date to 1980-06-30<\/code>, assuming that the author with id 1 exists in the Authors<\/code> table:<\/p>\n\n\n
$author->setBirthDate('1980-06-30'<\/span>);<\/code><\/span>Code language:<\/span> PHP<\/span> (<\/span>php<\/span>)<\/span><\/small><\/pre>\n\n\n
Finally, call the update()<\/code> method of the AuthorDB<\/code> object to update the author:<\/p>\n\n\n
$authorDB->update($author);<\/code><\/span>Code language:<\/span> PHP<\/span> (<\/span>php<\/span>)<\/span><\/small><\/pre>\n\n\n
Step 3. Verify the update.<\/p>\n\n\n\n
Open SQL Server Management Studio (SSMS), connect to SQL Server, and execute the following query:<\/p>\n\n\n
SELECT<\/span> * FROM<\/span> authors<\/span>\nWHERE<\/span> AuthorID = 1<\/span>;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Output:<\/p>\n\n\n
AuthorID | FirstName | LastName | BirthDate\n---------+-----------+----------+------------\n1        | Anthony   | Pham     | 1980-06-30\n(1 row)<\/code><\/span>Code language:<\/span> plaintext<\/span> (<\/span>plaintext<\/span>)<\/span><\/small><\/pre>\n\n\n
The output indicates that the birth date of the author ID 1 is updated to 1980-06-30<\/code>.<\/p>\n\n\n\n
Summary #<\/a><\/h2>\n\n\n\n
    \n
  • Execute the UPDATE<\/code> statement to update data in a table.<\/li>\n<\/ul>\n
    \n\t
    \n\t\t
    Was this tutorial helpful?<\/div>\n\t\t
    \n\t\t\t\n\t\t\t\t\n\t\t\t\t\t<\/path>\n\t\t\t\t<\/svg>\n\t\t\t\t Yes <\/span>\n\t\t\t<\/button>\n\n\t\t\t\n\t\t\t\t\n\t\t\t\t\t<\/path>\n\t\t\t\t<\/svg>\n\t\t\t\t No <\/span>\n\t\t\t<\/button>\n\t\t<\/div>\n\t<\/header>\n\n\t
    \n\t\t
    \n\t\t\t
    <\/div>\n\t\t\t\n\t\t\t
Second, connect to the SQL Server<\/a> and assign the returned PDO object to the $conn<\/code> variable:<\/p>\n\n\n
Third, create a prepared statement for executing the UPDATE<\/a> statement:<\/p>\n\n\n
Second, construct an UPDATE<\/a> statement that updates the first name, last name, and birth date of an author specified by an id:<\/p>\n\n\n