KoolReport
  1. Home
  2. DataGrid
  3. How to use DataTables with PHP

DataTables

The minimum settings to get DataTables working

employeeNumber
firstName
lastName
jobTitle
extension
1,002 Diane Murphy President x5800
1,056 Mary Patterson VP Sales x4611
1,076 Jeff Firrelli VP Marketing x9273
1,088 William Patterson Sales Manager (APAC) x4871
1,102 Gerard Bondur Sale Manager (EMEA) x5408
1,143 Anthony Bow Sales Manager (NA) x5428
1,165 Leslie Jennings Sales Rep x3291
1,166 Leslie Thompson Sales Rep x4065
1,188 Julie Firrelli Sales Rep x2173
1,216 Steve Patterson Sales Rep x4334
1,286 Foon Yue Tseng Sales Rep x2248
1,323 George Vanauf Sales Rep x4102
1,337 Loui Bondur Sales Rep x6493
1,370 Gerard Hernandez Sales Rep x2028
1,401 Pamela Castillo Sales Rep x2759
1,501 Larry Bott Sales Rep x2311
1,504 Barry Jones Sales Rep x102
1,611 Andy Fixter Sales Rep x101
1,612 Peter Marsh Sales Rep x102
1,619 Tom King Sales Rep x103
1,621 Mami Nishi Sales Rep x101
1,625 Yoshimi Kato Sales Rep x102
1,702 Martin Gerard Sales Rep x2312

Having myriad of features including paging, grouping, searching, DataTables is one of the best widgets to represent data in tabular format.

This example shows the minimum settings to get DataTables working.

<?php 
    DataTables::create(array(
        "dataSource"=>$this->dataStore("employee")
    ));
?>

In above example, DataTables use DataStore as the source of data, beside the DataStore which is most used, DataTables like any other KoolReport's widget can receive data from array or direct SQL query.

Use array as datasource

<?php 
    DataTables::create(array(
        "dataSource"=>array(
            array("name"=>"Peter","age"=>35),
            array("name"=>"David","age"=>32),
        )
    ));
?>

or in table format

<?php 
    DataTables::create(array(
        "dataSource"=>array(
            array("name","age"),
            array("Peter",35),
            array("David",32),
        )
    ));
?>

Use SQL query directly

<?php 
    DataTables::create(array(
        "dataSource"=>$this->src("automaker")->query("SELECT * FROM employees")
    ));
?>

<?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(
                "automaker"=>array(
                    "connectionString"=>"mysql:host=localhost;dbname=automaker",
                    "username"=>"root",
                    "password"=>"",
                    "charset"=>"utf8"
                ),
            )
        ); 
    } 
    protected function setup()
    {
        $this->src('automaker')
        ->query("SELECT employeeNumber, firstName,lastName,jobTitle, extension from employees")
        ->pipe($this->dataStore("employees"));
    } 

}
<?php

use \koolreport\datagrid\DataTables;
?>
<div class="report-content">
    <div class="text-center">
        <h1>DataTables</h1>
        <p class="lead">
            The minimum settings to get DataTables working
        </p>
    </div>

    <?php
    // $data = array_slice($this->dataStore("sales")->data(), 0, 20);
    // echo "<pre>" . var_export($data) . "</pre>";
    // $data = array(
    //     array('employeeNumber' => '1002', 'firstName' => 'Diane', 'lastName' => 'Murphy', 'jobTitle' => 'President', 'extension' => 'x5800'), 
    //     array('employeeNumber' => '1056', 'firstName' => 'Mary', 'lastName' => 'Patterson', 'jobTitle' => 'VP Sales', 'extension' => 'x4611'), 
    //     array('employeeNumber' => '1076', 'firstName' => 'Jeff', 'lastName' => 'Firrelli', 'jobTitle' => 'VP Marketing', 'extension' => 'x9273'), 
    //     array('employeeNumber' => '1088', 'firstName' => 'William', 'lastName' => 'Patterson', 'jobTitle' => 'Sales Manager (APAC)', 'extension' => 'x4871'), 
    //     array('employeeNumber' => '1102', 'firstName' => 'Gerard', 'lastName' => 'Bondur', 'jobTitle' => 'Sale Manager (EMEA)', 'extension' => 'x5408'), 
    //     array('employeeNumber' => '1143', 'firstName' => 'Anthony', 'lastName' => 'Bow', 'jobTitle' => 'Sales Manager (NA)', 'extension' => 'x5428'), 
    //     array('employeeNumber' => '1165', 'firstName' => 'Leslie', 'lastName' => 'Jennings', 'jobTitle' => 'Sales Rep', 'extension' => 'x3291'), 
    //     array('employeeNumber' => '1166', 'firstName' => 'Leslie', 'lastName' => 'Thompson', 'jobTitle' => 'Sales Rep', 'extension' => 'x4065'), 
    //     array('employeeNumber' => '1188', 'firstName' => 'Julie', 'lastName' => 'Firrelli', 'jobTitle' => 'Sales Rep', 'extension' => 'x2173'), 
    //     array('employeeNumber' => '1216', 'firstName' => 'Steve', 'lastName' => 'Patterson', 'jobTitle' => 'Sales Rep', 'extension' => 'x4334'), 
    //     array('employeeNumber' => '1286', 'firstName' => 'Foon Yue', 'lastName' => 'Tseng', 'jobTitle' => 'Sales Rep', 'extension' => 'x2248'), 
    //     array('employeeNumber' => '1323', 'firstName' => 'George', 'lastName' => 'Vanauf', 'jobTitle' => 'Sales Rep', 'extension' => 'x4102'), 
    //     array('employeeNumber' => '1337', 'firstName' => 'Loui', 'lastName' => 'Bondur', 'jobTitle' => 'Sales Rep', 'extension' => 'x6493'), 
    //     array('employeeNumber' => '1370', 'firstName' => 'Gerard', 'lastName' => 'Hernandez', 'jobTitle' => 'Sales Rep', 'extension' => 'x2028'), 
    //     array('employeeNumber' => '1401', 'firstName' => 'Pamela', 'lastName' => 'Castillo', 'jobTitle' => 'Sales Rep', 'extension' => 'x2759'), 
    //     array('employeeNumber' => '1501', 'firstName' => 'Larry', 'lastName' => 'Bott', 'jobTitle' => 'Sales Rep', 'extension' => 'x2311'), 
    //     array('employeeNumber' => '1504', 'firstName' => 'Barry', 'lastName' => 'Jones', 'jobTitle' => 'Sales Rep', 'extension' => 'x102'), 
    //     array('employeeNumber' => '1611', 'firstName' => 'Andy', 'lastName' => 'Fixter', 'jobTitle' => 'Sales Rep', 'extension' => 'x101'), 
    //     array('employeeNumber' => '1612', 'firstName' => 'Peter', 'lastName' => 'Marsh', 'jobTitle' => 'Sales Rep', 'extension' => 'x102'), 
    //     array('employeeNumber' => '1619', 'firstName' => 'Tom', 'lastName' => 'King', 'jobTitle' => 'Sales Rep', 'extension' => 'x103')
    // );
    // $data2 = array(
    //     array('customerName' => 'Online Diecast Creations Co.', 'productName' => '1917 Grand Touring Sedan', 'productLine' => 'Vintage Cars', 'orderDate' => '2003-01-06 00:00:00', 'orderDay' => '6', 'orderMonth' => '1', 'orderYear' => '2003', 'orderQuarter' => '1', 'dollar_sales' => '4080'),
    //     array('customerName' => 'Online Diecast Creations Co.', 'productName' => '1911 Ford Town Car', 'productLine' => 'Vintage Cars', 'orderDate' => '2003-01-06 00:00:00', 'orderDay' => '6', 'orderMonth' => '1', 'orderYear' => '2003', 'orderQuarter' => '1', 'dollar_sales' => '2754.5'),
    //     array('customerName' => 'Online Diecast Creations Co.', 'productName' => '1932 Alfa Romeo 8C2300 Spider Sport', 'productLine' => 'Vintage Cars', 'orderDate' => '2003-01-06 00:00:00', 'orderDay' => '6', 'orderMonth' => '1', 'orderYear' => '2003', 'orderQuarter' => '1', 'dollar_sales' => '1660.12'),
    //     array('customerName' => 'Online Diecast Creations Co.', 'productName' => '1936 Mercedes Benz 500k Roadster', 'productLine' => 'Vintage Cars', 'orderDate' => '2003-01-06 00:00:00', 'orderDay' => '6', 'orderMonth' => '1', 'orderYear' => '2003', 'orderQuarter' => '1', 'dollar_sales' => '1729.21'),
    //     array('customerName' => 'Blauer See Auto, Co.', 'productName' => '1932 Model A Ford J-Coupe', 'productLine' => 'Vintage Cars', 'orderDate' => '2003-01-09 00:00:00', 'orderDay' => '9', 'orderMonth' => '1', 'orderYear' => '2003', 'orderQuarter' => '1', 'dollar_sales' => '2701.5'),
    //     array('customerName' => 'Blauer See Auto, Co.', 'productName' => '1928 Mercedes-Benz SSK', 'productLine' => 'Vintage Cars', 'orderDate' => '2003-01-09 00:00:00', 'orderDay' => '9', 'orderMonth' => '1', 'orderYear' => '2003', 'orderQuarter' => '1', 'dollar_sales' => '4343.56'),
    //     array('customerName' => 'Blauer See Auto, Co.', 'productName' => '1939 Chevrolet Deluxe Coupe', 'productLine' => 'Vintage Cars', 'orderDate' => '2003-01-09 00:00:00', 'orderDay' => '9', 'orderMonth' => '1', 'orderYear' => '2003', 'orderQuarter' => '1', 'dollar_sales' => '1463.8500000000001'),
    //     array('customerName' => 'Blauer See Auto, Co.', 'productName' => '1938 Cadillac V-16 Presidential Limousine', 'productLine' => 'Vintage Cars', 'orderDate' => '2003-01-09 00:00:00', 'orderDay' => '9', 'orderMonth' => '1', 'orderYear' => '2003', 'orderQuarter' => '1', 'dollar_sales' => '2040.1000000000001'),
    //     array('customerName' => 'Vitachrome Inc.', 'productName' => '1937 Lincoln Berline', 'productLine' => 'Vintage Cars', 'orderDate' => '2003-01-10 00:00:00', 'orderDay' => '10', 'orderMonth' => '1', 'orderYear' => '2003', 'orderQuarter' => '1', 'dollar_sales' => '3726.45'),
    //     array('customerName' => 'Vitachrome Inc.', 'productName' => '1936 Mercedes-Benz 500K Special Roadster', 'productLine' => 'Vintage Cars', 'orderDate' => '2003-01-10 00:00:00', 'orderDay' => '10', 'orderMonth' => '1', 'orderYear' => '2003', 'orderQuarter' => '1', 'dollar_sales' => '1768.3300000000002')
    // );
    DataTables::create(array(
        "dataSource" => $this->dataStore("employees"),
        "themeBase" => "bs4", // Optional option to work with Bootsrap 4
        "cssClass" => array(
            "table" => "table table-striped table-bordered"
        )
    ));
    ?>
</div>

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