Bootstrap DataTable Using PHP, Mysql, Ajax, Json with server side script

Abhigyan Singh 18th Jul 2020

In this tutorial we will learn about implementation of Bootstrap DataTable. As we know Bootstrap is a very popular open source project that provides us much option in web development. Bootstrap is just a CSS based framework that can easily be integrated with minimum customization.

In web development, we required to populate data in structured table manner that can easily be search, sort. So Bootstrap datatable provide such type of features.

(Also Read: Bootstrap Datatable With Add Edit Remove option In Php Mysql and Ajax)

Bootstrap DataTable is just like a table that can easily integrated with Bootstarp using Bootstrap table styling option. Here Data are presented in table format.  In this tutorial we use PHP server side script to get data from Mysql table and data will be populated by JSON in table.

This Bootstrap DataTable enables features like:

  • Pagination (With Previous, Next option and showing total count)
  • Searching (All Column wise Searching)
  • Sorting (All Column wise Sorting)

Explanation:

To implement Bootstrap, we need Bootstrap JS and CSS.

<!-- Latest compiled and minified CSS -->
<link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.4/css/bootstrap.min.css">

<!-- Latest compiled and minified JavaScript -->
<script src="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.4/js/bootstrap.min.js"></script>

To use Bootstrap DataTable, we need additional Js

<script type="text/javascript" language="javascript" src="js/jquery.dataTables.js"></script>

Now we need to create a Mysql Table from which data will be populated.

CREATE TABLE `tbl_datatable` (
`id` int(10) NOT NULL auto_increment,
`name` varchar(250) NOT NULL default '',
`email` varchar(250) NOT NULL default '',
`mobile` varchar(250) NOT NULL default '',
`start_date` timestamp DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (`id`)
);

index.html 

This is file where datatable will be displayed.

<!DOCTYPE html>
<html>
<head>
<meta charset="utf-8">

<meta name="viewport" content="initial-scale=1.0, maximum-scale=2.0">
<link rel="stylesheet" type="text/css" href="css/jquery.dataTables.css">
<script type="text/javascript" language="javascript" src="js/jquery.js"></script>
<script type="text/javascript" language="javascript" src="js/jquery.dataTables.js"></script>
<script type="text/javascript" language="javascript" class="init">

$(document).ready(function() {
$('#example').dataTable( {
"aProcessing": true,
"aServerSide": true,
"ajax": "server-response.php",
} );
} );

</script>
</head>

<body>
<table id="example" cellspacing="0" width="100%">
<thead>
<tr>
<th>First name</th>
<th>Email</th>
<th>Mobile</th>
<th>Start Date</th>
</tr>
</thead>
</table>

</body>
</html>

The jquery Code:

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

$(document).ready(function() {
$('#example').dataTable( {
"aProcessing": true,
"aServerSide": true,
"ajax": "server-response.php",
} );
} );

</script>

Now Server side Scripting Code start here.

<?php
/*
* Script: DataTables server-side script for PHP and MySQL
* Copyright: 2010 - Allan Jardine, 2012 - Chris Wright
* License: GPL v2 or BSD (3-point)
*/

/* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
* Easy set variables
*/

/* Array of database columns which should be read and sent back to DataTables. Use a space where
* you want to insert a non-database field (for example a counter or static image)
*/
$aColumns = array( 'name', 'email', 'mobile', 'start_date');

/* Indexed column (used for fast and accurate table cardinality) */
$sIndexColumn = "id";

/* DB table to use */
$sTable = "";

/* Database connection information */
$gaSql['user'] = "root";
$gaSql['password'] = "";
$gaSql['db'] = "";
$gaSql['server'] = "localhost";


/* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
* If you just want to use the basic configuration for DataTables with PHP server-side, there is
* no need to edit below this line
*/

/*
* Local functions
*/
function fatal_error ( $sErrorMessage = '' )
{
header( $_SERVER['SERVER_PROTOCOL'] .' 500 Internal Server Error' );
die( $sErrorMessage );
}


/*
* MySQL connection
*/
if ( ! $gaSql['link'] = mysql_pconnect( $gaSql['server'], $gaSql['user'], $gaSql['password'] ) )
{
fatal_error( 'Could not open connection to server' );
}

if ( ! mysql_select_db( $gaSql['db'], $gaSql['link'] ) )
{
fatal_error( 'Could not select database ' );
}


/*
* Paging
*/
$sLimit = "";
if ( isset( $_GET['iDisplayStart'] ) && $_GET['iDisplayLength'] != '-1' )
{
$sLimit = "LIMIT ".intval( $_GET['iDisplayStart'] ).", ".
intval( $_GET['iDisplayLength'] );
}


/*
* Ordering
*/
$sOrder = "";
if ( isset( $_GET['iSortCol_0'] ) )
{
$sOrder = "ORDER BY ";
for ( $i=0 ; $i<intval( $_GET['iSortingCols'] ) ; $i++ )
{
if ( $_GET[ 'bSortable_'.intval($_GET['iSortCol_'.$i]) ] == "true" )
{
$sOrder .= $aColumns[ intval( $_GET['iSortCol_'.$i] ) ]."
".($_GET['sSortDir_'.$i]==='asc' ? 'asc' : 'desc') .", ";
}
}

$sOrder = substr_replace( $sOrder, "", -2 );
if ( $sOrder == "ORDER BY" )
{
$sOrder = "";
}
}


/*
* Filtering
* NOTE this does not match the built-in DataTables filtering which does it
* word by word on any field. It's possible to do here, but concerned about efficiency
* on very large tables, and MySQL's regex functionality is very limited
*/
$sWhere = "";
if ( isset($_GET['sSearch']) && $_GET['sSearch'] != "" )
{
$sWhere = "WHERE (";
for ( $i=0 ; $i<count($aColumns) ; $i++ )
{
if ( isset($_GET['bSearchable_'.$i]) && $_GET['bSearchable_'.$i] == "true" )
{
$sWhere .= $aColumns[$i]." LIKE '%".mysql_real_escape_string( $_GET['sSearch'] )."%' OR ";
}
}
$sWhere = substr_replace( $sWhere, "", -3 );
$sWhere .= ')';
}

/* Individual column filtering */
for ( $i=0 ; $i<count($aColumns) ; $i++ )
{
if ( isset($_GET['bSearchable_'.$i]) && $_GET['bSearchable_'.$i] == "true" && $_GET['sSearch_'.$i] != '' )
{
if ( $sWhere == "" )
{
$sWhere = "WHERE ";
}
else
{
$sWhere .= " AND ";
}
$sWhere .= $aColumns[$i]." LIKE '%".mysql_real_escape_string($_GET['sSearch_'.$i])."%' ";
}
}


/*
* SQL queries
* Get data to display
*/
$sQuery = "
SELECT SQL_CALC_FOUND_ROWS ".str_replace(" , ", " ", implode(", ", $aColumns))."
FROM $sTable
$sWhere
$sOrder
$sLimit
";
$rResult = mysql_query( $sQuery, $gaSql['link'] ) or fatal_error( 'MySQL Error: ' . mysql_errno() );

/* Data set length after filtering */
$sQuery = "
SELECT FOUND_ROWS()
";
$rResultFilterTotal = mysql_query( $sQuery, $gaSql['link'] ) or fatal_error( 'MySQL Error: ' . mysql_errno() );
$aResultFilterTotal = mysql_fetch_array($rResultFilterTotal);
$iFilteredTotal = $aResultFilterTotal[0];

/* Total data set length */
$sQuery = "
SELECT COUNT(".$sIndexColumn.")
FROM $sTable
";
$rResultTotal = mysql_query( $sQuery, $gaSql['link'] ) or fatal_error( 'MySQL Error: ' . mysql_errno() );
$aResultTotal = mysql_fetch_array($rResultTotal);
$iTotal = $aResultTotal[0];


/*
* Output
*/
/*
$output = array(
"sEcho" => intval($_GET['sEcho']),
"iTotalRecords" => $iTotal,
"iTotalDisplayRecords" => $iFilteredTotal,
"aaData" => array()
);
*/

while ( $aRow = mysql_fetch_array( $rResult ) )
{
$row = array();
for ( $i=0 ; $i<count($aColumns) ; $i++ )
{
if ( $aColumns[$i] == "version" )
{
/* Special output formatting for 'version' column */
$row[] = ($aRow[ $aColumns[$i] ]=="0") ? '-' : $aRow[ $aColumns[$i] ];
}
else if ( $aColumns[$i] != ' ' )
{
/* General output */
$row[] = $aRow[ $aColumns[$i] ];
}
}
$output['aaData'][] = $row;
}

echo json_encode( $output );
?>

This tutorial is based on Bootstrap Datatable example

Authored By Abhigyan Singh

He is a continuous blogger and has blogged on different topic. He loves to surf Internet and always trying to get new Idea about new Technology and Innovations and sharing these great information to all the technology lovers.

Also on DiscussDesk