We will add conditions to our DELETE query by using WHERE.
Deleting single record
By using record ID ( unique ) we will delete a record.
$my_conn = new PDO('sqlite:my_student.sqlite3');
// Set errormode to exceptions
$my_conn->setAttribute(PDO::ATTR_ERRMODE,PDO::ERRMODE_EXCEPTION);
$id=3;
$query="DELETE FROM student where id=:id";
$sql=$my_conn->prepare($query);
$sql->bindParam(':id',$id,PDO::PARAM_INT, 5);
if($sql->execute()){
echo "Successfully deleted record ";
echo "<br><br>Number of rows deleted : ".$sql->rowCount();
}
else{
print_r($sql->errorInfo()); // if any error is there it will be posted
$msg=" Database problem, please contact site admin ";
}
$my_conn = null;
Output is here
Successfully deleted record
Number of rows deleted : 1
Deleting more records
We will delete all rows of class equal to Four
$class='Four';
$query="DELETE FROM student where class=:class";
$sql=$my_conn->prepare($query);
$sql->bindParam(':class',$class,PDO::PARAM_STR, 15);
if($sql->execute()){
echo "Successfully deleted records ";
echo "<br><br>Number of rows deleted : ".$sql->rowCount();
}
else{
print_r($sql->errorInfo()); // if any error is there it will be posted
$msg=" Database problem, please contact site admin ";
}
$my_conn = null;
Output is here
Successfully deleted records
Number of rows deleted : 9
Deleting all records
There is no TRUNCATE command to delete all records in SQLite, so we will use DELETE command to remove all records from table.
$query="DELETE FROM student"; // Query to delete all records
$sql=$my_conn->prepare($query);
if($sql->execute()){
echo "Successfully deleted records ";
echo "<br><br>Number of rows deleted : ".$sql->rowCount();
}
else{
print_r($sql->errorInfo()); // if any error is there it will be posted
$msg=" Database problem, please contact site admin ";
}
$my_conn = null;
Output ( balance records are deleted from student table)
Successfully deleted records
Number of rows deleted : 25