DataTables AJAX Pagination with Search and Sort – PHP

DataTables is a jQuery plugin that makes it easier to add pagination on the webpage.

Just need to add records list then it will auto-adjust data and create pagination with search and sort feature.

There are options available to implement AJAX pagination.

In this tutorial, I show how you can implement AJAX pagination in DataTables with PHP.

DataTables AJAX Pagination with Search and Sort - PHP


Contents

  1. Table structure
  2. Configuration
  3. Download & Include
  4. HTML
  5. Script
  6. PHP
  7. Demo
  8. Conclusion

1. Table structure

Create employee table.

CREATE TABLE `employee` (
  `id` int(11) NOT NULL PRIMARY KEY AUTO_INCREMENT,
  `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
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

2. Configuration

Create a config.php for the database connection.

Completed Code

<?php

$host = "localhost"; /* Host name */
$user = "root"; /* User */
$password = ""; /* Password */
$dbname = "tutorial"; /* Database name */

$con = mysqli_connect($host, $user, $password,$dbname);
// Check connection
if (!$con) {
  die("Connection failed: " . mysqli_connect_error());
}

3. Download & Include

  • Download Datatables from here.
  • Include datatables.min.css and datatables.min.js in <head> section and also include the jQuery Library.
  • 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.5.1/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.5.1/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. Script

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

Within the method pass options to enable server-side processing and send AJAX post request to ajaxfile.php.

In the columns option pass field names that get read when 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' },
      ]
   });
});

6. PHP

Create a new ajaxfile.php.

Read the $_POST values and store in variables that are passed by DataTable during AJAX request – draw, start, length, order,columnIndex, column name, order, and search.

Prepare search query if $searchValue is not empty.

Count the total number of records in the employee table.

Count the total number of records with the Search filter from the employee table.

Both the count returns same value on the first time. The difference is when the search value from DataTable then the record count with filter will use to show – number of filtered record from total records in DataTable.

Fetch records from employee table.

Loop on the records and initialize $data Array with an associative array. In the Array, the key will the same as defined in columns option during DataTable initialization.

Prepare $response An array that has a draw, iTotalRecords, iTotalDisplayRecords, and aaData keys.

Return $response 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 = mysqli_real_escape_string($con,$_POST['search']['value']); // Search value

## Search 
$searchQuery = " ";
if($searchValue != ''){
   $searchQuery = " and (emp_name like '%".$searchValue."%' or 
        email like '%".$searchValue."%' or 
        city like'%".$searchValue."%' ) ";
}

## Total number of records without filtering
$sel = mysqli_query($con,"select count(*) as allcount from employee");
$records = mysqli_fetch_assoc($sel);
$totalRecords = $records['allcount'];

## Total number of record with filtering
$sel = mysqli_query($con,"select count(*) as allcount from employee WHERE 1 ".$searchQuery);
$records = mysqli_fetch_assoc($sel);
$totalRecordwithFilter = $records['allcount'];

## Fetch records
$empQuery = "select * from employee WHERE 1 ".$searchQuery." order by ".$columnName." ".$columnSortOrder." limit ".$row.",".$rowperpage;
$empRecords = mysqli_query($con, $empQuery);
$data = array();

while ($row = mysqli_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);

7. Demo

View Demo


8. Conclusion

Make sure that field names should be the same in AJAX response data as defined in columns data during DataTable initialization otherwise field value not be read.

Remove serverMethod option if you want to send GET type AJAX request then you also need to update the AJAX file.

You can also view the PDO version here.

If you found this tutorial helpful then don't forget to share.
Are you want to get implementation help, or modify or extend the functionality of this script? Submit paid service request.

110 thoughts on “DataTables AJAX Pagination with Search and Sort – PHP”

  1. I am in process of complying to PDO, would please advise how i should be able to convert the Fetch records $data = array(); using bindparam or bindvalue for pdo statement.

    Reply
  2. All is working,
    except this line, which i would like to replace with
    stmt = con->prepare(select * from employees WHERE 1 ? order by ? ? limit ?, ?);

    than bind the values.

    Reply
    • You can do like this –

      $order = “name”;
      $sort = “asc”;
      $stmt = $pdo->prepare(“Select * from employees WHERE active=:active order by “.$order.” “.$sort.” limit :limit, :offset”);

      $stmt->bindValue(‘:active’, 1, PDO::PARAM_INT);
      $stmt->bindValue(‘:limit’, 0, PDO::PARAM_INT);
      $stmt->bindValue(‘:offset’, 10, PDO::PARAM_INT);

      $stmt->execute();

      Reply
  3. Hi Yogesh,

    Would you be able to post your original query using PDO and email me please.

    if i try i am getting errors. and I am unable to post my query.

    The website is not letting us to type the php/pdo code in the reply section of this website

    Reply
  4. and If you type ‘ single quote or double quote at the beginning, i am getting json error. Please can you advise what can we do to escape or do nothing when ‘ ” are typed.

    secondly, would you be able to convert your original server side using PDO please?

    Reply
  5. hi sir ,
    Thanks this help me a lot in my project.
    but i am having a small problem with this whin i retrive large data i mean data above 5000+ it takes a lot of time to display in the web page . is their any solution on this,
    please help.
    And Thanks a lot for this Content.

    Reply
  6. Thanks for sharing your knowledge!
    In Brazil there is nothing like this. Congratulations!
    However I am trying to adapt this code to my table without success.
    The pager works but the records are not displayed.
    What could it be?
    thankful

    Reply
    • Hi Oliveiros,
      If the page number is showing and your data is not displaying then check the SQL query which you are using for data fetching and initializing the $data Array.

      Reply
  7. Eu consegui após colocar os mesmos campos em $searchQuery =, porém a primeira página não exibe os registros.
    Os registros são exibidos da segunda página em diante.
    A busca não está funcionando.
    Seria porque minha tabela tem mais de 30 mil registros?
    Gostaria de limitar a busca pelos registros da tabela pelo campo DATA_VENDA = curdate(), no entanto não estou conseguindo
    Como resolveria isto?
    Gratidão

    Reply
  8. I got it after putting the same fields in $ searchQuery =, but the first page doesn’t display the records.
    The records are displayed from the second page onwards.
    The search is not working.
    Is it because my table has over 30,000 records?
    I would like to limit the search for table records by the field DATA_VENDA = curdate (), however I am unable to
    How would you solve this?
    Gratitude

    Reply
    • Try to debug it by printing the SQL query and view the query in browser console network tab. Select the ajax request and copy the SQL query and run it in PHPMyAdmin. Check if any data is fetching or not.

      Reply
  9. 346/5000
    sorry for my inattention
    when changing fields in Search
    $ searchQuery = “”;
    if ($ searchValue! = ”) {
    $ searchQuery =
    I ended up removing the like from sql.
    now everything is working very well
    my question now would be just how to limit fetch records
    $ empQuery = with field DATA_VENDA = curdate ()
    Thanks so much for the quick reply previous

    Reply
  10. Hello yogesh, How are you, Am having problem with the search part

    $searchQuery = ” and (entry_no like ‘%”.$searchValue.”%’ or recept_no like ‘%”.$searchValue.”%’ or names like ‘%”.$searchValue.”%’ or
    middle_name like ‘%”.$searchValue.”%’ or email like ‘%”.$searchValue.”%’ or
    surname like’%”.$searchValue.”%’)”;

    when i add email on the list to be searched like on the script , it does not work, what could be the issue

    Reply
      • I have not tried it yet, i came to find out not only email, if i add another search item, the search feature does not work..

      • ## Search
        $searchQuery = ” “;
        if($searchValue != ”){
        $searchQuery = ” and (entry_no like ‘%”.$searchValue.”%’ or recept_no like ‘%”.$searchValue.”%’ or names like ‘%”.$searchValue.”%’ or
        middle_name like ‘%”.$searchValue.”%’ or email like ‘%”.$searchValue.”%’ or
        surname like’%”.$searchValue.”%’)”;
        }

        ## Total number of records without filtering
        $sel = mysqli_query($con,”select count(*) as allcount from majibu a INNER JOIN uhakiki b ON b.id=a.id AND a.size NOT IN(0) ORDER BY curdate desc”);
        $records = mysqli_fetch_assoc($sel);
        $totalRecords = $records[‘allcount’];

        ## Total number of records with filtering
        $sel = mysqli_query($con,”select count(*) as allcount from majibu a INNER JOIN uhakiki b ON b.id=a.id AND a.size NOT IN(0) WHERE 1 “.$searchQuery);
        $records = mysqli_fetch_assoc($sel);
        $totalRecordwithFilter = $records[‘allcount’];

        ## Fetch records
        //$empQuery = “select * from uhakiki WHERE 1 “.$searchQuery.” order by “.$columnName.” “.$columnSortOrder.” limit “.$row.”,”.$rowperpage CONCAT(b.names,’ ‘,b.middle_name,’ ‘,b.surname) as fullname;

        $empQuery = “select DISTINCT(b.entry_no), b.recept_no, b.entry_no, b.names, b.middle_name, TRIM(b.surname) AS surname , b.email, b.status, a.curdate, a.file FROM majibu a INNER JOIN uhakiki b ON b.id=a.id AND a.size NOT IN(0) WHERE 1 “.$searchQuery.” order by “.$order.” “.$sort.” limit “.$row.”,”.$rowperpage;

      • Hello Yogesh, am having issues adding export buttons features for excel, pdf on the javascript . do you have soln for that.

    • In my case the search stopped working when I used inner join in Fetch records.
      So I had to use table_name.column_name in Search and it all flowed

      Reply
      • From this query , $empQuery = “select DISTINCT(id), entry_no, names, middle_name, surname, email, status, file, curdate FROM uhakiki WHERE 1 “.$searchQuery.” order by “.$order.” “.$sort.” limit “.$row.”,”.$rowperpage;

        I want to add this statement “WHERE status IS NULL AND size NOT IN(0)”

  11. Greetings!
    fellow in ajaxfile.php I put:
    “radio” => ” Products ”
    How can I put an event like this?

    $ (document) .ready (function () {
    // define radio field event “onclick” with name prod
    $ (“input [name = prod]”). click (function () {

    // capture the value of fields from the form

    var cod = $ (“input [name = prod]: checked”). val ();

    // use the jquery library’s ajax method to post the data to insert.php
    $ .ajax ({
    “url”: “page.html”,
    “dataType”: “html”,
    “data”: {

    “prod”: prod

    },
    “success”: function (response) {
    // on success, div ID = output gets post response
    $ (“div # products2”). html (response);
    }

    });
    $ .ajax ({
    “url”: “customers29999.php”,
    “dataType”: “html”,
    “data”: {

    },
    “success”: function (response) {
    // on success, div ID = output gets post response
    $ (“div # customers999”). html (response);
    }

    });
    });
    });

    Reply
  12. Hi Mr. Yogesh,
    I want to directly display only male gender. I don’t know where to put the condition.
    Can you help me please.

    Reply
  13. Hi!!
    Great script!
    i want one of field, like employee name , have a hyperlink to another page … Could you help me?
    Thank you!

    Reply
    • Hi Ilias,
      For this add update the $data Array in ajaxfile.php file like this –

      $data[] = array( 
            "emp_name"=> "<a href='#' >".$row['emp_name']."</a>",
            "email"=>$row['email'],
            "gender"=>$row['gender'],
            "salary"=>$row['salary'],
            "city"=>$row['city']
         );

      Specify your link in href.

      Reply
    • Hi Saroj,
      For this, you need to add an anchor tag on a value that you want to display as a link while initializing Array in ajaxfile.php.

      Example –
      $data[] = array(
      “emp_name”=>”“.$row[’emp_name’].”“,
      “email”=>$row[’email’],
      “gender”=>$row[‘gender’],
      “salary”=>$row[‘salary’],
      “city”=>$row[‘city’]
      );

      Reply
    • Hi Gianluca,
      You can do this using columnDefs option while datatable initialization. Specify, your class name to align text to center –

      'columnDefs': [
                        {
                            "targets": 1, // second column
                            "className": "text-center"
                       }],
      Reply
  14. Great explanation.
    I wish to know how could I pass a variable to the scipt.
    For example, I have a dropdown with some channels names and wish to have the returned data from mysql query be about this channel.
    I already have the code working but it only gets all the records (wich are big).
    I can call a php script when the dropdown changes, but I can’t make it work with server side paging because of the variable channel name.
    Could you kindly help me?

    Thank you very much

    Reply
      • Sorry, I didn’t express my self correctly.
        I already have a script working to grab the database data when the dropdown changes, using javascript wich call an ajax query.
        S=But, the problem is that I want to do this using the bootstrap table’s server side pagination.
        So, using your code above, how could I do this, for example, to select the gender in the drobdown box, I mean when you change the gendre value in a dropdown, how will I send the ajax request with the gendre as a parameter?

        Your ajax call is inside the javascript code in:

        $(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’ },
        ]
        });
        });

        Thank you very much for your time.

  15. Hi bro thank you so much for this code, i’m learning from you
    can you help to fix this problem Warning: mysqli_fetch_assoc() expects parameter 1 to be mysqli_result, bool given. Here :

    $empQuery = “select * from sub_events WHERE 1 “.$searchQuery.” order by “.$columnName.” “.$columnSortOrder.” limit “.$row.”,”.$rowperpage;
    $empRecords = mysqli_query($dbhandle, $empQuery);
    $data = array();

    while ($row = mysqli_fetch_assoc($empRecords)) {

    Reply
    • Hi Lido,
      Your SQL query is not executing. You can debug it by printing the SQL query. Update the code with this –

      $empQuery = “select * from sub_events WHERE 1 “.$searchQuery.” order by “.$columnName.” “.$columnSortOrder.” limit “.$row.”,”.$rowperpage;
      echo $empQuery;
      die;
      $empRecords = mysqli_query($dbhandle, $empQuery);

      Navigate to the browser network tab to view the SQL query by selecting the AJAX file.

      Reply
  16. The script seems wrong.

    “iTotalRecords” => $totalRecordwithFilter,
    “iTotalDisplayRecords” => $totalRecords,

    these must be inverted

    iTotalRecord is total record, iTotalDisplayRecords is filtered record.

    Reply
  17. Hi, thanks for a great post. Quick question, do you know if it would be possible to add a 2nd search box? In my table, I have a column that has dates. My other columns have names, phone numbers etc. I want to be able to select a date first to show results from that day only and then use the search function to search for other fields. So that I am only looking at data from a particular day. Hope you can give me some ideas how to do that.

    Reply
  18. DataTables warning: table id=empTable – Invalid JSON response. For more information about this error, please see http://datatables.net/tn/1

    This error occurs whenever 50 or 100 records are selected per page. Also, when performing a search and there is more than 50/100 records per page. This error does not occur if 25 or 100 records per page is selected.
    code is identical to what you have on this page and is as follows:

    $(document).ready(function(){
    $(‘#user_data’).DataTable({
    ‘processing’: true,
    ‘serverSide’: true,
    ‘serverMethod’: ‘post’,
    ‘ajax’: {
    ‘url’:’fetch.php’
    },
    ‘columns’: [
    { data: ‘invQty’ },
    { data: ‘invManufac’ },
    { data: ‘InvPartNum’ },
    { data: ‘invDescr’ },
    { data: ‘invCost’ },
    { data: ‘invLocation’ },
    ]

    });

    //php script
    $con = mysqli_connect( ###,###,###,###);
    ## 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 = ” “;
    if($searchValue != ”){
    $searchQuery = ” and (InvPartNum like ‘%”.$searchValue.”%’ or
    invDescr like ‘%”.$searchValue.”%’ or
    invLocation like’%”.$searchValue.”%’ ) “;
    }

    ## Total number of records without filtering
    $sel = mysqli_query($con,”select count(*) as allcount from inventoryComponents”);
    $records = mysqli_fetch_assoc($sel);
    $totalRecords = $records[‘allcount’];

    ## Total number of record with filtering
    $sel = mysqli_query($con,”select count(*) as allcount from inventoryComponents WHERE 1 “.$searchQuery);
    $records = mysqli_fetch_assoc($sel);
    $totalRecordwithFilter = $records[‘allcount’];

    ## Fetch records
    $empQuery = “select * from inventoryComponents WHERE 1 “.$searchQuery.” order by “.$columnName.” “.$columnSortOrder.” limit “.$row.”,”.$rowperpage;
    $empRecords = mysqli_query($con, $empQuery);
    $data = array();

    while ($row = mysqli_fetch_assoc($empRecords)) {
    $data[] = array(
    “invQty”=>$row[‘invQty’],
    “invManufac”=>$row[‘invManufac’],
    “InvPartNum”=>$row[‘InvPartNum’],
    “invDescr”=>$row[‘invDescr’],
    “invCost”=>$row[‘invCost’],
    “invLocation”=>$row[‘invLocation’]
    );
    }

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

    echo json_encode($response);

    ?>

    Reply
  19. there was a typo in my comment

    This error occurs whenever 50 or 100 records are selected per page. Also, when performing a search and there is more than 50/100 records per page. This error does not occur if 25 or 100 records per page is selected.

    should read:

    This error occurs whenever 50 or 100 records are selected per page. Also, when performing a search and there is more than 50/100 records per page. This error does not occur if “25 or 10” records per page is selected.

    Reply
  20. Hello I am testing out the pagination scripts you have available on the website.

    $sql2 = “select * from staffdetails JOIN staff on staff.idNum=staffdetails.idNum WHERE 1 “.$searchQuery.” order by “.$columnName.” “.$columnSortOrder.”limit “.$row.”,”.$rowperpage;

    For some reason when I join in another table I get a JSON error. I believe the error has something to do with the $columnname and $columnsortorder variable because when I remove it the JSON loads fine. Any help you could give me would be appreciated.

    Reply
  21. Thank you very much for the code. Works great, For employee table I have an Employee class , I return an array of Employee objects.
    Is there a way to get this array ‘into’ the data table? Thanks Siva

    Reply
  22. any help me how to fetch particular id in this ajax method???
    for example in the table there are 1000 entry available and 3 company data like admin 1 and admin 2 and last admin 3 i want the admin 2 data then how can i get particular admin id data ???

    and sorry for the bad English grammar

    Reply
  23. Wonderful script!!!

    My $data Array in ajaxfile.php has this:
    “field_70″=>$row[‘field_70’],
    It is a URL. Instead of the URL written in the cell, how can I make it say, “Website” and post to that URL when clicked?

    Also, how can I post a thumbnail photo (‘m_photo_url’ in my database) inside a column?

    Thanks so much!!!!!

    Reply
    • Hi Rachel,
      For this create your HTML string and assign it to the key like – “field_70” => “<a href='”.$row[‘field_70’].”‘ >Website</a>”, similarly, you can create your image element.

      Reply
  24. Thank you so much Yogesh!

    I decided to use your “How to add Custom Filter in DataTable – AJAX and PHP” script, which adds the ability to search columns. Brilliant!

    I got it to work, including adding the Website link. Thank you!

    I am still unable to add the image. Here’s the problem. All the other information on the page comes from one table (f_member_fields) . The image link (photo_url) is in a different table (f_members).

    f_members has (id) as the primary key.
    f_member_fields has (member_id) as the primary key.

    I tried to combine the 2 tables in a query. I still couldn’t get a thumbnail image to be displayed in the row.

    I then tried to get the thumbnail image to popup onmouseover the emp_name in your example. I thought this might even be a better solution, as the size of the image would be bigger. Again, no luck.

    I appreciate any help you may offer, Yogesh!

    Reply
  25. I almost have it, Yogesh!!!

    I added the table to $empQuery, adding to the WHERE that the id and member_id were equal to each other. I added the column to the table, and IT WORKS…almost!!! The photo is a tiny box because it has the wrong URL.

    The only thing I have to figure out is how to change my data array, img src:
    “photo_url”=>””,

    I need ” ../folder/ ” added in front of .$row[‘photo_url’] to make it display the photo correctly. It seems so easy, yet I am not formatting it correctly. Any ideas?

    Reply
  26. It looks like this form does not allow the information beyond the => to display.

    Here’s what I have to the right of the arrow:
    “”,

    Reply
  27. Hi,
    This is a nice topic.
    I modified the code and am able to fetch data as my responce is having the coreect data.
    However, my first page is displaying no data. data for the first page not found.
    Then when in subsequent pages show s data for the previous data.
    What am doing wrong?

    Reply
  28. 1.I want to display view (not table) on the page in order of ic_id which is in the view but I dont want to display ic_id on the page. How can I achieve these?
    2. And also how to customize Specific column and Search bar?

    index code:-
    $(document).ready(function(){
    $(‘#item’).DataTable({
    ‘processing’: true,
    ‘serverSide’: true,
    ‘order’: [[ 0, ‘desc’ ]],
    ‘serverMethod’: ‘post’,
    ‘ajax’: {
    ‘url’:’ajaxfile.php’
    },
    ‘columns’: [
    { data: ‘ic_id’ },
    { data: ‘item_name’ },
    { data: ‘cost’ },
    { data: ‘retail’ },
    { data: ‘quantity’ },
    ]
    });
    });

    ajaxfile.php code:-
    while ($row = mysqli_fetch_assoc($empRecords)) {
    $data[] = array(
    “ic_id”=>$row[‘ic_id’],
    “item_name”=>$row[‘item_name’],
    “cost”=>$row[‘cost’],
    “retail”=>$row[‘retail’],
    “quantity”=>$row[‘quantity’],
    );

    Reply
  29. Hello,
    this post helps me a lot.
    I have a question regarding the “SearchPane” extension, I have tried a little bit but I haven’t found the right solution in order to show this extension.

    The basic setup says:

    $(‘#myTable’).DataTable( {
    dom: ‘Pfrtip’
    } );

    But exactly how this code need to be added in your code?

    $(document).ready(function(){
    $(‘#empTable’).DataTable({
    ‘processing’: true,
    ‘serverSide’: true,
    ‘serverMethod’: ‘post’,
    ‘searching’: true,
    ‘ajax’: {
    ‘url’:’ajaxfile.php’,
    ‘data’: function(data){
    var service_id = $(‘#searchByServiceID’).val();
    data.searchByServiceID = service_id;
    }
    },
    ‘columns’: [
    { data: ‘id’ },
    { data: ‘phone’ },
    { data: ‘day’ },

    Thanks a lot.

    Reply
  30. Receiving DataTables warning: table id=empTable – Invalid JSON response error but see no json_encode($response) in the files provided.

    Reply
  31. Hi Yogesh,

    So when I initially load the page I get “No matching records found” but also shows, “Showing 1 to 10 of 1,619 entries”

    When I hit a column header the data then shows and everything works. Any reason nothing would load on the initial page load?

    I won’t add my dir structure but checking the processing page alone throws this.

    Notice: Undefined index: draw in
    Notice: Undefined index: start in
    Notice: Undefined index: length in
    Notice: Undefined index: order in
    Notice: Trying to access array offset on value of type null in
    Notice: Trying to access array offset on value of type null in
    Notice: Undefined index: columns in
    Notice: Trying to access array offset on value of type null in
    Notice: Trying to access array offset on value of type null in
    Notice: Undefined index: order in
    Notice: Trying to access array offset on value of type null in
    Notice: Trying to access array offset on value of type null in
    Notice: Undefined index: search in
    Notice: Trying to access array offset on value of type null in
    Warning: mysqli_fetch_assoc() expects parameter 1 to be mysqli_result, bool given in

    Reply
  32. Hello, I’d like no results to be shown in the table until I start typing in the search box and then the relevant results to be displayed. How would I achieve this please?
    Many Thanks in advance for your help

    Reply
  33. Thanks a lot, buddy.

    Yogesh Bhai your script is running successfully. and I got to know how datatables works in core PHP.

    Once again Thanks A Lot 🙂

    Reply
  34. Hello,

    unfortunately the script does not work with german umlauts, e.g. äöü.
    Is there a possibility for this?
    Thank you very much.

    Bernd

    Reply

Leave a Comment