Skip to main content

Php Datatables Delete Multiple Selected Rows Tutorial

Hi Dev,

In this blog, I will explain you how to delete multiple selected records useing datatables in php. We will show delete multiple selected records in php. you can easy delete multiple selected records useing datatables in php. we will show datatables delete multiple selected rows example in php.

I will give fulll example of php delete multiple selected records in datatables.

Step 1: Cretae Table

In this step, i will create table in mysql database.


CREATE TABLE `employee` (
  `id` int(11) NOT NULL PRIMARY KEY AUTO_INCREMENT,
  `emp_name` varchar(80) NOT NULL,
  `salary` varchar(20) NOT NULL,
  `gender` varchar(10) NOT NULL,
  `city` varchar(80) NOT NULL,
  `email` varchar(80) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
Step 1:Configuration

In this step, I will create php to mysql datatables configuration.

config.php

<?php

$host = "localhost"; /* Host name */
$user = "root"; /* User */
$password = "root"; /* Password */
$dbname = "php_tutorial"; /* Database name */

$con = mysqli_connect($host, $user, $password,$dbname);
// Check connection
if (!$con) {
 die("Connection failed: " . mysqli_connect_error());
}
Step 2: HTML

In this step,Create a <table id='empTable' class='display dataTable' >. Add header row.

In the last cell add check all checkbox and a button for deleting checked records.


<!doctype html>
<html>
    <head>
        <title>Delete multiple selected records in DataTables – PHP</title>

        <meta name="viewport" content="width=device-width, initial-scale=1.0">
        
        <!-- Datatable CSS -->
        <link href='//cdn.datatables.net/1.10.19/css/jquery.dataTables.min.css' rel='stylesheet' type='text/css'>

        <!-- jQuery Library -->
        <script src="https://ajax.googleapis.com/ajax/libs/jquery/3.3.1/jquery.min.js"></script>

        <!-- Datatable JS -->
        <script src="//cdn.datatables.net/1.10.19/js/jquery.dataTables.min.js"></script>
        
        <!-- Bootstrap  Cs-->
        <link rel="stylesheet" href="https://cdnjs.cloudflare.com/ajax/libs/twitter-bootstrap/5.0.0-alpha1/css/bootstrap.min.css">
    </head>
    <body class="bg-dark">
        <div class="container">
          <div class="row">
              <div class="col-md-10 offset-md-1">
                  <div class="card mt-1">
                      <div class="card-header">
                          <h5>PHP Delete multiple selected records in DataTables – nicesnippets.com</h5>
                      </div>
                      <div class="card-body">
                            <!-- Table -->
                        <table id='empTable' class='display dataTable table table table-hover'>
                            <thead>
                            <tr>
                                <th>Employee name</th>
                                <th>Email</th>
                                <th>Gender</th>
                                <th>Salary</th>
                                <th>City</th>
                                <th>Check All <input type="checkbox" class='checkall' id='checkall'><input type="button" id='delete_record' class="btn btn-sm ml-3 btn-danger" value='Delete' ></th>
                            </tr>
                            </thead>
                        </table>
                      </div>
                  </div>
              </div>
          </div>
        </div>
    </body>
</html>
Step 3: Script

->DataTable Initialization –

Initialize DataTable on <table id='empTable'>.

Add 'processing': true, 'serverSide': true, 'serverMethod': 'post'. Specify AJAX url, and data using 'ajax' option.

Using 'columns' option to specify field names which need to be read from AJAX response.

Remove sorting from the last column using columnDefs option.

->Check All –

If checkall checkbox is clicked then check it is checked or not. If checked then set all checkboxes checked by class='delete_check'.

If not checked then remove checked from all checkboxes.

>->Checkbox checked –

Create a checkcheckbox() function.

Count total checkboxes and checked checkboxes with class='delete_check' on the page.

If total checkboxes equal to total checked checkboxes then check the checkall checkbox otherwise uncheck the checkbox.

->Delete button –

Read all checked checkboxes by class='delete_check' and push value in deleteids_arr Array.

If deleteids_arr Array length is greater then display confirm alert. Send AJAX POST request to ‘ajaxfile.php’ for deleting

records when ok button gets clicked.

Pass request: 2, deleteids_arr: deleteids_arr as data.

On successful callback reload the Datatable by calling

dataTable.ajax.reload(). 

var dataTable;
$(document).ready(function(){

   // Initialize datatable
   dataTable = $('#empTable').DataTable({
     'processing': true,
     'serverSide': true,
     'serverMethod': 'post',
     'ajax': {
        'url':'ajaxfile.php',
        'data': function(data){
           
           data.request = 1;

        }
     },
     'columns': [
        { data: 'emp_name' },
        { data: 'email' },
        { data: 'gender' },
        { data: 'salary' },
        { data: 'city' },
        { data: 'action' },
     ],
     'columnDefs': [ {
       'targets': [5], // column index (start from 0)
       'orderable': false, // set orderable false for selected columns
     }]
   });

   // Check all 
   $('#checkall').click(function(){
      if($(this).is(':checked')){
         $('.delete_check').prop('checked', true);
      }else{
         $('.delete_check').prop('checked', false);
      }
   });

   // Delete record
   $('#delete_record').click(function(){

      var deleteids_arr = [];
      // Read all checked checkboxes
      $("input:checkbox[class=delete_check]:checked").each(function () {
         deleteids_arr.push($(this).val());
      });

      // Check checkbox checked or not
      if(deleteids_arr.length > 0){

         // Confirm alert
         var confirmdelete = confirm("Do you really want to Delete records?");
         if (confirmdelete == true) {
            $.ajax({
               url: 'ajaxfile.php',
               type: 'post',
               data: {request: 2,deleteids_arr: deleteids_arr},
               success: function(response){
                  dataTable.ajax.reload();
               }
            });
         } 
      }
   });

});

// Checkbox checked
function checkcheckbox(){

   // Total checkboxes
   var length = $('.delete_check').length;

   // Total checked checkboxes
   var totalchecked = 0;
   $('.delete_check').each(function(){
      if($(this).is(':checked')){
         totalchecked+=1;
      }
   });

   // Checked unchecked checkbox
   if(totalchecked == length){
      $("#checkall").prop('checked', true);
   }else{
      $('#checkall').prop('checked', false);
   }
}
step 4: PHP

In this step, I will create ajaxfile.php file

->AJAX requests –

If $request == 1 then read DataTables POST values. If $searchQuery is not empty then prepare search query. Count total records without and with filter.

Fetch records from employee table where specify search query, order by and limit.

Loop on the fetched records and initialize $data Array with the keys specified in the columns option while dataTable initializing.

Assign checkbox in ‘action’ key. In the checkbox added onclick=‘checkcheckbox()’, pass $row[‘id’] in value attribute and class=‘delete_check’.

Assign required keys with values in $response Array and return in JSON format.

If $request == 2 then loop on the $deleteids_arr Array and execute DELETE query on the id.


<?php
include 'config.php';

$request = $_POST['request'];

// Datatable data
if($request == 1){
   ## Read value
   $draw = $_POST['draw'];
   $row = $_POST['start'];
   $rowperpage = $_POST['length']; // Rows display per page
   $columnIndex = $_POST['order'][0]['column']; // Column index
   $columnName = $_POST['columns'][$columnIndex]['data']; // Column name
   $columnSortOrder = $_POST['order'][0]['dir']; // asc or desc
   $searchValue = $_POST['search']['value']; // Search value

   ## Search 
   $searchQuery = " ";
   if($searchValue != ''){
      $searchQuery .= " and (emp_name like '%".$searchValue."%' or 
                        email like '%".$searchValue."%' or 
                        city like'%".$searchValue."%' ) ";
   }

   ## Total number of records without filtering
   $sel = mysqli_query($con,"select count(*) as allcount from employee");
   $records = mysqli_fetch_assoc($sel);
   $totalRecords = $records['allcount'];

   ## Total number of records with filtering
   $sel = mysqli_query($con,"select count(*) as allcount from employee WHERE 1 ".$searchQuery);
   $records = mysqli_fetch_assoc($sel);
   $totalRecordwithFilter = $records['allcount'];

   ## Fetch records
   $empQuery = "select * from employee WHERE 1 ".$searchQuery." order by ".$columnName." ".$columnSortOrder." limit ".$row.",".$rowperpage;
   $empRecords = mysqli_query($con, $empQuery);
   $data = array();

   while ($row = mysqli_fetch_assoc($empRecords)) {
      $data[] = array(
         "emp_name"=>$row['emp_name'],
         "email"=>$row['email'],
         "gender"=>$row['gender'],
         "salary"=>$row['salary'],
         "city"=>$row['city'],
         "action"=>"<input type='checkbox' class='delete_check' id='delcheck_".$row['id']."' onclick='checkcheckbox();' value='".$row['id']."'>"
      );
   }
   ## Response
   $response = array(
      "draw" => intval($draw),
      "iTotalRecords" => $totalRecords,
      "iTotalDisplayRecords" => $totalRecordwithFilter,
      "aaData" => $data
   );

   echo json_encode($response);
   exit;
}
// Delete record
if($request == 2){
   $deleteids_arr = $_POST['deleteids_arr'];

   foreach($deleteids_arr as $deleteid){
      mysqli_query($con,"DELETE FROM employee WHERE id=".$deleteid);
   }

   echo 1;
   exit;
}

It will help you...

Labels:

Comments

Post a Comment

Popular posts from this blog

Laravel Scout Algolia Full Text Search Example

Hi Guys Today,I will tell how you can full text search utilizing scout algolia. i will show the example of laravel scout algolia full text search.you can full text search utilizing scout algolia api.it can this example full expound scout algolia full text search. I will show the rudimental step of scout algolia api full text search.if you can utilize full text search for install scout and Algolia api package.we are utilizing algolia api utilizing full text search example in laravel. Here the following steps example laravel full text search Using scout algolia Step 1: Create Laravel Project In this step create laravel project following command. composer create-project --prefer-dist laravel/blog Step 2: Database Configuration After create laravel project , we require to make database configuration, you have to add following details on your .env file. 1.Database Username 1.Database Password 1.Database Name In .env file also available host and port details, you can configu
1 comment
Read more

Laravel IP Address Using Get Location Example

Hi Dev, Today,I will learn you how to get location useing ip address in laravel. we will show example of laravel ip address using get location. you can easy to get location useing ip address in laravel.In this example, I will useing stevebauman/location packege get location useing ip address in laravel. Many time you will need to get visitor's details for security, spam prevention etc. It's very easy to get visitor ip address and their location in PHP Laravel. Step 1: Install stevebauman/location Now, We will install stevebauman/location package using below command.Require this package with composer. It is recommended to only require the package for development. composer require stevebauman/location Step 2: Add providers and aliases In this step,We will add below providers and aliases in the "config/app.php" file. config/app.php 'providers' => [ .... Stevebauman\Location\LocationServiceProvider::class, ], 'aliases' => [ .... 'Loca
Post a Comment
Read more

Laravel 6 validation required if another field is empty

Hii guys, In this artical, i will give you example of laravel 6 in validation required if another field is empty. We know laravel provide several in-built laravel 6 validation required_without . If you need to add validation rules like required if other field is empty in laravel then you can do it using required_without. I am going to explain you, If you can not enter test (field) value at that time test1 (field) is required. So at that time you can add validation required_without. So, you can use this way: "test1" =>"required_without:test" Example: public function store(Request $request) { $request->validate([ "test" =>"required", "test1" =>"required_without:test" ]); dd("Done!"); } If return validation error otherwise show Done!. It will help you...
Post a Comment
Read more