Delete MySQL Records using PHP PDO

To delete a record with PDO, prepare a DELETE statement, bind the record ID and restrict the deletion with a WHERE condition.

<?php
require 'config.php';

$id=2;

$sql="DELETE FROM student
      WHERE id=:id";

try{
    $stmt=$dbo->prepare($sql);

    $stmt->bindValue(
        ':id',
        $id,
        PDO::PARAM_INT
    );

    $stmt->execute();

    $deleted=$stmt->rowCount();

    if($deleted===1){
        echo 'Record deleted successfully.';
    }else{
        echo 'Record not found.';
    }
}catch(PDOException $e){
    error_log($e->getMessage());
    echo 'Unable to delete the record.';
}

DELETE with a WHERE Condition Top ↑

The WHERE condition identifies which record should be removed. Here the student ID is used because it uniquely identifies the row.

$id=2;

$sql="DELETE FROM student
      WHERE id=:id";

$stmt=$dbo->prepare($sql);

$stmt->bindValue(
    ':id',
    $id,
    PDO::PARAM_INT
);

$stmt->execute();

Keeping the value in a placeholder avoids putting PHP variable data directly inside the SQL statement.

Delete a Record Submitted by POST Top ↑

A destructive operation such as deleting a database record should normally be performed through a POST request rather than directly through a GET link.

A simple delete form can send the record ID:

<form method="post">
<input type="hidden" name="id" value="5">
<button type="submit" class="btn btn-danger">Delete Record</button>
</form>

The receiving PHP code validates the ID before executing the DELETE statement:

<?php
require 'config.php';

if($_SERVER['REQUEST_METHOD']!=='POST'){
    exit('Invalid request.');
}

$id=filter_input(
    INPUT_POST,
    'id',
    FILTER_VALIDATE_INT,
    [
        'options' => [
            'min_range' => 1
        ]
    ]
);

if($id===false || $id===null){
    exit('Invalid student ID.');
}

$sql="DELETE FROM student
      WHERE id=:id";

try{
    $stmt=$dbo->prepare($sql);

    $stmt->bindValue(
        ':id',
        $id,
        PDO::PARAM_INT
    );

    $stmt->execute();

    if($stmt->rowCount()===1){
        echo 'Student record deleted.';
    }else{
        echo 'Student record not found.';
    }
}catch(PDOException $e){
    error_log($e->getMessage());
    echo 'Unable to delete the student record.';
}

Delete Multiple Matching Records Top ↑

A DELETE query can remove more than one row when several records satisfy the condition.

For example, this query removes students whose IDs are greater than a supplied value:

$minimum_id=10;

$sql="DELETE FROM student
      WHERE id>:minimum_id";

$stmt=$dbo->prepare($sql);

$stmt->bindValue(
    ':minimum_id',
    $minimum_id,
    PDO::PARAM_INT
);

$stmt->execute();

echo 'Records deleted: '.$stmt->rowCount();

The SQL comparison operators tutorial covers conditions such as greater than, less than and equality.

Count Deleted Rows with rowCount() Top ↑

After a DELETE statement is executed, rowCount() can report how many rows were removed.

$sql="DELETE FROM student
      WHERE id>:minimum_id";

$stmt=$dbo->prepare($sql);

$stmt->bindValue(
    ':minimum_id',
    10,
    PDO::PARAM_INT
);

$stmt->execute();

$deleted=$stmt->rowCount();

echo 'Number of records deleted: '.$deleted;

Unlike an UPDATE, where zero affected rows can also mean that matching values were unchanged, a DELETE returning zero normally means that no rows matched the deletion condition.

DELETE inside a PDO Transaction Top ↑

A single independent DELETE does not normally require an explicit transaction. Transactions become useful when the deletion is one part of several related database operations that must succeed together.

$id=5;

try{
    $dbo->beginTransaction();

    $stmt=$dbo->prepare(
        "DELETE FROM student
         WHERE id=:id"
    );

    $stmt->bindValue(
        ':id',
        $id,
        PDO::PARAM_INT
    );

    $stmt->execute();

    if($stmt->rowCount()!==1){
        $dbo->rollBack();
        exit('Student record not found.');
    }

    // Other related database operations can run here.

    $dbo->commit();

    echo 'Transaction completed.';
}catch(Throwable $e){
    if($dbo->inTransaction()){
        $dbo->rollBack();
    }

    error_log($e->getMessage());

    echo 'Transaction failed.';
}

The database table must use a storage engine that supports transactions for rollback behavior to work as expected.

Rollback a DELETE before Commit Top ↑

A DELETE executed inside an active transaction can be rolled back before the transaction is committed.

$id=5;

try{
    $dbo->beginTransaction();

    $stmt=$dbo->prepare(
        "DELETE FROM student
         WHERE id=:id"
    );

    $stmt->bindValue(
        ':id',
        $id,
        PDO::PARAM_INT
    );

    $stmt->execute();

    // The application decides not to keep the deletion.
    $dbo->rollBack();

    echo 'Transaction rolled back. No deletion was saved.';
}catch(Throwable $e){
    if($dbo->inTransaction()){
        $dbo->rollBack();
    }

    error_log($e->getMessage());

    echo 'Unable to complete the operation.';
}

Once commit() succeeds, the transaction is complete and the DELETE cannot be undone by calling rollBack().

Handle PDO DELETE Errors Top ↑

Database errors should be logged on the server rather than displaying SQL or connection details to visitors.

try{
    $stmt=$dbo->prepare(
        "DELETE FROM student
         WHERE id=:id"
    );

    $stmt->bindValue(
        ':id',
        $id,
        PDO::PARAM_INT
    );

    $stmt->execute();

    echo 'Records deleted: '.$stmt->rowCount();
}catch(PDOException $e){
    error_log($e->getMessage());

    echo 'Unable to delete the record.';
}

The PDO connection configuration can set exception mode and native prepared statements once, rather than changing connection attributes only after a query has failed.

Deleting All Records from a Table Top ↑

A DELETE statement without a WHERE condition removes every row while leaving the table structure in place.

$stmt=$dbo->prepare(
    "DELETE FROM student"
);

$stmt->execute();

$deleted=$stmt->rowCount();

echo 'Records deleted: '.$deleted;

If you need the SQL syntax and behavior in more detail, see the SQL DELETE tutorial.

Using TRUNCATE TABLE Top ↑

TRUNCATE TABLE also removes all records, but it behaves differently from DELETE. In MySQL it is treated as a DDL operation and rowCount() should not be used to determine the number of rows removed.

$stmt=$dbo->prepare(
    "TRUNCATE TABLE student"
);

$stmt->execute();

If the number of existing rows is required, count them before truncating the table:

$total=(int)$dbo->query(
    "SELECT COUNT(*) FROM student"
)->fetchColumn();

$stmt=$dbo->prepare(
    "TRUNCATE TABLE student"
);

$stmt->execute();

echo 'Records before TRUNCATE: '.$total;

See the SQL COUNT() tutorial for counting records before a table-wide operation.

DELETE vs TRUNCATE vs DROP Top ↑

Use DELETE when records need to be removed conditionally. Use TRUNCATE when the intention is to empty the entire table and its database-specific behavior is acceptable. Use DROP TABLE only when the table itself should be removed.

For MySQL auto-generated IDs, see the AUTO_INCREMENT tutorial.

PDO rowCount() after DELETE Video Top ↑

Common PDO DELETE Problems Top ↑

Deleting without a WHERE Condition Top ↑

A DELETE without WHERE removes every matching row, which means every row when no condition is supplied.

// Deletes every row in student
DELETE FROM student

Putting the Record ID Directly into SQL Top ↑

When the ID comes from a form, URL, session or another external source, validate it and pass it through a prepared-statement placeholder.

Deleting through a GET Link Top ↑

A GET request is suitable for displaying a confirmation page, but the actual state-changing delete action should normally use POST.

Not Checking rowCount() Top ↑

For a single-record DELETE, checking rowCount() helps distinguish a successful deletion from a request for a record that does not exist.

Displaying Raw Database Errors Top ↑

Do not display errorInfo(), exception messages or database details directly to visitors. Log technical errors on the server and show a short public message.

Expecting TRUNCATE to Behave Like DELETE Top ↑

TRUNCATE has different transaction and affected-row behavior. Use it only when the intention is to empty the complete table.

Using Transactions without Checking Database Support Top ↑

Rollback requires a database and table engine that supports transactional operations.

Related PHP PDO Tutorials Top ↑

Download the PDO sample scripts from the main PDO tutorial.

Frequently Asked Questions Top ↑

Subhendu Mohapatra

Author & Instructor at plus2net

I write and maintain practical tutorials on Python, PHP, SQL, JavaScript, HTML, jQuery, and web development at plus2net. The tutorials focus on clear explanations, working examples, and code that readers can test and adapt while learning.

About the author   Watch on YouTube

Connect: