Datatables server side processing

How to implement: Datatables server side processing. Code Length / limit changes, the search bar filter and sorting mechanisms.

Length / Limit

You can select the amount of data entries, which you want to display. That has to be handled on the server side.

"lengthMenu": [ [10, 25, 50, -1], [10, 25, 50, "Alle"] ],
Datatables server side processing of the length selection
Datatables server side processing of the length selection

With the following code you can add the limit command with the user specified length to the sql query:

if($_POST["length"] != -1){
  $sqlQuery .= ' LIMIT ' . $_POST['start'] . ', ' . $_POST['length'];
 }

With chrome developer tools you can debug issues. Here you can see that the action.php api is getting called with the length 50, as the user has clicked on.

Debug in Developer Tools
Debug in Developer Tools

Search bar / filter

The search bar is enabled by default in datatables.

Datatables Search Bar Filter
Datatables Search Bar Filter

With the following code you can add it to your existing sql command:

if(!empty($_POST["search"]["value"])){
  $sqlQuery .= ' AND stockname LIKE "%'.$_POST["search"]["value"].'%" ';
}

In the search console you can see your own user input character for character appearing as single action.php api calls. The search[value] field contains your search keyword.

The frontend shows in real time the result of the updated sql command.

Sorting

You can sort the table by clicking on the table header column names. Every column you can order ascending or descending. This has to be in your server side api program code.

If you are calculating some columns, you can do this calculation within the sql command.

$sqlQuery = "SELECT stockname, price, buy, sell, userlimits.id AS userlimitID, stocks.url AS stockURL, userid, (price / buy) AS buyPercent, (price / sell) AS sellPercent FROM userlimits LEFT JOIN users ON userlimits.userid = users.id LEFT JOIN stocks on userlimits.stockid = stocks.id WHERE userid = '".$_SESSION["userid"]."'";

The processing of the user selected columns and order mechanism can be coded that way:

At the search console you will see the order[0][column] variable, which contains the number of the column, which you want to order. order[0][dir] is the variable which contains ascending or descending.

How to disable server side sorting?

You can’t disable just server side sorting. You have to make a decision and stay with it. You can’t pick for the search bar / number limit of the data entries or the sorting mechanism, if you want to use the client or server version.

https://stackoverflow.com/questions/45261832/how-to-disable-server-side-sorting-with-datatables

Kommentar verfassen

Deine E-Mail-Adresse wird nicht veröffentlicht. Erforderliche Felder sind mit * markiert

Nach oben scrollen