Delete multiple records from MySQL in PHP


Delete records one by one is very time-consuming. Multiple records delete on a single click is strongly recommended for every data management section. Today we will discuss how to delete multiple rows from MySQL database in PHP with checkbox.

In this tutorial, we will display records from users table using MySQL and PHP. The checkboxes should be displayed with each row. Checkboxes would be used for select multiple records from users list. We will also provide a checkbox at the header for select all records on a single click. Once the delete button is clicked, we will display an alert popup for confirmation. After confirmation the selected records would be removed from the database.

users table:

Into the users table we have stored users first_name, last_name, email and phone fields.

CREATE TABLE `users` (
    `id` int(11) NOT NULL AUTO_INCREMENT,
    `first_name` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
    `last_name` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
    `email` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
    `phone` varchar(15) COLLATE utf8_unicode_ci NOT NULL,
    `created` datetime NOT NULL,
    `modified` datetime NOT NULL,
    `status` tinyint(1) NOT NULL DEFAULT '1' COMMENT '1=Active, 0=Deactive',
    PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

To implement delete multiple records in PHP we have used 3 files, the files details are given below.


This file is used to connect with the database.

    $dbHost 'localhost';  //database host name
$dbUser 'root';       //database username
$dbPass '';           //database password
$dbName 'semicolonworld'//database name
$conn mysqli_connect($dbHost,$dbUser,$dbPass,$dbName);
"Database connection failed: " mysqli_connect_error());


At first, you need to include jQuery library. JavaScript delete_confirm() function is used for display delete confirmation popup.
Into the $(document).ready(function(){}); we have wrote some jQuery for implement select all checkbox functionality.

<script src="jquery.min.js"></script>
<script type="text/javascript">
function deleteConfirm(){
    var result = confirm("Are you sure to delete users?");
        return true;
        return false;

                this.checked = true;
                this.checked = false;
        if($('.checkbox:checked').length == $('.checkbox').length){

For database access we have included the dbConfig.php file. Using MySQL we will fetch the users data from users table and display the users row into table using PHP.

$query mysqli_query($conn,"SELECT * FROM users");
<form name="bulk_action_form" action="action.php" method="post" onsubmit="return deleteConfirm();"/> <table class="bordered"> <thead> <tr> <th><input type="checkbox" name="select_all" id="select_all" value=""/>th> <th>First Nameth> <th>Last Nameth> <th>Emailth> <th>Phoneth> tr> thead> if(mysqli_num_rows($query) > 0){ while($row = mysqli_fetch_assoc($query)){ ?> <tr> <td align="center"><input type="checkbox" name="checked_id[]" class="checkbox" value="echo $row['id']; ?>"/>td> <td>echo $row['first_name']; ?>td> <td>echo $row['last_name']; ?>td> <td>echo $row['email']; ?>td> <td>echo $row['phone']; ?>td> tr> else{ ?> <tr><td colspan="5">No records>tr> table> <input type="submit" class="btn btn-danger" name="bulk_delete_submit" value="Delete"/> form>


Once the delete button is clicked the form would be submitted to the action.php file. In this file we will get the selected rows ID and delete records from users table depending on the respective ID. When the data deletion process is completed, we will store success message into SESSION and redirect to index.php.

$idArr $_POST['checked_id'];
$idArr as $id){
mysqli_query($conn,"DELETE FROM users WHERE id=".$id);
$_SESSION['success_msg'] = 'Users have been deleted successfully.';

 If you have any query about this tutorial and scripts, feel free to comment here.

Related Articles

Comments 0