How to Delete Multiple Records from Database using PHP

How to Delete Multiple Records from Database using PHP

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>