datatables ajax example

html

<table id="city-grid" cellpadding="0" cellspacing="0" border="0"
                   class="table table-striped table-bordered">
                <thead>
                <tr>
                    <th>ID</th>
                    <th>Название</th>
                </tr>
                </thead>
                <tbody>
                </tbody>
            </table>

js

CityTable = jQuery('#city-grid').dataTable({
        "bProcessing": true,
        "bServerSide": true,
        "bPaginate": true,
        "iDisplayLength": 10,
        "bAutoWidth": true,
        "bFilter": false,
        "sAjaxSource": '/datatables/city/',
        "aoColumnDefs": [
            {"aTargets": [0], "mDataProp": "ID", "sType": "numeric"},
            {"aTargets": [1], "mDataProp": "Name", "sType": "string"}
        ],
        "fnServerData": function (sSource, aoData, fnCallback) {
            jQuery.getJSON(sSource, aoData, function (json) {
                fnCallback(json)
            });
        },
        "fnRowCallback": function (nRow, aData, iDisplayIndex) {
         //пока ничего
        },
        "fnInitComplete": function (oSettings, json) {
          //пока ничего
        },
        "fnCreatedRow": function (nRow, aData, iDataIndex) {
          //пока ничего
        }
    });

php

//$f3->get('GET.iDisplayStart') - это $_GET['iDisplayStart']
$aColumns = array('ID','Name');
$query = 'SELECT SQL_CALC_FOUND_ROWS ID,Name FROM cities ';
        $sLimit = "";
        if (!is_null($_GET['iDisplayStart']) && ($_GET['iDisplayLength'] != '-1')) {
            $sLimit = " LIMIT " . intval($_GET['iDisplayStart']) . ", " .
                intval($_GET['iDisplayLength']);
        }
        $sOrder = "";
        if (!is_null($_GET['iSortCol_0'])) {
            $sOrder = "ORDER BY  ";
            for ($i = 0; $i < intval($_GET['iSortingCols']); $i++) {
                if ($_GET['bSortable_' . intval($_GET['iSortCol_' . $i]] == "true") {
                    $sOrder .= $aColumns[intval($_GET['iSortCol_' . $i])] . ($_GET['sSortDir_' . $i] === 'asc' ? ' asc' : ' desc') . ", ";
                }
            }
            $sOrder = substr_replace($sOrder, "", -2);
            if ($sOrder == "ORDER BY") {
                $sOrder = "";
            }
        }

        $query = $query. $sOrder . $sLimit;
        $sth = Dbh::getInstance()->prepare($query);
        $sth->execute();
        $rResultFilterTotal = Dbh::getInstance()->query("SELECT FOUND_ROWS()")->fetchColumn();
        $result = $sth->fetchAll(PDO::FETCH_OBJ);
        foreach ($result as $row) {

        }
        $iFilteredTotal = $rResultFilterTotal;
        $iTotal = Dbh::getInstance()->query("SELECT COUNT(`ID`) FROM cities")->fetchColumn();

        $output = array(
            "sEcho" => intval($_GET['sEcho']),
            "iTotalRecords" => $iTotal,
            "iTotalDisplayRecords" => $iFilteredTotal,
            "aaData" => (array)$result,
        );
        echo json_encode($output);