PHP SQL Server: Deleting Data

Summary: in this tutorial, you will learn how to delete data from a table in SQL Server using PHP.

This tutorial begins where the updating data in a table from PHP tutorial left off.

We’ll delete a row from the Authors table by id.

Deleting data

Step 1. Add the delete() method to AuthorDB to delete an author by ID:

public function delete(Author $author)
{
    $sql = 'DELETE FROM Authors WHERE AuthorID = :id';
    $stmt = $this->conn->prepare($sql);
    $stmt->execute(['id' => $author->getId()]);
    return $stmt->rowCount() > 0;
}Code language: PHP (php)

How it works.

First, define a public method delete() that takes an Author object as the parameter:

public function delete(Author $author)Code language: PHP (php)

Second, construct an DELETE statement that deletes a row from the Authors table where the AuthorId match a specified id:

$sql = 'DELETE FROM Authors WHERE AuthorID = :id';Code language: PHP (php)

Third, create a prepared statement for executing the DELETE statement:

$stmt = $this->conn->prepare($sql);Code language: PHP (php)

Fourth, execute the prepared statement with the id that comes from the Author object:

$stmt->execute(['id' => $author->getId()]);Code language: PHP (php)

Finally, return true if the number of deleted rows is greater than 0:

return $stmt->rowCount() > 0;Code language: PHP (php)

Step 2. Modify the index.php to use the delete() 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);

// Delete the author
$authorDB->delete($author);Code language: PHP (php)

How it works.

First, load the authordb.php file to use the AuthorDB class in the script:

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)

Finally, call the delete() method of the AuthorDB object to delete the author:

$authorDB->delete($author);Code language: PHP (php)

Step 3. Run the index.php on the web browser.

Step 4. Verify the deletion.

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)

It returns an empty set, meaning the application successfully deleted the author with id 1.

Summary

• Execute the DELETE statement to delete data from a table.