PHP SQLite delete single or multiple record and get the number of rows affected by the query

We will create the database and connection object.

$my_conn = new PDO('sqlite:my_student.sqlite3');

We will use $my_conn to execute our query.

By using delete query we will remove records.

We will add conditions to our DELETE query by using WHERE.

Delete table after user confirmation using PHP SQLite PDO object A4

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

Download sample script for SQLite with instructions on how to use.

Update records Delete the Table with all records

PHP SQLite PDO Functions

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:

Subscribe to our YouTube Channel here

PHP SQLite delete record and get affected rows

Deleting single record

Deleting more records

Deleting all records

Subhendu Mohapatra