Delete record using PDO MySql connection

WE can delete a record using delete query through PDO connection Our database connection object $dbo is declared inside config.php file and it is connected to database .
id is the unique identity of the record which is to be deleted.

rowcount() to get number of records affected by Query involving any Delete , update & insert command

$id=2; // Unique id of the student record. 
$step=$dbo->prepare("DELETE FROM student WHERE id=:id");
$step->bindParam(":id",$id,PDO::PARAM_INT);
$step->execute();

In the above code one record will be deleted from the table based on the id number stored in the variable $id. We can also delete more records matching to a condition.
Let us try to delete all records having id number more than 10.
Here is the code.

$step=$dbo->prepare("DELETE FROM student WHERE id > 10 ");
$step->execute();

All records having id more than 10 will be deleted. We can also know how many records are deleted by using rowCount() command. This command will return a numeric value saying us number of records deleted by the query just given. Here is the sample code.

$step=$dbo->prepare("DELETE FROM student WHERE id > 10 ");
$step->execute();
$no=$step->rowCount();
echo " No of records deleted = ".$no;

Example: Transaction with DELETE and Rollback in PHP PDO

In this example, we use a DELETE query inside a transaction. If a condition arises where we do not want to delete the student record, we can rollback the operation to restore the data.

The DELETE query is executed to remove a student by ID.
rollBack() is called to undo the delete operation and restore the data.
If an error occurs, the changes are also rolled back, ensuring data integrity.
More on PDO Transactions

try {
    $dbo->beginTransaction(); // Start the transaction
    
    // Prepare and execute the DELETE query
    $stmt = $dbo->prepare("DELETE FROM student WHERE id = ?");
    $stmt->execute([5]);  // Assume we are deleting the student with ID 5
    
    // If we decide not to proceed, rollback
    $dbo->rollBack(); 
    echo "Transaction rolled back, no changes made.";
    
} catch (PDOException $e) {
    // In case of error, rollback the transaction
    $dbo->rollBack();
    echo "Error: " . $e->getMessage();
}

Using commit to complete the delete process.

The transaction starts with beginTransaction().
The DELETE query removes the record with a specific ID.
If successful, commit() makes the deletion permanent.
If an error occurs, rollBack() reverts the changes.

try {    
    $dbo->beginTransaction();// Start the transaction
    
    // Prepare and execute the DELETE query
    $stmt = $dbo->prepare("DELETE FROM student WHERE id = ?");
    $stmt->execute([5]);  // Deleting student with ID 5
    
    // Commit the transaction to make the deletion permanent
    $dbo->commit();
    echo "Record deleted successfully, transaction committed.";
    
} catch (PDOException $e) {
    // Rollback in case of an error
    $dbo->rollBack();
    echo "Error: " . $e->getMessage();
}

DELETE with Error Message

If the delete operation fails, PDO's errorInfo() retrieves error details. The error information is looped through and concatenated into the $msg string.
Emulation Mode: The line $dbo->setAttribute(PDO::ATTR_EMULATE_PREPARES, false); disables emulated prepared statements for real database query handling.

$step=$dbo->prepare("DELETE FROM student where id > 10 ");
if($step->execute()){
$msg = "No of records deleted =". $step->rowCount();
}else{
$str='';
$dbo->setAttribute(PDO::ATTR_EMULATE_PREPARES,false); 
$a=$step->errorInfo();
while (list ($key, $val) = each ($a)  ) {
$str .= "<br>$key -> $val";
}
$msg .= " Not able to delete record  please contact Admin: Error Message :  $str";
}

Deleting all the records of a table

We can empty any table by using TRUNCATE command. But after deleting we will not get number of records deleted by using rowCount().
Read More on TRUNCATE command
Here is the code .

$step=$dbo->prepare("TRUNCATE student ");
//$step=$dbo->prepare("DELETE FROM student");

$step->execute();
$no=$step->rowCount();
echo " No of records deleted = ".$no;

Above code will delete all records but we won't get any output saying number of records deleted.

By using delete command we can get the number of records deleted in the table.

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

Use this line ( by removing comment from this and adding comment to TRUNCATE query ) to get number of records deleted.

Command	Operation	Key Differences
DELETE	Removes rows based on a condition.	Can use WHERE clause. Logs individual deletions. rowCount() works.
TRUNCATE	Removes all rows from the table.	No WHERE clause. Faster than DELETE. Resets auto-increment. rowCount() doesn't work.
DROP	Deletes the entire table structure.	Removes both data and structure. Cannot be rolled back. rowCount() doesn't apply.

PDO References Delete Table

Subscribe to our YouTube Channel here

Delete Query to remove records using PDO

Example: Transaction with DELETE and Rollback in PHP PDO

Using commit to complete the delete process.

DELETE with Error Message

Deleting all the records of a table

Subscribe