KoolReport
  1. Home
  2. DataGrid
  3. How to use DataTables Server Processing feature

DataTables' Server Processing

Displaying, paging and searching large datasets with server processing
"employees_salaries": approximate 2.8 million records

emp_name
emp_no
salary
from_date
to_date

By default, DataTables retrieves all records from a data source and performs paging, sorting, searching on client-side. The good thing of this method is all data is available at client ready to show for customer. The drawback is that it does not work well with large dataset. We are talking about millions of data points. Imagine if we load all data to clients, the browser's performance will be downgrade and it is actually a waste since most of users will not view all data.

To overcome this issue, we would like to introduce "serverSide" options for DataGrid in which data will not load all at beginning. Rather it will load page by page on-demand. Beside the paging, this serverSide feature also support sorting and searching.

Code

DataTables::create(array(
    ...
    "dataSource" => function() {
        return $this->src('employees')
        ->query('select * from salaries');
    },
    "serverSide"=>true,
    // "method"=>'post', //default method = 'get'
    ...

<?php
require_once "MyReport.php";

$report = new MyReport;
$report->run()->render();

<?php
//Step 1: Load KoolReport
require_once "../../../load.koolreport.php";

//Step 2: Creating Report class
class MyReport extends \koolreport\KoolReport
{
    function settings()
    {
        return array(
            "dataSources" => array(
                "employees"=>array(
                    "connectionString"=>"mysql:host=localhost;dbname=employees",
                    "username"=>"root",
                    "password"=>"",
                    "charset"=>"utf8"
                ), 
            )
        );
    }
    protected function setup()
    {

    }
}
<?php
    use \koolreport\core\Utility as Util;
    use \koolreport\datagrid\DataTables;
?>
<style>
    .dataTables_paginate.paging_input {
        padding: 0 !important;
    }
    .dataTables_info,
    .dataTables_paginate.paging_input span,
    .dataTables_length label,
    .dataTables_filter label {
        padding-top: 5px !important;
        padding-bottom: 5px !important;
    }
    .dataTables_length label,
    .dataTables_filter label {
        margin: 0 20px !important;
    }
</style>
<div class="report-content">
    <div class="text-center">
        <h1>DataTables' Server Processing</h1>
        <p class="lead">
            Displaying, paging and searching large datasets with server processing<br>
            "employees_salaries": approximate 2.8 million records
        </p>
    </div>

    <?php
    DataTables::create(array(
        'name' => 'DataTable1',
        'dataSource' => function() {
            return $this->src('employees')
            ->query('select * from employees_salaries');
            // ->query("select concat(e.first_name, ' ', e.last_name) as emp_name,
            // s.* from salaries s left join employees e on s.emp_no = e.emp_no");
        },
        'scope' => $this->params,
        "options" => array(
            "searching" => true,
            "paging" => true,
            "pagingType" => "input",
            "colReorder" => true,
            "order" => [],
            "ordering" => false,
            "pageLength" => 25,
            //"pagingType" => "input",
            //"dom" => '<"top"ipfl<"clear">>rt<"bottom"ipfl<"clear">>'
            // "dom" => '<"top"iflp<"clear">>rt<"bottom"ip<"clear">>'
        ),
        // "columns"=>array(
        //     "customerName" => array("label" => "Customer"),
        //     "productLine" => array("label" => "Category"),
        //     "productName" => array("label" => "Product"),
        // ),
        "showFooter"=>true,
        "serverSide"=>true,
        "themeBase"=>"bs4",
        "method"=>'post', //default method = 'get'
        "searchOnEnter" => true,
        "searchMode" => "or"
    ));
    ?>
</div>
emp_nobirth_datefirst_namelast_namegenderhire_date
10,001 1953-09-02 Georgi Facello M 1986-06-26
10,002 1964-06-02 Bezalel Simmel F 1985-11-21
10,003 1959-12-03 Parto Bamford M 1986-08-28
10,004 1954-05-01 Chirstian Koblick M 1986-12-01
10,005 1955-01-21 Kyoichi Maliniak M 1989-09-12
10,006 1953-04-20 Anneke Preusig F 1989-06-02
10,007 1957-05-23 Tzvetan Zielinski F 1989-02-10
10,008 1958-02-19 Saniya Kalloufi M 1994-09-15
10,009 1952-04-19 Sumant Peac F 1985-02-18
10,010 1963-06-01 Duangkaew Piveteau F 1989-08-24
10,011 1953-11-07 Mary Sluis F 1990-01-22
10,012 1960-10-04 Patricio Bridgland M 1992-12-18
10,013 1963-06-07 Eberhardt Terkki M 1985-10-20
10,014 1956-02-12 Berni Genin M 1987-03-11
10,015 1959-08-19 Guoxiang Nooteboom M 1987-07-02
10,016 1961-05-02 Kazuhito Cappelletti M 1995-01-27
10,017 1958-07-06 Cristinel Bouloucos F 1993-08-03
10,018 1954-06-19 Kazuhide Peha F 1987-04-03
10,019 1953-01-23 Lillian Haddadi M 1999-04-30
10,020 1952-12-24 Mayuko Warwick M 1991-01-26
10,021 1960-02-20 Ramzi Erde M 1988-02-10
10,022 1952-07-08 Shahaf Famili M 1995-08-22
10,023 1953-09-29 Bojan Montemayor F 1989-12-17
10,024 1958-09-05 Suzette Pettey F 1997-05-19
10,025 1958-10-31 Prasadram Heyers M 1987-08-17
10,026 1953-04-03 Yongqiao Berztiss M 1995-03-20
10,027 1962-07-10 Divier Reistad F 1989-07-07
10,028 1963-11-26 Domenick Tempesti M 1991-10-22
10,029 1956-12-13 Otmar Herbst M 1985-11-20
10,030 1958-07-14 Elvis Demeyer M 1994-02-17
10,031 1959-01-27 Karsten Joslin M 1991-09-01
10,032 1960-08-09 Jeong Reistad F 1990-06-20
10,033 1956-11-14 Arif Merlo M 1987-03-18
10,034 1962-12-29 Bader Swan M 1988-09-21
10,035 1953-02-08 Alain Chappelet M 1988-09-05
10,036 1959-08-10 Adamantios Portugali M 1992-01-03
10,037 1963-07-22 Pradeep Makrucki M 1990-12-05
10,038 1960-07-20 Huan Lortz M 1989-09-20
10,039 1959-10-01 Alejandro Brender M 1988-01-19
10,040 1959-09-13 Weiyi Meriste F 1993-02-14
10,041 1959-08-27 Uri Lenart F 1989-11-12
10,042 1956-02-26 Magy Stamatiou F 1993-03-21
10,043 1960-09-19 Yishay Tzvieli M 1990-10-20
10,044 1961-09-21 Mingsen Casley F 1994-05-21
10,045 1957-08-14 Moss Shanbhogue M 1989-09-02
10,046 1960-07-23 Lucien Rosenbaum M 1992-06-20
10,047 1952-06-29 Zvonko Nyanchama M 1989-03-31
10,048 1963-07-11 Florian Syrotiuk M 1985-02-24
10,049 1961-04-24 Basil Tramer F 1992-05-04
10,050 1958-05-21 Yinghua Dredge M 1990-12-25
10,051 1953-07-28 Hidefumi Caine M 1992-10-15
10,052 1961-02-26 Heping Nitsch M 1988-05-21
10,053 1954-09-13 Sanjiv Zschoche F 1986-02-04
10,054 1957-04-04 Mayumi Schueller M 1995-03-13
10,055 1956-06-06 Georgy Dredge M 1992-04-27
10,056 1961-09-01 Brendon Bernini F 1990-02-01
10,057 1954-05-30 Ebbe Callaway F 1992-01-15
10,058 1954-10-01 Berhard McFarlin M 1987-04-13
10,059 1953-09-19 Alejandro McAlpine F 1991-06-26
10,060 1961-10-15 Breannda Billingsley M 1987-11-02
10,061 1962-10-19 Tse Herber M 1985-09-17
10,062 1961-11-02 Anoosh Peyn M 1991-08-30
10,063 1952-08-06 Gino Leonhardt F 1989-04-08
10,064 1959-04-07 Udi Jansch M 1985-11-20
10,065 1963-04-14 Satosi Awdeh M 1988-05-18
10,066 1952-11-13 Kwee Schusler M 1986-02-26
10,067 1953-01-07 Claudi Stavenow M 1987-03-04
10,068 1962-11-26 Charlene Brattka M 1987-08-07
10,069 1960-09-06 Margareta Bierman F 1989-11-05
10,070 1955-08-20 Reuven Garigliano M 1985-10-14
10,071 1958-01-21 Hisao Lipner M 1987-10-01
10,072 1952-05-15 Hironoby Sidou F 1988-07-21
10,073 1954-02-23 Shir McClurg M 1991-12-01
10,074 1955-08-28 Mokhtar Bernatsky F 1990-08-13
10,075 1960-03-09 Gao Dolinsky F 1987-03-19
10,076 1952-06-13 Erez Ritzmann F 1985-07-09
10,077 1964-04-18 Mona Azuma M 1990-03-02
10,078 1959-12-25 Danel Mondadori F 1987-05-26
10,079 1961-10-05 Kshitij Gils F 1986-03-27
10,080 1957-12-03 Premal Baek M 1985-11-19
10,081 1960-12-17 Zhongwei Rosen M 1986-10-30
10,082 1963-09-09 Parviz Lortz M 1990-01-03
10,083 1959-07-23 Vishv Zockler M 1987-03-31
10,084 1960-05-25 Tuval Kalloufi M 1995-12-15
10,085 1962-11-07 Kenroku Malabarba M 1994-04-09
10,086 1962-11-19 Somnath Foote M 1990-02-16
10,087 1959-07-23 Xinglin Eugenio F 1986-09-08
10,088 1954-02-25 Jungsoon Syrzycki F 1988-09-02
10,089 1963-03-21 Sudharsan Flasterstein F 1986-08-12
10,090 1961-05-30 Kendra Hofting M 1986-03-14
10,091 1955-10-04 Amabile Gomatam M 1992-11-18
10,092 1964-10-18 Valdiodio Niizuma F 1989-09-22
10,093 1964-06-11 Sailaja Desikan M 1996-11-05
10,094 1957-05-25 Arumugam Ossenbruggen F 1987-04-18
10,095 1965-01-03 Hilari Morton M 1986-07-15
10,096 1954-09-16 Jayson Mandell M 1990-01-14
10,097 1952-02-27 Remzi Waschkowski M 1990-09-15
10,098 1961-09-23 Sreekrishna Servieres F 1985-05-13
10,099 1956-05-25 Valter Sullins F 1988-10-18
10,100 1953-04-21 Hironobu Haraldson F 1987-09-21

What People Are Saying

"KoolReport helps me very much in creating data report for my corporate! Keep up your good work!"
-- Alain Melsens

"The first use of your product. I was impressed by its easiness and powerfulness. This product is a great and amazing."
-- Dr. Lew Choy Onn

"Fantastic framework for reporting!"
-- Greg Schneider

Download KoolReport Get KoolReport Pro