DataTables Pagination

Hi guys, this is a simple tutorial on how to use pagination server side processing in Datatables in PHP.
NOTE: The code you see is a straight forward code.. so expect it to be dirty :)

---

HTML Struture

See figure 1 for the html tags, files required

(Figure 1)

1. CSS style needed by DataTables for its Layout and design.
2. Table tag for DataTables to use. (Note: add attribute id for later use)
3. Jquery js file (library Dependencies), DataTables need this file to be able to run.
4. DataTables file it self.
5. A javascript code to run/use DataTables with Server side processing and pagination.

For more info about the above stated. please go to:

https://datatables.net/manual/installation 

https://datatables.net/examples/data_sources/server_side.html

---

PHP Code

PHP file code (this is the dirty part :) so please bear with me :) )

(Figure 2)

1. Connection string to the db
2. $_POST to get/fetch/receive the data from DataTables
3. Simple query to get all the total record without any filters
4. Simple query to get all the records that are filtered
5. Draw the json return.. 
6. close connection.

so there.. code is complete.. but dont forget the Database.
DBNAME

• User (table)
• id (primary)
• userName
• firstName
• lastName

---

Try to run the code

Should expect this output

---

How it works?

lets see first the js code for running/using DataTables.

(Figure 3)

1. jquery .ready.. if the page is good to go or ready.
2. select the Table tag with attribute id to be used by DataTables
3. DOM: to show only the elements the DataTables will render (every letter has its corresponding elements )
4. this will be the mark that we will use DataTables serverside processing
• serverside: true: to communicate with serverside.
• processing: true: to process the fetch data.
• ajax: url or file where to post,sent, communicate with your backend.

For more info about DOM of datatables: https://datatables.net/reference/option/dom
Serverside processing: https://datatables.net/examples/data_sources/server_side.html

After page load.. DataTables will send data params to your api (see figure 4). thats because of "serverside: true", "processing: true" and "ajax" we declare/set in our DataTables js code.

(Figure 4)

This data is sent/post to your api, and will be used for filtering.

Figure 5

1. For Columns Search
2. Ordering
3. Paging
4. Search text

We will do Pagination so i will just use Paging parameters "Start" and "Length".

---

Now that DataTables sent data to our api, its now time to check our api code.
We will get the post parameter sent by DataTables and we use it for our SQL Query to create a filter (figure 6).

(Figure 6)

Then we will create/build the correct json format that DataTables wants.

(Figure 6.1)

JSON format DataTables needed: https://datatables.net/examples/ajax/simple.html

This is the JSON response sent back DataTables.

(Figure 7)

1. Data result from our filtered SQL query.
2. recordsTotal: this is the total record without a query filter
3. recordsFiltered: this is the total record after a query filter

and thats it. your DataTables is now working with pagination server side processing. :)

To Download the dirty code.

Click here 

Thank you.

happy coding:)