Show Hide jQuery DataTables Column with Server-side Processing with PHP Ajax

We have published many tutorial on jQuery DataTables with PHP Server-side processing using Ajax. But there are some viewers has requested us to publish tutorial on How can we show and hide jQuery DataTables columns with not loosing server-side process using Ajax with PHP. So for solve this problem, we have publish this tutorial, in which we will first load Mysql table dynamic data in jQuery DataTables plugin with server-side processing of searching, pagination of data using Ajax and then after we will make one select box with selection of multiple option with column name. So when we have select any column name name, then that column data will be hide from web page and when we have disselect any column name then that column data will be display on web page. So this show and hide column feature we will make for jQuery DataTable.

In this tutorial, for build show and hide DataTable column, we have use PHP script for backend operation and for front-end operation here we have use Ajax, jQuery DataTable plugin, Bootstrap library and Bootstrap select plugin. We can easily hide simple HTML table column by using CSS nth-child selector, but with DataTables it is not working properly. But if you search on DataTables site then it has already provide method for show or hide column. So by using that method we have make solution for dynamically show or hide jQuery DataTable column with PHP server-side processing using Ajax.

So here mainly we have use two main jQuery plugin for make show hide DataTable column tutorial, one is jQuery DataTable and another one is Bootstrap select plugin.

jQuery DataTable Plugin

jQuery DataTable plugin is a javascript library which has convert simple HTML table into multiple functional table with different feature like data search, pagination, sorting of data etc. This plugin has been used for load dynamic data in tabular format on web page. By using this plugin user can easily filter data and it will automatically make pagination without writing any line of code. So in this tutorial, we want to learn how to show or hide DataTable column dynamically.

Bootstrap Select Plugin

In this post we have use Bootstrap select plugin for select multiple column name for show or hide DataTable column dynamically. By using this plugin, we can convert simple select element into stylish select element with different feature like multi selection of option, search option text and much more. So here we want to make tutorial on show or hide multiple DataTable column dynamically, for this we have use this plugin under this tutorial for select multiple option at the same time for hide or show multiple DataTable column with server-side processing.

Here you can find complete source code for how to use Bootstrap select plugin with jQuery DataTable for make show or hide jQuery DataTable column dynamically. Here you can find how to load dynamic mysql table data in jQuery DataTable plugin and then after we will make simple select box and then after convert that select box into stylish select box by using Bootstrap select plugin. So when we have select column name then that column data will be hide from web page and when we have disselect selected column name then that column data will be visible on web page. So by using this feature we can display only that DataTable column data, which is required and we can hide othere unrequired DataTable column data from web page. Below you can find complete source code of show hide DataTable column data dynamically with server-side processing using PHP with Ajax.

View Demo

Source Code

--
-- Database: `testing`
--

-- --------------------------------------------------------

--
-- Table structure for table `customer_table`
--

CREATE TABLE `customer_table` (
  `customer_id` int(11) NOT NULL,
  `customer_first_name` varchar(200) NOT NULL,
  `customer_last_name` varchar(200) NOT NULL,
  `customer_email` varchar(300) NOT NULL,
  `customer_gender` varchar(30) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Indexes for dumped tables
--

--
-- Indexes for table `customer_table`
--
ALTER TABLE `customer_table`
  ADD PRIMARY KEY (`customer_id`);

--
-- AUTO_INCREMENT for dumped tables
--

--
-- AUTO_INCREMENT for table `customer_table`
--
ALTER TABLE `customer_table`
  MODIFY `customer_id` int(11) NOT NULL AUTO_INCREMENT;
COMMIT;

index.php

<html>
	<head>
		<title>How to Dynamically Display Hide DataTables Column with PHP Ajax</title>
		<!-- JS, Popper.js, and jQuery -->
		<script  src="https://code.jquery.com/jquery-3.5.1.js" integrity="sha256-QWo7LDvxbWT2tbbQ97B53yJnYU3WhH/C8ycbRAkjPDc=" crossorigin="anonymous"></script>
		<!-- CSS only -->
		<link rel="stylesheet" href="https://stackpath.bootstrapcdn.com/bootstrap/4.5.2/css/bootstrap.min.css" integrity="sha384-JcKb8q3iqJ61gNV9KGb8thSsNjpSL0n8PARn9HuZOnIxN0hoP+VmmDGMN5t9UJ0Z" crossorigin="anonymous">

		
		<script src="https://cdn.jsdelivr.net/npm/popper.js@1.16.1/dist/umd/popper.min.js" integrity="sha384-9/reFTGAW83EW2RDu2S0VKaIzap3H66lZH81PoYlFhbGU+6BZp6G7niu735Sk7lN" crossorigin="anonymous"></script>
		<script src="https://stackpath.bootstrapcdn.com/bootstrap/4.5.2/js/bootstrap.min.js" integrity="sha384-B4gt1jrGC7Jh4AgTPSdUtOBvfO8shuf57BaghqFfPlYxofvL8/KUEfYiJOMMV+rV" crossorigin="anonymous"></script>
		<script src="https://cdn.datatables.net/1.10.22/js/jquery.dataTables.min.js"></script>
		<script src="https://cdn.datatables.net/1.10.22/js/dataTables.bootstrap4.min.js"></script>  
		<link rel="stylesheet" href="https://cdn.datatables.net/1.10.22/css/dataTables.bootstrap4.min.css" />
		<link rel="stylesheet" href="https://cdn.jsdelivr.net/npm/bootstrap-select@1.13.14/dist/css/bootstrap-select.min.css">
  		<script src="https://cdn.jsdelivr.net/npm/bootstrap-select@1.13.14/dist/js/bootstrap-select.min.js"></script>
	</head>
	<body>
		<div class="container">
			<br />
			<h1 align="center" class="text-primary"><b>How to Dynamically Display Hide DataTables Column with PHP Ajax</b></h1>
			<br />
			<div class="card">
				<div class="card-header">
					<div class="row">
						<div class="col-lg-9">Sample Data</div>
						<div class="col-lg-3">
							<select name="column_name" id="column_name" class="form-control selectpicker" multiple>
								<option value="0">Customer ID</option>
						      	<option value="1">Customer First Name</option>
						      	<option value="2">Customer Last Name</option>
						      	<option value="3">Customer Email</option>
						      	<option value="4">Customer Gender</option>
							</select>
						</div>
					</div>
				</div>
				<div class="card-body">
					<div class="table-responsive">
						<table id="sample_data" class="table table-bordered table-striped">
							<thead>
								<tr>
									<th>Customer ID</th>
									<th>Customer First Name</th>
									<th>Customer Last Name</th>
									<th>Customer Email</th>
									<th>Customer Gender</th>
								</tr>
							</thead>
						</table>
					</div>
				</div>
			</div>
		</div>
		<br />
		<br />
	</body>
</html>

<script type="text/javascript" language="javascript">

$(document).ready(function(){
	
	var dataTable = $('#sample_data').DataTable({
		"processing" : true,
		"serverSide" : true,
		"order" : [],
		"ajax" : {
			url:"fetch.php",
			type:"POST"
		}
	});
	
	$('#column_name').selectpicker();

	$('#column_name').change(function(){

		var all_column = ["0", "1", "2", "3", "4"];

		var remove_column = $('#column_name').val();

		var remaining_column = all_column.filter(function(obj) { return remove_column.indexOf(obj) == -1; });

		dataTable.columns(remove_column).visible(false);

		dataTable.columns(remaining_column).visible(true);

	});

});	
</script>

fetch.php

<?php

//fetch.php

$connect = new PDO("mysql:host=localhost;dbname=testing", "root", "");

$column = array("customer_id", "customer_first_name", "customer_last_name", "customer_email", "customer_gender");

$query = "SELECT * FROM customer_table ";

if(isset($_POST["search"]["value"]))
{
	$query .= '
	WHERE customer_id LIKE "%'.$_POST["search"]["value"].'%" 
	OR customer_first_name LIKE "%'.$_POST["search"]["value"].'%" 
	OR customer_last_name LIKE "%'.$_POST["search"]["value"].'%" 
	OR customer_email LIKE "%'.$_POST["search"]["value"].'%" 
	OR customer_gender LIKE "%'.$_POST["search"]["value"].'%" 
	';
}

if(isset($_POST["order"]))
{
	$query .= 'ORDER BY '.$column[$_POST['order']['0']['column']].' '.$_POST['order']['0']['dir'].' ';
}
else
{
	$query .= 'ORDER BY customer_id DESC ';
}

$query1 = '';

if($_POST["length"] != -1)
{
	$query1 = 'LIMIT ' . $_POST['start'] . ', ' . $_POST['length'];
}

$statement = $connect->prepare($query);

$statement->execute();

$number_filter_row = $statement->rowCount();

$result = $connect->query($query . $query1);

$data = array();

foreach($result as $row)
{
	$sub_array = array();
	$sub_array[] = $row['customer_id'];
	$sub_array[] = $row['customer_first_name'];
	$sub_array[] = $row['customer_last_name'];
	$sub_array[] = $row['customer_email'];
	$sub_array[] = $row['customer_gender'];
	$data[] = $sub_array;
}

function count_all_data($connect)
{
	$query = "SELECT * FROM customer_table";

	$statement = $connect->prepare($query);

	$statement->execute();

	return $statement->rowCount();
}

$output = array(
	"draw"		=>	intval($_POST["draw"]),
	"recordsTotal"	=>	count_all_data($connect),
	"recordsFiltered"	=>	$number_filter_row,
	"data"	=>	$data
);

echo json_encode($output);

?>