Delete record using PDO MySql connection

WE can delete a record using delete query through PDO connection We assumed that config.php file is already available 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;
$step=$dbo->prepare("DELETE FROM pdo_admin 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 pdo_admin 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 pdo_admin WHERE id > 10 ");
$step->execute();
$no=$step->rowCount();
echo " No of records deleted = ".$no;

DELETE with Error Message

To display the error message we will modify the above query like this.

$step=$dbo->prepare("delete from pdo_admin 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_del ");
//$step=$dbo->prepare("delete from student_del");

$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_del");

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

Subscribe to our YouTube Channel here

Delete Query using PDO

DELETE with Error Message

Deleting all the records of a table

Subscribe