In this article, you will learn How to Delete Multiple Records from Database using PHP.
Example:-
SQL Table
CREATE TABLE `users` (
`id` int(10) NOT NULL,
`name` varchar(140) DEFAULT NULL,
`education` varchar(120) DEFAULT NULL,
`doj` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
ALTER TABLE `users`
ADD PRIMARY KEY (`id`);
ALTER TABLE `users`
MODIFY `id` int(10) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=1;
Fetch data from the database and create a checkbox .
<html>
<head>
<title>Delete Multiple Records</title>
</head>
<body>
<form name="multipledeletion" method="post">
<div class="container">
<div class="row col-md-6 col-md-offset-2 custyle">
<h2>How to Delete Multiple Records from Database using PHP</h2>
<!-- Message -->
<p style="color:red; font-size:16px;">
<?php if($errmsg){ echo $errmsg; } ?> </p>
<table class="table table-striped custab">
<!-- Deletion Button -->
<tr>
<td colspan="4"> <input type="submit" name="submit" value="Delete" class="btn btn-primary btn-md pull-left" onClick="return confirm('Are you sure you want to delete?');" ></td>
</tr>
<tr>
<th>
<!-- For Selecting All -->
<li><input type="checkbox" id="select_all" /> Select all</li></th>
<th>Name</th>
<th>Education </th>
<th>Date</th>
</tr>
<?php
$query=mysqli_query($con,"select * from users");
$totalcnt = mysqli_num_rows($query);
if ($totalcnt > 0) {
while ($row=mysqli_fetch_array($query)) {
?>
<tr>
<td><input type="checkbox" class="checkbox" name="ids[]" value="<?php echo htmlentities($row['id']);?>"/></td>
<td><?php echo htmlentities($row['name']);?></td>
<td><?php echo htmlentities($row['education']);?></td>
<td><?php echo htmlentities($row['doj']);?></td>
</tr>
<?php } } else { ?>
<tr>
<td colspan="4"> No Record Found</td>
</tr>
<?php } ?>
</table>
</div>
</div>
</form>
</body>
</html>
Code of Submit
if (isset($_POST["submit"])) {
if (count($_POST["ids"]) > 0 ) {
$all = implode(",", $_POST["ids"]);
$sql =mysqli_query($con,"DELETE FROM users WHERE id in ($all)");
if ($sql) {
$errmsg ="Data has been deleted successfully";
} else {
$errmsg ="Error while deleting. Please Try again.";
}
} else {
$errmsg = "You need to select atleast one checkbox to delete!";
}
}
Complete Code
<?php
include('dbconfig.php');
error_reporting(0);
if (isset($_POST["submit"])) {
if (count($_POST["ids"]) > 0 ) {
// Imploding checkbox ids
$all = implode(",", $_POST["ids"]);
$sql =mysqli_query($con,"DELETE FROM users WHERE id in ($all)");
if ($sql) {
$errmsg ="Data has been deleted successfully";
} else {
$errmsg ="Error while deleting. Please Try again.";
}
} else {
$errmsg = "You need to select atleast one checkbox to delete!";
}
}
?>
<!DOCTYPE html>
<html>
<head>
<meta name="author" content="Anuj Kumar">
<title>How to Delete Multiple Records from Database using PHP</title>
<link href="//netdna.bootstrapcdn.com/bootstrap/3.0.0/css/bootstrap.min.css" rel="stylesheet" id="bootstrap-css">
<script src="//netdna.bootstrapcdn.com/bootstrap/3.0.0/js/bootstrap.min.js"></script>
<script src="//code.jquery.com/jquery-1.11.1.min.js"></script>
<script src="https://ajax.googleapis.com/ajax/libs/jquery/1.11.2/jquery.min.js"></script>
<style type="text/css">
.custab{
border: 1px solid #ccc;
padding: 5px;
margin: 5% 0;
box-shadow: 3px 3px 2px #ccc;
transition: 0.5s;
}
.custab:hover{
box-shadow: 3px 3px 0px transparent;
transition: 0.5s;
}
li {
list-style-type: none;
}
</style>
</head>
<body>
<form name="multipledeletion" method="post">
<div class="container">
<div class="row col-md-6 col-md-offset-2 custyle">
<h2>How to Delete Multiple Records from Database using PHP</h2>
<!-- Message -->
<p style="color:red; font-size:16px;">
<?php if($errmsg){ echo $errmsg; } ?> </p>
<table class="table table-striped custab">
<!-- Deletion Button -->
<tr>
<td colspan="4"> <input type="submit" name="submit" value="Delete" class="btn btn-primary btn-md pull-left" onClick="return confirm('Are you sure you want to delete?');" ></td>
</tr>
<tr>
<th>
<!-- For Selecting All -->
<li><input type="checkbox" id="select_all" /> Select all</li></th>
<th>Name</th>
<th>Education </th>
<th>Date</th>
</tr>
<?php
$query=mysqli_query($con,"select * from users");
$totalcnt = mysqli_num_rows($query);
if ($totalcnt > 0) {
while ($row=mysqli_fetch_array($query)) {
?>
<tr>
<td><input type="checkbox" class="checkbox" name="ids[]" value="<?php echo htmlentities($row['id']);?>"/></td>
<td><?php echo htmlentities($row['name']);?></td>
<td><?php echo htmlentities($row['education']);?></td>
<td><?php echo htmlentities($row['doj']);?></td>
</tr>
<?php } } else { ?>
<tr>
<td colspan="4"> No Record Found</td>
</tr>
<?php } ?>
</table>
</div>
</div>
</form>
<script type="text/javascript">
$(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);
}
});
});
</script>
</body>
</html>