PHP SQL Server: Deleting Data

You organize your data. Now organize your savings.

Set savings goals, track your progress, and stay on course.

Get Savigo for iPhone ›

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;
}Copy

How it works.

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

public function delete(Author $author)Copy

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';Copy

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

$stmt = $this->conn->prepare($sql);Copy

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

$stmt->execute(['id' => $author->getId()]);Copy

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

return $stmt->rowCount() > 0;Copy

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);Copy

How it works.

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

require_once 'authordb.php';Copy

Second, connect to the SQL Server and assign the returned PDO object to the $conn variable:

$conn = require_once 'connect.php';Copy

Third, create a new AuthorDB object:

$authorDB = new AuthorDB($conn);Copy

Fourth, find the Author object with id 1:

$author = $authorDB->findById(1);Copy

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

$authorDB->delete($author);Copy

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;Copy

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.