• Home
  • PHP
  • MySQL
  • Demos
  • HTML
  • CSS
  • jQuery
  • Framework
  • Social
  • Request Tutorial
PHP Lift
  • Home
  • Demos
  • Advertisement
PHP Lift
  • Home
  • PHP
  • MySQL
  • Demos
  • HTML
  • CSS
  • jQuery
  • Framework
  • Social
  • Request Tutorial
  • Follow
    • Facebook
    • Twitter
    • Google+
    • Pinterest
    • Youtube
    • Instagram
    • RSS
DataTables Server-side Processing with PHP and MySQL
Home
MySQL

DataTables Server-side Processing with PHP and MySQL

February 24th, 2022 Huzoor Bux Guide, jQuery, MySQL, PHP 0 comments

Facebook Twitter Google+ LinkedIn Pinterest

DataTables is a library of jQuery that displays the list records in an HTML table using an intuitive interface. It offers features such as search, pagination, and sort.

In an earlier article, we saw a custom code that allows search and pagination of the records. In addition, DataTables will enable us to limit the number of records displayed on a page.

DataTables extension supports both client-side as well as server-side processing. This article will show you the database results using DataTables server-side processing.

A PHP file calls domain class by sending the table, column, and configuration details to the function. The domain class executes the query and returns an array of results. The JSON format encodes the resultant array and will be sent as a response to the DataTables AJAX program.

DEMO
DOWNLOAD CODE

Read Also: How to integrate jQuery Fullcalendar with PHP and MySQL

DataTable Defining Code

This code defines HTML code for DataTable. This code requires CSS and JavaScript libraries. The table HTML tag contains an id attribute used to define and assign the table as a DataTable.

<!-- DataTable CSS -->
<link rel="stylesheet" href="https://cdnjs.cloudflare.com/ajax/libs/datatables/1.10.21/css/jquery.dataTables.min.css" />

<!-- DataTable Script -->
<script src="https://cdnjs.cloudflare.com/ajax/libs/datatables/1.10.21/js/jquery.dataTables.min.js" ></script>

Table Details:

<table id="table_list" class="dataTable" width="100%" cellspacing="0">
   <thead>
      <tr>
         <th>S.No.</th>
         <th>Country</th>
         <th>ISO Alpha-2 Code </th>
         <th>ISO Alpha-3 Code </th>
      </tr>
    </thead>
</table>

DataTable Initialize using jQuery script.

This code shows how to set the required DataTable property key features, and jQuery DataTable initialize. In addition, this code sets the serverSide property to true. The server-side file path for the ajax key is also specified. These keys are used for setting the server-side processing of the DataTable.

$(document).ready(function(e){
		$('#table_list').dataTable({
			"bProcessing": true,
         	"serverSide": true,
         	"ajax":{
	            url :"dataList.php",
	            type: "POST",
	            error: function(){
	              $("#error").show();
	            }
          	}
        });
	});

Server-Side Processing script

Below is the PHP code that will provide data set details in JSON format. This will retrieve the data from MySql using SELECT with ORDER AND LIMIT keys. In addition, it includes column names and their respective values in an array.

<?php 
// DB table to use 
$table = 'worldcountryList';

// Table's primary key 
$primaryKey = 'id'; 

// An array of columns from the database that should be read and returned to DataTables. 
// The 'db' parameter is the database column name, and the 'dt parameter the DataTables column ID. 
// In this example, object parameter names
$columns = array( array('db' => 'id', 'dt' => 0),
	array('db' => 'name', 'dt' => 1),
	array('db' => 'alpha_2',  'dt' => 2),
	array('db' => 'alpha_3',   'dt' => 3)
);
// SQL server connection information
$sql_details = array(
	'user' => 'root',
	'pass' => '******',
	'db'   => 'database',
	'host' => 'localhost'
);

// Helper functions for building a DataTables server-side processing SQL query
require('ssp.class.php');
echo json_encode(SSP::simple($_POST, $sql_details, $table, $primaryKey, $columns));

Database table file attached in demo download you can simply import it in your database and update your database credentials above and run script.

I hope you find it helpfull article update by comments.

Share this:

  • Click to share on Twitter (Opens in new window)
  • Click to share on Facebook (Opens in new window)

Related

  • Tags
  • DataTables
  • MYSQL
  • PHP
  • Server-side
Facebook Twitter Google+ LinkedIn Pinterest
Next article Make Country, State and City Dropdown with REST API & jQuery
Previous article How to Find Geolocation by Country IP Address in PHP

Huzoor Bux

I am a PHP Developer

Related Posts

How to Extract Text from PDF using PHP PHP
May 17th, 2022

How to Extract Text from PDF using PHP

3 reasons why you SHOULD write tests Guide
May 15th, 2022

3 reasons why you SHOULD write tests

Best 10 Programming Languages that will rule in 2022 Guide
May 1st, 2022

Best 10 Programming Languages that will rule in 2022

Leave a Reply Cancel reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.

    Advertisement
    Like us
    Recent Posts
    • How to Extract Text from PDF using PHP
    • 3 reasons why you SHOULD write tests
    • How to create a screen recorder in JavaScript
    • Best 10 Programming Languages that will rule in 2022
    • Top 7 Websites To Get Your First Paid Internship
    Categories
    • API
    • Bootstrap
    • Bot
    • CSS
    • CSS 3
    • Database
    • Designing
    • Framework
    • Guide
    • HTML
    • HTML 5
    • JavaScript
    • jQuery
    • MySQL
    • Node.js
    • oAuth
    • Payment
    • PHP
    • Python
    • Social
    • Tips
    • WordPress
    Weekly Tags
    • PHP
    • How to
    • javascript
    • api
    • MYSQL
    • jQuery
    • PHP Basics
    • Programming Habits
    • HTML5
    • PHP framework
    • About
    • Privacy Policy
    • Back to top
    © PHPLift 2021. All rights reserved.