Dynamically Show Hide columns in DataTable AJAX pagination

Sometimes require to show and hide HTML table columns dynamically.

You can easily hide a column using CSS nth-child selector. But with dataTables it not works properly.

DataTables already provided a method for this.

In this tutorial, I show how you can dynamically show and hide multiple columns in DataTables.

Dynamically Show Hide columns in DataTable AJAX pagination


Contents

  1. Table structure
  2. Configuration
  3. Download & Include
  4. HTML
  5. Script
  6. PHP
  7. Demo
  8. Conclusion

1. Table structure

Create employee table.

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;

2. Configuration

Create a config.php for the database connection.

Completed Code

<?php

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

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

3. Download & Include

  • Download Datatables from here.
  • Include datatables.min.css and datatables.min.js in <head> section and also include the jQuery Library.
  • You can also use CDN.
<!-- 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.4.1/jquery.min.js"></script>

<!-- Datatable JS -->
<script src="//cdn.datatables.net/1.10.19/js/jquery.dataTables.min.js"></script>

4. HTML

Created 5 checkboxes and a button for Show/Hide multiple columns.

Assign column index in the checkbox value attribute.

The DataTables column indexing start from 0. In the example, 0 => “Employee Name”, 1 => “Email”,2 => “Gender”,3 => “Salary”, and 4 => “Email”.

Create <table id='empTable'> to initialize dataTables.

Completed Code

<div >
  <div>
    <span>Show/Hide Columns</span><br>
    <input type="checkbox" name='hide_columns[]' value='0'> Employee 
    <input type="checkbox" name='hide_columns[]' value='1'> Email 
    <input type="checkbox" name='hide_columns[]' value='2'> Gender 
    <input type="checkbox" name='hide_columns[]' value='3'> Salary 
    <input type="checkbox" name='hide_columns[]' value='4'> City

    <input type="button" id="but_showhide" value='Show/Hide'>
  </div>


  <!-- Table -->
  <table id='empTable' class='display dataTable'>
    <thead>
       <tr>
         <th>Employee name</th>
         <th>Email</th>
         <th>Gender</th>
         <th>Salary</th>
         <th>City</th>
       </tr>
    </thead>

  </table>
</div>

5. Script

Initialize DataTable on <table id='empTable'> and assign instance in empDataTable variable.

Set options – 'processing': true, 'serverSide': true, 'serverMethod': 'post'. Set AJAX url to 'ajaxfile.php'.

With 'columns' option specifies key names that need to read from AJAX response.

Show and Hide columns –

Use columns() and visible() method to show and hide columns.

Syntax – 

  • Single hide – 
datatable-instance.column( column-index ).visible(false);
  • Single show – 
datatable-instance.column( column-index ).visible(true);
  • Multiple hides – 
datatable-instance.column( [ comm-separated-column-indexes ] ).visible(false);
  • Multiple shows – 
datatable-instance.column( [ comm-separated-column-indexes ] ).visible(true);

Bind click event on #but_showhide button.

Declare two Array variables – checked_arr and unchecked_arr.

Loop on all checked and unchecked checkboxes. Initialize checked_arr with checked checkbox value and initialize unchecked_arr with unchecked checkbox value.

Pass checked_arr on empDataTable.columns( checked_arr ) and set visible to false. Similarly, pass unchecked_arr on empDataTable.columns( unchecked_arr ) and set visible to true.

NOTE – If you want to hide a single column at a time then just pass the column index instead of Array in columns(). ExampleempDataTable.columns(0).visible(false); hiding 1st column.

Completed Code

$(document).ready(function(){

   // Initialize DataTable
   var empDataTable = $('#empTable').DataTable({
     'processing': true,
     'serverSide': true,
     'serverMethod': 'post',
     'ajax': {
        'url':'ajaxfile.php'
     },
     'columns': [
        { data: 'emp_name' },
        { data: 'email' },
        { data: 'gender' },
        { data: 'salary' },
        { data: 'city' }, 
     ]
  });
  
  // Hide & show columns
  $('#but_showhide').click(function(){
     var checked_arr = [];var unchecked_arr = [];

     // Read all checked checkboxes
     $.each($('input[type="checkbox"]:checked'), function (key, value) {
        checked_arr.push(this.value);
     });

     // Read all unchecked checkboxes
     $.each($('input[type="checkbox"]:not(:checked)'), function (key, value) {
        unchecked_arr.push(this.value);
     });

     // Hide the checked columns
     empDataTable.columns(checked_arr).visible(false);

     // Show the unchecked columns
     empDataTable.columns(unchecked_arr).visible(true);
  });
});

6. PHP

Create an ajaxfile.php file.

Read DataTables POST values and assign in variables.

If $searchValue is not empty then prepares the search filter query. Use $searchValue to search on emp_name, email, and city fields.

Count total records with and without a search filter from employee table.

Fetch records from employee table where pass $searchQuery in WHERE clause and specify ORDER BY and LIMIT.

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

Completed Code

<?php
## Database configuration
include 'config.php';

## 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 = mysqli_real_escape_string($con,$_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 record 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']
   );
}

## Response
$response = array(
  "draw" => intval($draw),
  "iTotalRecords" => $totalRecords,
  "iTotalDisplayRecords" => $totalRecordwithFilter,
  "aaData" => $data
);

echo json_encode($response);

7. Demo

View Demo


8. Conclusion

To hide and show columns use columns() and visible() method. Call it on dataTables instance and pass column index in columns() method and false to visible() method.

Similarly, pass true to visible() if you want to show the columns.

If you found this tutorial helpful then don't forget to share.
Are you want to get implementation help, or modify or extend the functionality of this script? Submit paid service request.

1 thought on “Dynamically Show Hide columns in DataTable AJAX pagination”

Leave a Comment