Pagination/Search/Sorting not working with serverSide: true

Da9L

First of all. If i just set serverSide to false, and keep my ajax call, everything works. As soon as i set serverSide to true, the initial data is shown fine, but i cant search, change page, or oder anything. I can see that there is an ajax request happening when i click on page 2 as an example, and i can see that the ajax response holds the correct data for page 2.. But its not shown in the table. All i see is "Processing..." below it. Its like the table isn't updated, and I'm wondering if i need to implement some redrawing of the table, when a response from ajax has happened?

Heres my JS:

var datatable = $(\'#datatable\').DataTable({
        stateSave: false,
        serverSide: true,
        processing: true,
        order: [[ 0, "asc" ]],
        lengthMenu: [ [10, 25, 50, -1], [10, 25, 50, "Alle"] ], 
        ajax: { 
            url: "/ajax/get_users",
        },  
        columns: [
            { data: "id", title: "Id", "width": "5%", "searchable": true, "type": "num" },
            { data: "name", title: "Name", "width": "95%", "searchable": true, "type": "html"},
        ],                      
    });

Heres get_users.php (Simplified quite a bit in the name of finding out what is wrong) :

$sql_users_count = "SELECT * FROM users GROUP BY id ORDER BY id ";
if (!$result2 = mysqli_query($conn,$sql_users_count)) {
    die('There was an error running the query [' . $conn->error . ']');
}

$sql_users = "SELECT * FROM users GROUP BY id ORDER BY id ";
if(isset($_GET["length"])) {
    $sql_users .= "LIMIT ".$_GET["length"]." ";
}
if(isset($_GET["start"])) {
    $sql_users .= "OFFSET ".$_GET["start"].";";
}

if (!$result = mysqli_query($conn,$sql_users)) {
    die('There was an error running the query [' . $conn->error . ']');
}

$data_obj["draw"] = 1;
$data_obj["recordsTotal"] = $result2->num_rows;
$data_obj["recordsFiltered"] = $result2->num_rows;
$data_obj["data"] = array();

$i = 0;
while($row = $result->fetch_assoc()) {
    $data_obj["data"][$i]["id"] = $row["user_id"];            
    $data_obj["data"][$i]["name"] = $row["title"];    
       
    $i++;
}

echo json_encode($data_obj);

If you look at get_users.php i get why sorting and and searching isn't working. But as you can see i have LIMIT and OFFSET on my mysql query and looking at the response i get from get_users.php when changing page, it seems to get new users in the data array, but the table is simply not updated.

Any help?

bur

You have $data_obj["draw"] = 1;. This should be set to (int)$_GET["draw"]. Not sure if this the cause of your problem, but it may be.

Da9L

That was totally my problem, and the moment i saw your comment it made total sense to me. Not sure why i hadn't connected that part, because i did notice that the draw parameter was changing when i changed page.

Thank you!

bur

Glad to help!