Who has an idea how to setup the server-side datatables using ssp.class.php please?

Who has an idea how to setup the server-side datatables using ssp.class.php please?

devianvisualsdevianvisuals Posts: 1Questions: 1Answers: 0

Not working search bar, sorting, pagination and showing 0 to 0 of 0 entries but showing data from database
Can someone help me please?

Ajax Result:
{"draw":0,"recordsTotal":19,"recordsFiltered":19,"data":[["Adrian Sellote","9260354100","1234","Philippines","6329","6th Aug 22"],["Joselle Salpocial","9260354100","1234","Philippines","6329","6th Aug 22"],["Robin B. Sellote","9260354100","1234","Philippines","6239","6th Aug 22"],["asdfa","8498465464","1032132","Philippines","6465546","6th Aug 22"],["asdf","5498464654","131313","Philippines","65646","6th Aug 22"],["asdf","3213123123","asdf1321321","Philippines","31313","6th Aug 22"],["asdfasdf","6846464654","13213213","Philippines","3132131","6th Aug 22"],["asdf","5465465465","121233","Philippines","3213","6th Aug 22"],["asdf","5454621321","asdfasdf1","Philippines","51312","6th Aug 22"],["asdf","3351312321","asdfasd32","Philippines","23123","6th Aug 22"],["asdf","3132231321","23113","Philippines","211321","6th Aug 22"],["asdf","5465465465","13213","Philippines","312321","6th Aug 22"],["asdf","1313213212","13213213","Philippines","121213","6th Aug 22"],["asdfasfd","6546454654","6464646","Philippines","145121321","6th Aug 22"],["asdf","5465654654","321321321","Philippines","31321","6th Aug 22"],["asdfasdf","5464645465","13212313","Philippines","321321","6th Aug 22"],["asdfasfd","3213132132","1321321321","Philippines","2133213","6th Aug 22"],["asdfasdasf","3132132132","32132131","Philippines","321321231","6th Aug 22"],["asdfasdasdfasdf","2121312132","1321321","Philippines","21321","6th Aug 22"]]}

HTML

<link rel="stylesheet" href="https://cdn.datatables.net/1.12.1/css/dataTables.bootstrap5.min.css">
<script src="https://cdnjs.cloudflare.com/ajax/libs/jquery/3.6.0/jquery.min.js"></script>
<script src="https://cdn.datatables.net/1.12.1/js/jquery.dataTables.min.js"></script>
<script src="https://cdn.datatables.net/1.12.1/js/dataTables.bootstrap5.min.js"></script>

<table class="table table-striped table-bordered responsive display no-wrap" id="example" style="width: 100%">
    <thead>
        <tr>
            <th>Customer Name</th>
            <th>Contact Number</th>
            <th>Address</th>
            <th>Country</th>
            <th>Postal Code</th>
            <th>Date Created</th>
        </tr>
    </thead>
    <tbody></tbody>
    <tfoot>
        <tr>
            <th>Customer Name</th>
            <th>Contact Number</th>
            <th>Address</th>
            <th>Country</th>
            <th>Postal Code</th>
            <th>Date Created</th>
        </tr>
    </tfoot>
</table>

JQuery

$(document).ready(function() {
    $('#example').DataTable({
        processing: true,
        serverSide: true,
        ajax: '/pages/collections/datatables-server-side/index-action.php',
        paging: true,
        ordering: true,
        info: true,
        order: [
            [0, 'asc']
        ],
        "columnDefs": [
            { "visible": false, "targets": [] }
        ],
        bDestroy: true,
        
    });
 
    table.buttons().container().appendTo( '#example_wrapper .col-md-6:eq(0)' );

    $('#example tbody').on('click', 'tr', function () {
        if ($(this).hasClass('selected')) {
            $(this).removeClass('selected');
        } else {
            table.$('tr.selected').removeClass('selected');
            $(this).addClass('selected');
        }
    });
});

Server-side Processing Script (index-action.php)
ajax: '/pages/collections/datatables-server-side/index-action.php'

<?php

$table = 'customer_tbl';
$primaryKey = 'id';

$columns = array(
    array( 'db' => '_customer_name', 'dt' => 0 ),
    array( 'db' => '_contact_number', 'dt' => 1 ),
    array( 'db' => '_address', 'dt' => 2 ),
    array( 'db' => '_country', 'dt' => 3 ),
    array( 'db' => '_postal_code', 'dt' => 4 ),
    array(
        'db'        => '_created_at',
        'dt'        => 5,
        'formatter' => function( $d, $row ) {
            return date( 'jS M y', strtotime($d));
        }
    )
);

$sql_details = array(
    'user' => 'root',
    'pass' => '',
    'db'   => 'php_db',
    'host' => 'localhost'
);

require( 'ssp.class.php' );
echo json_encode(
    SSP::simple( $_GET, $sql_details, $table, $primaryKey, $columns )
);

ssp.class.php
https://github.com/DataTables/DataTablesSrc/blob/master/examples/server_side/scripts/ssp.class.php

```
static function simple ( $request, $conn, $table, $primaryKey, $columns )
{
$bindings = array();
$db = self::db( $conn );

// Build the SQL query string from the request
$limit = self::limit( $request, $columns );
$order = self::order( $request, $columns );
$where = self::filter( $request, $columns, $bindings );

// Main query to actually get the data
$data = self::sql_exec( $db, $bindings,
    "SELECT `".implode("`, `", self::pluck($columns, 'db'))."`
        FROM `$table`
        $where
        $order
        $limit"
);

// Data set length after filtering
$resFilterLength = self::sql_exec( $db, $bindings,
    "SELECT COUNT(`{$primaryKey}`)
        FROM   `$table`
        $where"
);
$recordsFiltered = $resFilterLength[0][0];

// Total data set length
$resTotalLength = self::sql_exec( $db,
    "SELECT COUNT(`{$primaryKey}`)
        FROM   `$table`"
);
$recordsTotal = $resTotalLength[0][0];

/*
    * Output
    */
return array(
    "draw"            => isset ( $request['draw'] ) ?
        intval( $request['draw'] ) :
        0,
    "recordsTotal"    => intval( $recordsTotal ),
    "recordsFiltered" => intval( $recordsFiltered ),
    "data"            => self::data_output( $columns, $data )
);

}

Sign In or Register to comment.