CODEWITHSUNDEEP
jquerydatatabledatatables
Nicky
Nicky

Reputation: 13

How to define datatables server-side ordering manually?

Datatables plugin is using column index for ordering data. But I want to define manually these indexes with names of MySQL column names. I have more than 60 columns and users can change column order in settngs section.

Datatables sends for example:

order[0][column]:"6"
order[0][column]:"desc"

I want to send also column name defined by myself..

Upvotes: 1

Views: 4791

Answers (2)

oscar
oscar

Reputation: 15

Do this in your ajax, send custom var with the name of the column:

    var table = jQuery('#table').DataTable({
    "processing": true,
    "serverSide": true,
    "ajax": {
        url: "/my-url/",     
        "data": function ( d ) {
            if (d.order.length>0){
                var columna = d.columns[d.order[0].column].name
                var tipo_orden = d.order[0].dir
                if (tipo_orden == "desc"){
                    columna = "-"+columna
                }

                d.orden_columna = columna;

            }

        }       
    }, 
});

Then in your php get the order:

<?php
$order_by = $_REQUEST['orden_columna'];
?>

Upvotes: 0

Arka
Arka

Reputation: 591

Using custom http variable can be a choice. But there is a better way. Write an array including your mysql column name with your datatable column index in your server side script.

<?php
$columns = array(
// datatable column index  => database column name
    0 =>'employee_name',
    1 => 'employee_salary',
    2=> 'employee_age'
);
?>

Then you can write sql with column name, just like that.

$sql = "SELECT employee_name, employee_salary, employee_age ";
$sql.=" FROM employee";
$sql.=" ORDER BY ". $columns[$_REQUEST['order'][0]['column']]."   ".$_REQUEST['order'][0]['dir']."   LIMIT ".$_REQUEST['start']." ,".$_REQUEST['length']."   ";

For reference you can refer coderexample

Upvotes: 5

Related Questions