DELETE records using mysqli functions with binding parameters

Delete with Where Query (without parameters ), Object Oriented Style

require "config.php";// Database connection file.
$query="DELETE FROM student WHERE class='Four'";
$connection->query($query);
echo " Records Deleted ".$connection->affected_rows;

Output

Records Deleted 9

Student Table : Sample Data and query to create table
config.php Database connection object $connection is taken from config.php file.
mysqli_affected_rows() Getting number of rows affected

if($stmt=$connection->query("DELETE FROM student WHERE class='Four'")){
	$msg.= " One class deleted  <br>";
}else { 
$msg.="  Class  can't be deleted <br>" ;
}

With Parameters by using bind_param()

<?Php
require "config.php";// Database connection
/////////////////////
$id=5;
$query="DELETE FROM student  WHERE id=?";
$stmt = $connection->prepare($query);
if ($stmt) {
$stmt->bind_param('i', $id);
$stmt->execute();
echo "Record Deleted :";
echo $stmt->affected_rows;
}else{
echo $connection->error;
}
?>

Using multiple parameters to Delete record

Object Oriented Style

$id=10;
$class='Four';
$query="DELETE FROM student  WHERE id=? AND class=?";
$stmt = $connection->prepare($query);
if ($stmt) {
$stmt->bind_param('is', $id,$class);
$stmt->execute();
echo "Number of Record Deleted :". $stmt->affected_rows;
}else{
echo $connection->error;
}

Output

Number of Record Deleted :1

Procedural style

<?Php
require "config.php";// Database connection
/////////////////////
$id=6;
$class='Four';
$query="DELETE FROM  student  WHERE class=? AND id=?";
if ($stmt = mysqli_prepare($connection,$query)){
mysqli_stmt_bind_param($stmt, "si",$class, $id);
mysqli_stmt_execute($stmt);
echo "Number of Record Deleted :";
echo mysqli_affected_rows($connection);
}else{
echo mysqli_error($connection);
}
?>

MySQL DELETE Query

Transaction with DELETE and Rollback using try-catch block

More on Try - Catch - Finally code blocks to handle error

try {
    // Start transaction
    $connection->begin_transaction();

    // DELETE query (trying to delete a student record)
    $sql = "DELETE FROM student WHERE id = 1";
    if (!$connection->query($sql)) {
        throw new Exception("Delete failed: " . $connection->error);
    }

    // Simulating an error after DELETE for rollback demonstration
    if (true) {  // This condition triggers rollback, replace with actual condition
        throw new Exception("Simulated error, rolling back.");
    }

    // Commit transaction
    $connection->commit();
    echo "Transaction Successful. Changes committed.";

} catch (Exception $e) {
    // Rollback transaction in case of an error
    $connection->rollback();
    echo "Transaction Failed. Rolled back. Error: " . $e->getMessage();
}

// Close connection
$connection->close();

Transaction Start: We begin the transaction using $connection->begin_transaction().
DELETE Query: The query tries to delete a record with id = 1 from the student table.
Error Simulation: After the DELETE query, we simulate an error using an exception to trigger a rollback.
Rollback: If an error occurs, the transaction is rolled back using $connection->rollback().
Commit: If there are no errors, the changes are committed to the database with $connection->commit().

Make sure you replace the simulated condition with an actual check based on your use case.

DELETE with Error Message

If the delete operation fails, MySQLi's error method retrieves the error details. The error information is concatenated into the $msg string.

$sql = "DELETE FROM student WHERE id > 10";
if($connection->query($sql) === TRUE) {
    $msg = "No of records deleted = " . $connection->affected_rows;
} else {
    $msg = "Not able to delete record. Please contact Admin. Error Message: " . $connection->error;
}

Deleting all the records of a table

We can empty any table by using the TRUNCATE command. However, after using TRUNCATE, we won't get the number of records deleted since affected_rows will not work.
Read More on TRUNCATE command
Here is the MySQLi code:

$sql = "TRUNCATE TABLE student";
// $sql = "DELETE FROM student"; // Use this to get number of records deleted.

if($connection->query($sql) === TRUE) {
    $no = $connection->affected_rows;
    echo "No of records deleted = " . $no;
} else {
    echo "Error: " . $connection->error;
}

The above code will delete all records but won't display the number of records deleted when using TRUNCATE.

By using the DELETE command instead of TRUNCATE, you can get the number of deleted records.

$sql = "DELETE FROM student";

You can uncomment the DELETE query and comment out the TRUNCATE query to get the number of records deleted.

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

MySQL DUMP of student table

Podcast on MySQL database management using MySQLi connector

MYSQLI Functions mysqli_num_rows() Number of rows in result set SELECT query UPDATE query

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

MYSQLI DELETE query

Using multiple parameters to Delete record

Transaction with DELETE and Rollback using try-catch block

DELETE with Error Message

Deleting all the records of a table

Subhendu Mohapatra