Delete multiple records from MySQL in PHP with checkbox

Leave a Comment / php / By

Delete multiple records from MySQL in PHP with checkbox

In this post we will give you information about Delete multiple records from MySQL in PHP with checkbox. Hear we will give you detail about Delete multiple records from MySQL in PHP with checkboxAnd how to use it also give you demo for it if it is necessary.

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.

  1. CREATETABLE'products'(
  2. 'id'int(11)NOTNULLAUTO_INCREMENT,
  3. 'name'varchar(255) COLLATE utf8_unicode_ci NOTNULL,
  4. 'details'varchar(255) COLLATE utf8_unicode_ci NOTNULL,
  5. 'created_at'datetimeNOTNULL,
  6. 'updated_at'datetimeNOTNULL,
  7. 'status'tinyint(1)NOTNULLDEFAULT'1' COMMENT '1=Active, 0=Deactive',
  8. PRIMARYKEY('id')
  9. ) 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.

  1. <?php
  2. $dbHost='localhost';//database host name
  3. $dbUser='root';//database username
  4. $dbPass='';//database password
  5. $dbName='onlinecode_demo';//database name
  6. $conn=mysqli_connect($dbHost,$dbUser,$dbPass,$dbName);
  7. if(!$conn){
  8. die("Database connection failed: ".mysqli_connect_error());
  9. }
  10. ?>
<?php
    $dbHost = 'localhost';  //database host name
    $dbUser = 'root';       //database username
    $dbPass = '';           //database password
    $dbName = 'onlinecode_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.

  1. <!DOCTYPEhtml>
  2. <htmllang="en-US">
  3. <head>
  4.      <title>Delete multiple records from MySQL in PHP with checkbox - onlinecode</title>
  5. <linkrel="stylesheet"href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.7/css/bootstrap.min.css">
  6.     <scriptsrc="https://code.jquery.com/jquery-3.1.0.min.js"></script>
  7. </head>
  9. <body>
  10. <?php
  11.     session_start();
  12.     include_once('db_config.php');
  13.     $query = mysqli_query($conn,"SELECT * FROM products");
  14. ?>
  15.     
  16. <formname="actionForm"action="action.php"method="post"onsubmit="return deleteConfirm();"/>
  17. <tableclass="table-striped">
  18. <thead>
  19. <tr>
  20. <th><inputtype="checkbox"name="check_all"id="check_all"value=""/></th>
  21. <th>Name</th>
  22. <th>Details</th>
  23. </tr>
  24. </thead>
  25.         <?php
  26.             if(mysqli_num_rows($query) > 0){
  27.                 while($row = mysqli_fetch_assoc($query)){
  28.                     extract($row);
  29.         ?>
  30. <tr>
  31. <tdalign="center"><inputtype="checkbox"name="selected_id[]"class="checkbox"value="<?php echo $id; ?>"/></td>
  32. <td><?php echo $name; ?></td>
  33. <td><?php echo $details; ?></td>
  35. </tr>
  36.         <?php } }else{ ?>
  37. <tr><tdcolspan="3">No records found.</td></tr>
  38.         <?php } ?>
  39. </table>
  40. <inputtype="submit"class="btn btn-primary"name="btn_delete"value="Delete"/>
  41. </form>
  42. <scripttype="text/javascript">
  43. function deleteConfirm(){
  44.     var result = confirm("Do you really want to delete records?");
  45.     if(result){
  46.         return true;
  47.     }else{
  48.         return false;
  49.     }
  50. }
  51. $(document).ready(function(){
  52.     $('#check_all').on('click',function(){
  53.         if(this.checked){
  54.             $('.checkbox').each(function(){
  55.                 this.checked = true;
  56.             });
  57.         }else{
  58.              $('.checkbox').each(function(){
  59.                 this.checked = false;
  60.             });
  61.         }
  62.     });
  64.     $('.checkbox').on('click',function(){
  65.         if($('.checkbox:checked').length == $('.checkbox').length){
  66.             $('#check_all').prop('checked',true);
  67.         }else{
  68.             $('#check_all').prop('checked',false);
  69.         }
  70.     });
  71. });
  72. </script>
  73. </body>
  74. </html>
<!DOCTYPE html>
<html lang="en-US">
<head>
 	<title>Delete multiple records from MySQL in PHP with checkbox - onlinecode</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 >
        <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[]"  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"  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.

  1. <?php
  2. session_start();
  3. include_once('db_config.php');
  4. if(count($_POST["selected_id"])>){
  5.       $all=implode(",",$_POST["selected_id"]);
  6.       $query="DELETE FROM products WHERE 1 AND id IN($all)";
  7.       if(mysqli_query($conn,$query)){
  8.           $_SESSION['success']='Products have been deleted successfully.';
  9.       }
  10.     }else{
  11.         $_SESSION['error']='Select checkbox to delete product.';
  12.     }
  13. header("Location:index.php");
  15. ?>
<?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

Hope this code and post will helped you for implement Delete multiple records from MySQL in PHP with checkbox. if you need any help or any feedback give it in comment section or you have good idea about this post you can give it comment section. Your comment will help us for help you more and improve us. we will give you this type of more interesting post in featured also so, For more interesting post and code Keep reading our blogs

For More Info See :: laravel And github

Leave a Comment

Your email address will not be published. Required fields are marked *

70  +    =  71

We're accepting well-written guest posts and this is a great opportunity to collaborate : Contact US