In this tutorial, You will learn to delete multiple records with checkboxes in PHP and MYSQL step by step. Even You will get a complete source code that will be very easy to integrate into your website.
Generally, We need to delete display the largest records of data in the HTML table. It is very inconvenient to delete all or multiple records one by one and It also takes more must time. So, You need to implement a bulk delete feature in your project.
In the case of Bulk delete functionality, you can easily delete all or multiple records from the MySQL Database at once by selecting using checkboxes.
Delete Multiple Rows From MySQL Database with PHP
Now, Let’s start coding to delete multiple rows from the MySQL database from the next steps. But getting started, You should have already stored records in the database. Otherwise, first of all, you will have to insert data into the database using PHP & MYSQL then start coding for deleting data from the database.
myproject/ |__backend.php |__custom.js |__ rocords-table.html
Learn Also –
Check Uncheck All checkboxes with a single checkbox using-jquery
Integrate Google ReCAPTCHA using PHP
Create Admin Panel Template in PHP
Let’s start its coding from the next steps –
1. Connect MySQL Database
First of all, connect the MYSQL database to PHP using the following SQL query in PHP
File Name – database.php
<?php
$hostName = "localhost";
$userName = "root";
$password = "";
$databaseName = "codingstatus";
$conn = new mysqli($hostName, $userName, $password, $databaseName);
// Check connection
if ($conn->connect_error) {
die("Connection failed: " . $conn->connect_error);
}
?>
2. Fetch and delete multiple records from the database
To fetch and delete multiple records from the database, you will have to implement the following points –
- First of all, You should include database.php to connect the database
- check checkedId (checkbox name of multiple records) and deleteAll (checkbox name of the select all ) are set or not using $_POST. If both are set then call the deleteMultipleData()
fetch_data() – This function accept a single parameters $conn returns all records from that database.
deleteMultipleData() – This function will execute when you check records & click the delete all button and then It will delete the checked records from the database. It mainly accepts two parameters $conn and $checkedId.
Fil Name – backend.php
<?php
include("database.php");
if(isset($_POST['checkedId']) && isset($_POST['deleteAll'])){
$checkedId = $_POST['checkedId'];
$deleteMsg=deleteMultipleData($conn, $checkedId);
}
$fetchData = fetch_data($conn);
function fetch_data($conn){
$query = "SELECT id, fullName, gender, email, city FROM developers ORDER BY id DESC";
$result = $conn->query($query);
if ($result->num_rows > 0) {
$row= mysqli_fetch_all($result, MYSQLI_ASSOC);
$data= $row;
} else {
$data= [];
}
return $data;
}
function deleteMultipleData($conn, $checkedId){
$checkedIdGroup = implode(',', $checkedId);
$query = "DELETE FROM developers WHERE id IN ($checkedIdGroup)";
$result = $conn->query($query);
if($result==true){
return "Selected data was deleted successfully";
}
}
?>
3. Display Multiple records with checkboxes
To display multiple records with checkboxes, you will have to implement the following steps –
- You have to include backend.php that contains code to delete & fetch data
- You must include the following jquery CDN to execute the jquery code
<script src="https://ajax.googleapis.com/ajax/libs/jquery/1.11.2/jquery.min.js"></script>
- Also, include the custom.js to add a check and uncheck records functionality
<script type="text/javascript" src="custom.js"></script>
- Create HTML table with checkboxes input and display records by fetching from the database
File Name – records-table.php
<?php
include("backend.php");
?>
<!DOCTYPE html>
<html>
<head>
<link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/4.5.2/css/bootstrap.min.css">
<script src="https://ajax.googleapis.com/ajax/libs/jquery/1.11.2/jquery.min.js"></script>
<script type="text/javascript" src="custom.js"></script>
</head>
<body>
<div class="container">
<div class="row">
<div class="col-sm-8">
<h3 class="text-danger text-center">Delete Multiple Records with Checkbox in PHP</h3>
<p><?php echo $deleteMsg??'';?></p>
<div class="table-responsive">
<form method="post" id="deleteForm">
<table class="table table-bordered table-striped ">
<thead><tr><th>S.N</th>
<th>Full Name</th>
<th>Gender</th>
<th>Email</th>
<th>City</th>
</thead>
<tbody class="checkbox-group">
<?php
if(count($fetchData)>0){
foreach($fetchData as $data){
?>
<tr>
<td><input type="checkbox" name="checkedId[]" value="<?php echo $data['id']??''?>"></td>
<td><?php echo $data['fullName']??''; ?></td>
<td><?php echo $data['gender']??''; ?></td>
<td><?php echo $data['email']??''; ?></td>
<td><?php echo $data['city']??''; ?></td>
</tr>
<?php
}}else{ ?>
<tr>
<td colspan="8">
<?php echo "No Data Found"; ?>
</td>
<tr>
<?php }?>
</tbody>
<?php
if(count($fetchData)>0){
?>
<tfoot>
<tr>
<td><input type="checkbox" id="singleCheckbox" ></td>
<td class="text-danger">Check All</td>
<td colspan="7"><input type="submit" name="deleteAll" value="Delete All" class="bg-danger text-light"></td>
</tr>
<tfoot>
<?php } ?>
</table>
</tfoot>
</div>
</div>
</div>
</div>
</body>
</html>
4. Select Multiple records with checkboxes
To select multiple records, you will have to implement the following points –
- Apply the submit event on id #deleteForm and call confirmDeleteData() function for confirmation to delete data.
- Also, create checkUncheck() function and implement the follwing steps within it and called it within $(document).ready().
- Assign an id “#singleCheckbox” to a variable #singleCheckbox
- Also, assign “.checkbox-group input[type=’checkbox’]” to another variable checkboxGroup
- Apply click event on singleCheckbox with the following condition
- If singleCheckbox is checked then all checkboxes will be checked otherwise all checkboxes will be unchecked
- If any single of checkbox group is unchecked then a single checkbox to check all will be unchecked
File Name – custom.js
$(document).ready(function(){
checkUncheck();
$("#deleteForm").on("submit", function(event){
confirmDeleteData(event);
});
});
function checkUncheck(){
const boxsingleCheck = '#singleCheckbox';
const checkboxGroup = ".checkbox-group input[type='checkbox']";
if($(document).find(boxsingleCheck).length!==0 || $(document).find(checkboxGroup).length!==0){
$(singleCheckbox).on('click',function(){
if(this.checked){
$(checkboxGroup).each(function(){
this.checked = true;
});
}else{
$(checkboxGroup).each(function(){
this.checked = false;
});
}
});
$(checkboxGroup).on('click',function(){
if($(checkboxGroup+':checked').length == $(checkboxGroup).length){
$(boxsingleCheck).prop('checked',true);
}else{
$(boxsingleCheck).prop('checked',false);
}
});
}
}
function confirmDeleteData(event){
if($(".checkbox-group input[type='checkbox']:checked").length > 0){
var conformation = confirm("Are you sure to delete selected data?");
if(conformation==false){
event.preventDefault();
}
}else{
alert('Check at least 1 record to delete.');
return false;
}
}