Datatables Example using PHP and MySQL

POST data

By default, the Ajax request that DataTables makes to obtain server-side processing data is an HTTP GET request. However, there are times when you might wish to use POST. This is very easily done by using the type option of the ajax initialisation option.

When given as an object, the ajax option maps directly onto the jQuery ajax options (i.e. any option that can be used in jQuery’s Ajax function can also be used in DataTable’s ajax option).

The example below shows ajax being used with the type option set to POST to make a POST request.

First name	Last name	Position	Office	Start date	Salary
First name	Last name	Position	Office	Start date	Salary

• Javascript
• HTML
• CSS
• Ajax
• Server-side script
• Comments

The Javascript shown below is used to initialise the table shown in this example:

In addition to the above code, the following Javascript library files are loaded for use in this example:

The HTML shown below is the raw HTML table element, before it has been enhanced by DataTables:

This example uses a little bit of additional CSS beyond what is loaded from the library files (below), in order to correctly display the table. The additional CSS used is shown below:

The following CSS library files are loaded for use in this example to provide the styling of the table:

This table loads data by Ajax. The latest data that has been loaded is shown below. This data will update automatically as any additional data is loaded.

The script used to perform the server-side processing for this table is shown below. Please note that this is just an example script using PHP. Server-side processing scripts can be written in any language, using the protocol described in the DataTables documentation.

Other examples

Basic initialisation

• Zero configuration
• Feature enable / disable
• Default ordering (sorting)
• Multi-column ordering
• Multiple tables
• Hidden columns
• Complex headers (rowspan and colspan)
• DOM positioning
• Flexible table width
• State saving
• Alternative pagination
• Data rendering
• Scroll — vertical
• Scroll — vertical, dynamic height
• Scroll — horizontal
• Scroll — horizontal and vertical
• Language — Comma decimal place

Advanced initialisation

• DOM / jQuery events
• DataTables events
• Column rendering
• Enter Key to Search
• Page length options
• Multiple table control elements
• Complex headers with column visibility
• Read HTML to data objects
• HTML5 data-* attributes — cell data
• HTML5 data-* attributes — table options
• Setting defaults
• Row created callback
• Row grouping
• Footer callback
• Custom toolbar elements
• Order direction sequence control
• Example of stocks results

Data sources

Internationalisation

DateTime

Styling

• Base style
• Base style — no styling classes
• Base style — cell borders
• Base style — compact
• Base style — hover
• Base style — order-column
• Base style — row borders
• Base style — stripe
• Bootstrap 3
• Bootstrap 4
• Bootstrap 5
• Foundation
• Fomantic-UI (formally Semantic-UI)
• Bulma
• jQuery UI ThemeRoller
• Material Design (Tech. preview)
• Tailwind CSS (Tech. preview)
• UIKit 3 (Tech. preview)

API

• Add rows
• Individual column searching (text inputs)
• Individual column searching (select inputs)
• Highlighting rows and columns
• Child rows (show extra / detailed information)
• Child rows with StateSave
• Row selection (multiple rows)
• Row selection and deletion (single row)
• Form inputs
• Index column
• Show / hide columns dynamically
• Using API in callbacks
• Scrolling and Bootstrap tabs
• Search API (regular expressions)
• HighCharts Integration

Ajax

Server-side

Plug-ins

DataTables

DataTables designed and created by SpryMedia Ltd.
© 2007-2023 MIT licensed. Privacy policy. Supporters.
SpryMedia Ltd is registered in Scotland, company no. SC456502.

Источник

jQuery Datatables with PHP, MySQL and AJAX Example

Hi! In this tutorial let’s look at the server-side processing of jquery datatables using php, mysql and ajax. In case you don’t know, Datatables is an amazing jquery plugin that converts the simple html table into a feature-rich data grid with additional functions like instant search, pagination, multi-column sorting etc. The table works with multiple data sources like DOM, AJAX etc., and supports both client and server side processing. We have already seen about datatables with json data and here we will see about server side processing.

For the server script, we are going to use PHP and MySQL as a data source.

jQuery Datatables Server-side Processing with PHP and MySQL:

Let’s see how to fetch records from the server-side using ajax request and list it in the data tables. To use in this example, we need a dummy database. So let’s create it first.

Step 1) Create MySQL Database

The following sql will create a mysql database, a table and some sample records in it. Run this script on phpmyadmin to create the database.

CREATE DATABASE `my_demo`; USE `my_demo`; CREATE TABLE `customers` ( `id` int(8) NOT NULL AUTO_INCREMENT, `name` varchar(50) NOT NULL, `email` varchar(60) NOT NULL, PRIMARY KEY (`id`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=14 ; INSERT INTO `customers` (`id`, `name`, `email`) VALUES (1, 'Jim Connor', 'jimconnor@yahoo.com'), (2, 'Mark Higgins', 'mark.higgins21@yahoo.com'), (3, 'Austin Joseph', 'austin.joseph.boston@gmail.com'), (4, 'Sean Kennedy', 'seankennedy01@gmail.com'), (5, 'Rose Harris', 'roseharris@gmail.com'), (6, 'Lilly Whites', 'lillywhites@outlook.com'), (7, 'Jennifer Winters', 'jennie.winters001@gmail.com'), (8, 'Michael Bruce', 'michaelbruce78@yahoo.com'), (9, 'John Alex', 'johnalex@example.com'), (10, 'Demi Milan', 'demimilan@gmail.com'), (11, 'Austin Joseph', 'austin.joseph.boston@gmail.com'), (12, 'Mark Higgins', 'mark.higgins21@yahoo.com'), (13, 'Sean Kennedy', 'seankennedy.boss@outlook.com');

Step 2) Load the Required CSS and JS Libraries

Next load the CSS and JS files of the datatables plug-in. And you must also include the ‘jquery.js’ before loading ‘jquery.datatables.js’ since it is dependent on jquery.

Please note that I have loaded the datatables files from the cdn but you can download and use it from your own server though.

Step 3) Create HTML Table

Then create html markup for the table. This will act as a placeholder for data table. Just add the appropriate column headers for the table.

Step 4) AJAX Call

Next, make ajax request to the php script to get the data from the server-side. You must also map the table columns with the fields in the database to populate the html table.

The method dataTable() will initialize the datatables and comes with various options. By setting different parameters we can control the way it behaves.

Complete index.html File

     
Datatables Server-Side Example
 
 
 
ID
 
Name
 
Email
 
 
 
      

Step 5) Fetch Records from Database and Return as JSON

Finally the server script. This will communicate with the backend mysql database, retrieve records, encode it to json along with other necessary values and send it back to the front-end.

fetch_records.php

 $dataset = array( "echo" => 1, "totalrecords" => count($array), "totaldisplayrecords" => count($array), "data" => $array ); echo json_encode($dataset); ?>

We have all the code in place. Now run the index.html and you can see data grid like this,

You can filter the records using the instant search box at the top this way,

That explains about displaying jquery datatables with database records using php and mysql. Although datatables works fairly well with client data sources such as json, JS Array etc., when you want to work with a huge data-set, going with server side processing is the best route. I hope this tutorial is useful for you. Please, share it in your social circle if you like it.

Источник

Ajax sourced data

DataTables has the ability to read data from virtually any JSON data source that can be obtained by Ajax. This can be done, in its most simple form, by setting the ajax option to the address of the JSON data source.

The ajax option also allows for more advanced configuration such as altering how the Ajax request is made. See the ajax documentation and the other Ajax examples for further information.

The example below shows DataTables loading data for a table from arrays as the data source (object parameters can also be used through the columns.data option ).

• Javascript
• HTML
• CSS
• Ajax
• Server-side script
• Comments

The Javascript shown below is used to initialise the table shown in this example:

In addition to the above code, the following Javascript library files are loaded for use in this example:

The HTML shown below is the raw HTML table element, before it has been enhanced by DataTables:

This example uses a little bit of additional CSS beyond what is loaded from the library files (below), in order to correctly display the table. The additional CSS used is shown below:

The following CSS library files are loaded for use in this example to provide the styling of the table:

This table loads data by Ajax. The latest data that has been loaded is shown below. This data will update automatically as any additional data is loaded.

The script used to perform the server-side processing for this table is shown below. Please note that this is just an example script using PHP. Server-side processing scripts can be written in any language, using the protocol described in the DataTables documentation.

Other examples

Basic initialisation

• Zero configuration
• Feature enable / disable
• Default ordering (sorting)
• Multi-column ordering
• Multiple tables
• Hidden columns
• Complex headers (rowspan and colspan)
• DOM positioning
• Flexible table width
• State saving
• Alternative pagination
• Data rendering
• Scroll — vertical
• Scroll — vertical, dynamic height
• Scroll — horizontal
• Scroll — horizontal and vertical
• Language — Comma decimal place

Advanced initialisation

• DOM / jQuery events
• DataTables events
• Column rendering
• Enter Key to Search
• Page length options
• Multiple table control elements
• Complex headers with column visibility
• Read HTML to data objects
• HTML5 data-* attributes — cell data
• HTML5 data-* attributes — table options
• Setting defaults
• Row created callback
• Row grouping
• Footer callback
• Custom toolbar elements
• Order direction sequence control
• Example of stocks results

Data sources

Internationalisation

DateTime

Styling

• Base style
• Base style — no styling classes
• Base style — cell borders
• Base style — compact
• Base style — hover
• Base style — order-column
• Base style — row borders
• Base style — stripe
• Bootstrap 3
• Bootstrap 4
• Bootstrap 5
• Foundation
• Fomantic-UI (formally Semantic-UI)
• Bulma
• jQuery UI ThemeRoller
• Material Design (Tech. preview)
• Tailwind CSS (Tech. preview)
• UIKit 3 (Tech. preview)

API

• Add rows
• Individual column searching (text inputs)
• Individual column searching (select inputs)
• Highlighting rows and columns
• Child rows (show extra / detailed information)
• Child rows with StateSave
• Row selection (multiple rows)
• Row selection and deletion (single row)
• Form inputs
• Index column
• Show / hide columns dynamically
• Using API in callbacks
• Scrolling and Bootstrap tabs
• Search API (regular expressions)
• HighCharts Integration

Ajax

Server-side

Plug-ins

DataTables

DataTables designed and created by SpryMedia Ltd.
© 2007-2023 MIT licensed. Privacy policy. Supporters.
SpryMedia Ltd is registered in Scotland, company no. SC456502.

Источник