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.
Contents
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.cssanddatatables.min.jsin<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(). Example –empDataTable.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
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.
