{"id":4422,"date":"2024-07-31T08:55:44","date_gmt":"2024-07-31T01:55:44","guid":{"rendered":"https:\/\/www.sqlservertutorial.net\/?page_id=4422"},"modified":"2024-08-18T10:14:52","modified_gmt":"2024-08-18T03:14:52","slug":"php-sql-server-delete","status":"publish","type":"page","link":"https:\/\/www.sqlservertutorial.net\/php-sql-server\/php-sql-server-delete\/","title":{"rendered":"PHP SQL Server: Deleting Data"},"content":{"rendered":"\n

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

This tutorial begins where\u00a0the updating data in a table from PHP tutorial<\/a>\u00a0left off.<\/p>\n\n\n\n

We’ll delete a row from the Authors<\/code> table by id.<\/p>\n\n\n\n

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

Step 1. Add the delete()<\/code> method to AuthorDB<\/code> to delete an author by ID:<\/p>\n\n\n

public<\/span> function<\/span> delete<\/span>(Author $author)<\/span>\n<\/span>{\n    $sql = 'DELETE FROM Authors WHERE AuthorID = :id'<\/span>;\n    $stmt = $this<\/span>->conn->prepare($sql);\n    $stmt->execute(['id'<\/span> => $author->getId()]);\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 delete()<\/code> that takes an Author<\/code> object as the parameter:<\/p>\n\n\n
public<\/span> function<\/span> delete<\/span>(Author $author)<\/span><\/span><\/code><\/span>Code language:<\/span> PHP<\/span> (<\/span>php<\/span>)<\/span><\/small><\/pre>\n\n\n
Second, construct an DELETE<\/code> statement that deletes a row from the Authors<\/code> table where the AuthorId<\/code> match a specified id:<\/p>\n\n\n
$sql = 'DELETE FROM Authors WHERE AuthorID = :id'<\/span>;<\/code><\/span>Code language:<\/span> PHP<\/span> (<\/span>php<\/span>)<\/span><\/small><\/pre>\n\n\n
Third, create a prepared statement for executing the DELETE<\/code> statement:<\/p>\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 the id that comes from the Author<\/code> object:<\/p>\n\n\n
$stmt->execute(['id'<\/span> => $author->getId()]);<\/code><\/span>Code language:<\/span> PHP<\/span> (<\/span>php<\/span>)<\/span><\/small><\/pre>\n\n\n
Finally, return true if the number of deleted 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> to use the delete()<\/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\/\/ Delete the author<\/span>\n$authorDB->delete($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 in the script:<\/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
Second, connect to the SQL Server<\/a> and assign the returned PDO object to the $conn<\/code> variable:<\/p>\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
Finally, call the delete()<\/code> method of the AuthorDB<\/code> object to delete the author:<\/p>\n\n\n
$authorDB->delete($author);<\/code><\/span>Code language:<\/span> PHP<\/span> (<\/span>php<\/span>)<\/span><\/small><\/pre>\n\n\n
Step 3. Run the index.php<\/code> on the web browser.<\/p>\n\n\n\n
Step 4. Verify the deletion.<\/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
It returns an empty set, meaning the application successfully deleted the author with id 1.<\/p>\n\n\n\n
Summary #<\/a><\/h2>\n\n\n\n
    \n
  • Execute the DELETE<\/code> statement to delete data from 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