Delete Multiple Records from MySQL Database in PHP

It is very time-consuming to delete records one by one from a large amount of data in the list. We can make it easier by deleting multiple records at once. With the bulk delete feature, you can make the large data list user-friendly by allowing the user to delete multiple rows with a single click. Delete multiple records with a single click is strongly recommended for the data management section in the web application.

You can use the checkbox to select each record and delete all the selected rows after the remove request submission. Also, jQuery can be used to select or unselect all checkboxes at once in the data list. In this tutorial, we will show you how to delete multiple records from MySQL database in PHP with checkbox.

In the example code, we will implement the following operations to delete multiple records using checkboxes in PHP.

• Fetch all users data from the database and list them in an HTML table.
• Add a checkbox in each row to select multiple records in the user’s data list.
• Add a checkbox in the table header to check or uncheck all checkboxes on a single click.
• Add delete button to remove all selected users from the table of MySQL database.

Create Database Table

A table needs to be created in the database to store the user’s data. The following SQL creates a users table with some basic fields in the MySQL database.

CREATE TABLE `users` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `first_name` varchar(100) NOT NULL,
  `last_name` varchar(100) NOT NULL,
  `email` varchar(200) NOT NULL,
  `phone` varchar(15) 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;

Database Configuration (dbConfig.php)

The dbConfig.php file is used to connect and select the database. Specify the database hostname ($dbHost), username ($dbUsername), password ($dbPassword), and name ($dbName) as per your MySQL database credentials.

<?php
// Database configuration
$dbHost     = "localhost";
$dbUsername = "root";
$dbPassword = "root";
$dbName     = "codexworld";

// Create database connection
$db = new mysqli($dbHost, $dbUsername, $dbPassword, $dbName);

// Check connection
if ($db->connect_error) {
    die("Connection failed: " . $db->connect_error);
}
?>

Data List with Checkboxes

In this file, we will fetch all the records from the users table and list them with checkboxes in an HTML table.

JavaScript Code:
jQuery is used to integrate the delete confirmation dialog and select all checkboxes functionality. So, include the jQuery library first.

<script src="https://ajax.googleapis.com/ajax/libs/jquery/3.7.0/jquery.min.js"></script>

The delete_confirm() function checks whether the user selects at least one checkbox and display an alert if the user has not checked any checkbox using JavaScript. Also, displays a confirmation popup before submitting the delete request to the server-side script.

function delete_confirm(){
    if($('.checkbox:checked').length > 0){
        var result = confirm("Are you sure to delete selected users?");
        if(result){
            return true;
        }else{
            return false;
        }
    }else{
        alert('Select at least 1 record to delete.');
        return false;
    }
}

The following jQuery code is used to implement the Select / Deselect All CheckBoxes functionality. It helps the user to check / uncheck all checkboxes in the table with a single click.

$(document).ready(function(){
    $('#select_all').on('click',function(){
        if(this.checked){
            $('.checkbox').each(function(){
                this.checked = true;
            });
        }else{
             $('.checkbox').each(function(){
                this.checked = false;
            });
        }
    });
	
    $('.checkbox').on('click',function(){
        if($('.checkbox:checked').length == $('.checkbox').length){
            $('#select_all').prop('checked',true);
        }else{
            $('#select_all').prop('checked',false);
        }
    });
});

HTML & PHP Code:
The user can check single or multiple records using checkbox in the table and delete multiple records from the MySQL database.

• Include the dbConfig.php file to connect the MySQL database.
• All the records are retrieved from the users table.
• Multiple records can be selected using checkbox provided on each table row.
• By clicking the checkbox in the table header, all checkboxes will be checked or unchecked.
• Once the delete button is clicked, a confirmation dialog will appear.
• After the confirmation, the form is submitted to delete selected rows from the database.

<!-- Display the status message -->
<?php if(!empty($statusMsg)){ ?>
<div class="alert alert-success"><?php echo $statusMsg; ?></div>
<?php } ?>

<!-- Users data list -->
<form name="bulk_action_form" action="delete_submit.php" method="post" onSubmit="return delete_confirm();"/>
    <table class="bordered">
        <thead>
        <tr>
            <th><input type="checkbox" id="select_all" value=""/></th>        
            <th>First Name</th>
            <th>Last Name</th>
            <th>Email</th>
            <th>Phone</th>
        </tr>
        </thead>
        <?php
        // Include the database configuration file
        include_once 'dbConfig.php';
        
        // Get users from the database
        $query = $db->query("SELECT * FROM users ORDER BY id DESC");
        
        // List all records
        if($query->num_rows > 0){
            while($row = $query->fetch_assoc()){
        ?>
        <tr>
            <td><input type="checkbox" name="checked_id[]" class="checkbox" value="<?php echo $row['id']; ?>"/></td>        
            <td><?php echo $row['first_name']; ?></td>
            <td><?php echo $row['last_name']; ?></td>
            <td><?php echo $row['email']; ?></td>
            <td><?php echo $row['phone']; ?></td>
        </tr>
        <?php } }else{ ?>
            <tr><td colspan="5">No records found.</td></tr>
        <?php } ?>
    </table>
    <input type="submit" class="btn btn-danger" name="bulk_delete_submit" value="Delete"/>
</form>

After the confirmation, the form is submitted to the server-side script (delete_submit.php) for proceeding with the delete request.

Delete All Records from Database with PHP

The delete_submit.php file handles the multiple records delete operations with PHP and MySQL.

• Check whether the delete request is submitted.
• Check whether the user selects at least one record and the ID array is not empty.
• Get selected IDs using PHP $_POST method and convert this array to a string using implode() function in PHP.
• Delete records from the database based on the selected user’s ID using PHP and MySQL.
• Display the status message on the web page.

<?php 
 
// Include the database configuration file 
include_once 'dbConfig.php'; 
 
// If record delete request is submitted 
if(isset($_POST['bulk_delete_submit'])){ 
    // If id array is not empty 
    if(!empty($_POST['checked_id'])){ 
        // Get all selected IDs and convert it to a string 
        $idStr = implode(',', $_POST['checked_id']); 
         
        // Delete records from the database 
        $delete = $db->query("DELETE FROM users WHERE id IN ($idStr)"); 
         
        // If delete is successful 
        if($delete){ 
            $statusMsg = 'Selected users have been deleted successfully.'; 
        }else{ 
            $statusMsg = 'Some problem occurred, please try again.'; 
        } 
    }else{ 
        $statusMsg = 'Select at least 1 record to delete.'; 
    } 
} 
 
?>

PHP CRUD Operations with MySQLi Extension

Conclusion

The multiple records removal functionality is very useful for the data list. You can use bulk delete functionality in the data management section of your web application. It will make your web application user-friendly because the user doesn’t need to click multiple times to delete multiple records from the database. Using our example code you can check/uncheck all records at once and delete all records from the database on a single click using PHP.