Delete multiple records from MySQL in PHP with checkbox

Posted 7 years ago By Ajay Gupta 102987

As we know, Its very time consuming to delete records one by one. Everyone wants to delete bulk record on single event only.

So here i will tell you how to delete bulk records from MySQL table in PHP with checkbox, you just need to select checkbox which one you want to delete and hit submit button to delete.

Its strongly recommended for large scale of data where its not possible to delete one by one.

Here in this tutorial, i will simple create a product table for demo with dummy records to test this code.

And with every row there will be one checkbox that hold the id of records in array.

There will be one checkbox with header too for select all records on single click.

When you are going to delete records, you will be asked for confirmation "Do you really want to delete records" when you click yes then selected records will be deleted from database table.

products table

In first step, create a products table in database and insert some dummy records after creating a table.

CREATE TABLE `products` (
    `id` int(11) NOT NULL AUTO_INCREMENT,
    `name` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
    `details` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
    `created_at` datetime NOT NULL,
    `updated_at` 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;

CREATE TABLE `products` (
    `id` int(11) NOT NULL AUTO_INCREMENT,
    `name` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
    `details` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
    `created_at` datetime NOT NULL,
    `updated_at` 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;

Now i have a products table with id, name, details, created_at, updated_At, status fields.

Database Configuration : db_config.php

Now i need to setup my database configuration in db_config.php file.

<?php
    $dbHost = 'localhost';  //database host name
    $dbUser = 'root';       //database username
    $dbPass = '';           //database password
    $dbName = 'expertphp_demo'; //database name
    $conn = mysqli_connect($dbHost,$dbUser,$dbPass,$dbName);
    if(!$conn){
        die("Database connection failed: " . mysqli_connect_error());
    }
?>

<?php
    $dbHost = 'localhost';  //database host name
    $dbUser = 'root';       //database username
    $dbPass = '';           //database password
    $dbName = 'expertphp_demo'; //database name
    $conn = mysqli_connect($dbHost,$dbUser,$dbPass,$dbName);
    if(!$conn){
        die("Database connection failed: " . mysqli_connect_error());
    }
?>

index.php

In this step, create a file where we list all records from database into html table but first include db_config.php for database access.

To work with js script, include jQuery library.

deleteConfirm() method is javascript function that is called on form submit to make sure you are really want to delete the product.

<!DOCTYPE html>
<html lang="en-US">
<head>
     <title>Delete multiple records from MySQL in PHP with checkbox - ExpertPHP</title>
   <link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.7/css/bootstrap.min.css">
    <script src="https://code.jquery.com/jquery-3.1.0.min.js"></script>
</head>
<body>
<?php
    session_start();
    include_once('db_config.php');
    $query = mysqli_query($conn,"SELECT * FROM products");
?>
    
<form name="actionForm" action="action.php" method="post" onsubmit="return deleteConfirm();"/>
    <table class="table-striped">
        <thead>
        <tr>
            <th><input type="checkbox" name="check_all" id="check_all" value=""/></th>        
            <th>Name</th>
            <th>Details</th>
        </tr>
        </thead>
        <?php
            if(mysqli_num_rows($query) > 0){
                while($row = mysqli_fetch_assoc($query)){
                    extract($row);
        ?>
        <tr>
            <td align="center"><input type="checkbox" name="selected_id[]" class="checkbox" value="<?php echo $id; ?>"/></td>        
            <td><?php echo $name; ?></td>
            <td><?php echo $details; ?></td>
           
        </tr> 
        <?php } }else{ ?>
            <tr><td colspan="3">No records found.</td></tr> 
        <?php } ?>
    </table>
    <input type="submit" class="btn btn-primary" name="btn_delete" value="Delete"/>
</form>
<script type="text/javascript">
function deleteConfirm(){
    var result = confirm("Do you really want to delete records?");
    if(result){
        return true;
    }else{
        return false;
    }
}
$(document).ready(function(){
    $('#check_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){
            $('#check_all').prop('checked',true);
        }else{
            $('#check_all').prop('checked',false);
        }
    });
});
</script>
</body>
</html>

<!DOCTYPE html>
<html lang="en-US">
<head>
 	<title>Delete multiple records from MySQL in PHP with checkbox - ExpertPHP</title>
   <link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.7/css/bootstrap.min.css">
	<script src="https://code.jquery.com/jquery-3.1.0.min.js"></script>
</head>

<body>
<?php
	session_start();
    include_once('db_config.php');
    $query = mysqli_query($conn,"SELECT * FROM products");
?>
	
<form name="actionForm" action="action.php" method="post" onsubmit="return deleteConfirm();"/>
    <table class="table-striped">
        <thead>
        <tr>
            <th><input type="checkbox" name="check_all" id="check_all" value=""/></th>        
            <th>Name</th>
            <th>Details</th>
        </tr>
        </thead>
        <?php
            if(mysqli_num_rows($query) > 0){
                while($row = mysqli_fetch_assoc($query)){
                	extract($row);
        ?>
        <tr>
            <td align="center"><input type="checkbox" name="selected_id[]" class="checkbox" value="<?php echo $id; ?>"/></td>        
            <td><?php echo $name; ?></td>
            <td><?php echo $details; ?></td>
           
        </tr> 
        <?php } }else{ ?>
            <tr><td colspan="3">No records found.</td></tr> 
        <?php } ?>
    </table>
    <input type="submit" class="btn btn-primary" name="btn_delete" value="Delete"/>
</form>
<script type="text/javascript">
function deleteConfirm(){
    var result = confirm("Do you really want to delete records?");
    if(result){
        return true;
    }else{
        return false;
    }
}
$(document).ready(function(){
    $('#check_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){
            $('#check_all').prop('checked',true);
        }else{
            $('#check_all').prop('checked',false);
        }
    });
});
</script>
</body>
</html>

action.php

In this step create a php file where we handle form data. when we click submit button then form will be submitted to their action page.

In this PHP file we will get all selected id of records that will be deleted from product table.

Once records are deleted then we store the message in session according to response to notify the user.

<?php
    session_start();
    include_once('db_config.php');
    if (count($_POST["selected_id"]) > 0 ) {
      $all = implode(",", $_POST["selected_id"]);
      $query="DELETE FROM products WHERE 1 AND id IN($all)";
      if(mysqli_query($conn,$query)){
          $_SESSION['success'] = 'Products have been deleted successfully.';
      }
    }else{
        $_SESSION['error'] = 'Select checkbox to delete product.';
    }
    header("Location:index.php");
?>

<?php
    session_start();
    include_once('db_config.php');
    if (count($_POST["selected_id"]) > 0 ) {
	  $all = implode(",", $_POST["selected_id"]);
	  $query="DELETE FROM products WHERE 1 AND id IN($all)";
	  if(mysqli_query($conn,$query)){
	  	$_SESSION['success'] = 'Products have been deleted successfully.';
	  }
	}else{
		$_SESSION['error'] = 'Select checkbox to delete product.';
	}
    header("Location:index.php");

?>

Now you can use these steps whenever you need to delete bulk records from MySQL table in PHP.

If you want to know how to submit form without page refresh using jQuery then click here : Submit PHP Bootstrap Form without Page Refresh using jQuery, Ajax

Delete multiple records from MySQL in PHP with checkbox

Delete multiple records from MySQL in PHP with checkbox

Tags :

Related Posts

Basic File Handling in PHP

What is the best IDE for PHP

What are Super Global Variables in PHP Example

What is cURL and how to POST Form Data and file with cURL with Example

What is PHP Language

How to get Folder or Directory size in PHP Laravel?

How Does PHP works and Embedding PHP in HTML

How to remove duplicate values from a multi-dimensional array in PHP

How to Remove Empty Array Elements and reindex in PHP Example?

How to access PHP variables in JavaScript or jQuery?

PHP code display in browser instead of executing it

How can I upload an image from a URL in PHP

How to set custom header authorization in PHP curl?

How To Replace Some Characters With Asterisks in PHP

How to Convert PHP Timestamp to Date Format with example

How to enable rewrite module in XAMPP, WAMP and Apache ?

How to get a random key from a PHP array

How to get filename without extension in PHP

Remove a string from the beginning of a string in PHP

What is $this Variable Vs self?

Add custom text to image in PHP with Example

PHP - Calculate experience in months, years and days from two given dates

PHP - How to get start date and end date from given week number and year

How to get file name, file size and sub directory from given directory

PHP - How to capture screenshot of website from URL with example

PHP : Upload multiple file using dropzone.js with drag and drop features

PHP script to send email from contact us form with jQuery Bootstrap validation

PHP image upload and resize it before uploading tutorial

PHP - How to create zip file and download multiple files using ZipArchive ?

PHP and MySQL - Social login and register with Google Account Example

How to convert PHP file to PDF file or create pdf file from webpage Using mpdf

Tips & tricks for text detection from images using Google Vision API in PHP

How to install or upgrade php 7.3 to 7.4 or downgrade php 7.4 to 7.3 ubuntu

PHP : Check if requested time is between two given time slots

Using JSON Schema to Validate Web Service Requests

MilesWeb Know The Hosting Types and Plans They Provide