Create Datatables AJAX pagination from PostgreSQL with PHP

With pagination, you can display lots of data on the page in an effective way.

You can implement this easily using DataTables jQuery plugin.

In this tutorial, I show how you can create DataTables AJAX pagination with search and sort from PostgreSQL database using PHP.

Download

---

Contents

1. Table structure
2. Configuration
3. Download and Include
4. HTML
5. PHP
6. jQuery
7. Output
8. Conclusion

---

1. Table structure

I am using employees table in the example. It has the following structure –

CREATE TABLE employees (
    id serial PRIMARY KEY,
    emp_name varchar(80) NOT NULL,
    salary varchar(20) NOT NULL,
    gender varchar(10) NOT NULL,
    city varchar(80) NOT NULL,
    email varchar(80) NOT NULL
)

---

2. Configuration

Create a config.php for database configuration.

Completed Code

<?php

$host = "localhost";
$user = "postgres";
$password = "root";
$dbname = "tutorial";
$con = pg_connect("host=$host dbname=$dbname user=$user password=$password");

if (!$con) {
   die('Connection failed.');
}

---

3. Download and Include

• Download Datatables from here.
• Include datatables.min.css, jQuery library, and datatables.min.js.
• You can also use CDN.

<!-- Datatable CSS -->
<link href='//cdn.datatables.net/1.10.19/css/jquery.dataTables.min.css' rel='stylesheet' type='text/css'>

<!-- jQuery Library -->
<script src="https://ajax.googleapis.com/ajax/libs/jquery/3.6.0/jquery.min.js"></script>

<!-- Datatable JS -->
<script src="//cdn.datatables.net/1.10.19/js/jquery.dataTables.min.js"></script>

---

4. HTML

Create a <table id='empTable' class='display dataTable'> and add column name in <thead>.

Completed Code

<!-- Datatable CSS -->
<link href='//cdn.datatables.net/1.10.19/css/jquery.dataTables.min.css' rel='stylesheet' type='text/css'>

<!-- jQuery Library -->
<script src="https://ajax.googleapis.com/ajax/libs/jquery/3.6.0/jquery.min.js"></script>

<!-- Datatable JS -->
<script src="//cdn.datatables.net/1.10.19/js/jquery.dataTables.min.js"></script>

<!-- Table -->
<table id='empTable' class='display dataTable'>

  <thead>
    <tr>
      <th>Employee name</th>
      <th>Email</th>
      <th>Gender</th>
      <th>Salary</th>
      <th>City</th>
    </tr>
  </thead>

</table>

---

5. PHP

Create ajaxfile.php file.

Read DataTables POST values and assigned them to variables.

Count total records with and without search filter from the employees table.

Fetch records from employees table and initialize $data Array with values.

NOTE – Array key names must be same as defined in the columns option while initializing DataTables.

Initialize $response Array with required values and return in JSON format.

Completed Code

<?php
## Database configuration
include 'config.php';

## Read value
$draw = $_POST['draw'];
$row = $_POST['start'];
$rowperpage = $_POST['length']; // Rows display per page
$columnIndex = $_POST['order'][0]['column']; // Column index
$columnName = $_POST['columns'][$columnIndex]['data']; // Column name
$columnSortOrder = $_POST['order'][0]['dir']; // asc or desc
$searchValue = $_POST['search']['value']; // Search value

## Search 
$searchQuery = "id>$1";
$searchQueryVal = array();
$searchQueryVal[] = 0;

if($searchValue != ''){
     $searchQuery .= " and (emp_name ilike $2 or 
          email ilike $3 or 
          city ilike $4 ) ";

     $searchQueryVal[] = '%'.$searchValue.'%';
     $searchQueryVal[] = '%'.$searchValue.'%';
     $searchQueryVal[] = '%'.$searchValue.'%';

}

## Total number of records without filter
$sql = "select count(*) as allcount from employees";
$result = pg_query($con,$sql);
$records = pg_fetch_assoc($result);
$totalRecords = $records['allcount'];

## Total number of record with filter
$sql = "select count(*) as allcount from employees where ".$searchQuery;
$result = pg_query_params($con,$sql,$searchQueryVal);
$records = pg_fetch_assoc($result);
$totalRecordwithFilter = $records['allcount'];

## Fetch records
$sql = "select * from employees where ".$searchQuery." order by ".$columnName." ".$columnSortOrder." limit $rowperpage OFFSET $row";

$empRecords = pg_query_params($con,$sql,$searchQueryVal);
$data = array();

while ($row = pg_fetch_assoc($empRecords)) {
    $data[] = array( 
        "emp_name"=>$row['emp_name'],
        "email"=>$row['email'],
        "gender"=>$row['gender'],
        "salary"=>$row['salary'],
        "city"=>$row['city']
    );
}

## Response
$response = array(
    "draw" => intval($draw),
    "iTotalRecords" => $totalRecords,
    "iTotalDisplayRecords" => $totalRecordwithFilter,
    "aaData" => $data
);

echo json_encode($response);

---

6. jQuery

Initialize DataTables on <table id='empTable'>.

Enable serverside processing and set the POST method using options. Send AJAX request to 'ajaxfile.php'.

In the columns option pass key names that get read on AJAX successfully callback.

Completed Code

$(document).ready(function(){
   $('#empTable').DataTable({
      'processing': true,
      'serverSide': true,
      'serverMethod': 'post',
      'ajax': {
          'url':'ajaxfile.php'
      },
      'columns': [
         { data: 'emp_name' },
         { data: 'email' },
         { data: 'gender' },
         { data: 'salary' },
         { data: 'city' },
      ]
   });
});

---

7. Output

View Output

---

8. Conclusion

If you are getting JSON error while loading the page then check the SQL queries again and return response and use the browser network tab to debug.

Share this post