Filtering DataTables server side processing

Question

I need help with jquery plugin DataTables server side processing and filtering.

I just can't figure out how to get the data that is displayed to be limited to only a specific post_id.

Currently when there's a request to view data, the user is linked to something like www.example-site.com/post?id=123 and the goal is to have DataTables only display the data for id=123.

I've only been able to set it up just pull everything from a particular table- I don't know how to tell datatables to just use the filter by the id.

Here is the html:

<script type="text/javascript" language="javascript" class="init">
  $(document).ready(function() {
  $('#research').DataTable( {
    "processing": true,
    "serverSide": true,
    "ajax": "server_processing.php"
  } );
} );
</script>

</head>

<body>

<table id="research" class="display" cellspacing="0" width="100%">

<thead>
    <tr>
        <th>Title</th>
        <th>Link</th>
        <th>Description</th>
        <th>Type</th>
        <th>Posted</th>
    </tr>
</thead>    
</table>

And here is the SQL:

<?php

$table = 'example_table';

$primaryKey = 'id';

$columns = array(
  array( 'db' => 'title', 'dt' => 0 ),
  array( 'db' => 'link',  'dt' => 1 ),
  array( 'db' => 'description',   'dt' => 2 ),
  array( 'db' => 'category',     'dt' => 3 ),
  array(
    'db'        => 'post_date',
    'dt'        => 4,
    'formatter' => function( $d, $row ) {
        return date( 'M d, Y', strtotime($d));
      }
   )
);

$sql_details = array(
  'user' => '*USER*',
  'pass' => '*PW*',
  'db'   => 'example_database',
  'host' => 'localhost'
);

require( 'ssp.class.php' );

echo json_encode(
   SSP::simple( $_GET, $sql_details, $table, $primaryKey, $columns ) 
);

?>

I'm not a programmer by trade, but I can usually figure out what to do by searching around online. I'm at a total loss on this... any help would be much appreciated.

score 0 · Accepted Answer · edited May 23 '17 at 12:00

0

First, you'll need to get the id query string variable in Javascript using either pure Javascript or the jQuery-URL-Parser plugin.
Then, you need to pass this variable to the ajax option. For example: "ajax": "server_processing.php?id=" + id

Finally, you'll need to filter your result set in server_processing.php by this query string variable using $_SERVER['QUERY_STRING']:

$query_string = $_SERVER['QUERY_STRING'];
parse_str($query_string, $query_string_array);

...

require( 'ssp.class.php' );
$where = "id = '".htmlspecialchars($query_string_array['id'], ENT_QUOTES)."'";

echo json_encode(
  SSP::simple( $_GET, $sql_details, $table, $primaryKey, $columns, $where )
);

You can find out more on $_SERVER here.

edited May 23 '17 at 12:00

Community

1
1

answered Feb 21 '16 at 08:04

Khalid T.

10,039
5
45
53

I've accomplished the following: (1) retrieve `id` query string variable with purl.js; (2) set-up `ajax` option to receive the variable: `"server_processing.php?id="+id` ;(3) set-up **server_processing.php** to filter the query string variable. I know for sure that I'm correctly retrieving the `id` query string and that **server_processing.php** can filter the query string. What I don't know is whether I'm correctly passing the `id` variable to the ajax option and whether the ajax is correctly sending the query string. Is there a way I could check if the ajax is sending the query? – Feb 22 '16 at 00:00
....I noticed my script blocker (ublock) was on. It was blocking the request. Your solution worked- thank you. – Feb 22 '16 at 00:06
Hi, I'm solving similar task. Is this solution secure? Isn't it better to send this informations from step 1 to server_processing script by ajax call in DataTables inicialisation script like this?: "data": function(datas){ var filter1 = $('#filter1').val(); var filter2 = $('#filter2').val(); if(filter1) datas.filter1=filter1; – culter Oct 25 '18 at 14:04

score -2 · Answer 2 · edited Feb 27 '17 at 06:56

$(document).ready(function () {
    $('#example').DataTable({
        "order": [[ 0, 'desc' ]],  // https://datatables.net/reference/option/order  cara agar default descending 
        "iDisplayLength": 10,
        "aLengthMenu": [[3,5, 10, 25, 50, -1], [3,5, 10, 25, 50, "All"]],
        "processing": true,
        "serverSide": true,
        "ajax": {
      "url": 'prosesmydevice.php',
      "type": "GET",
      "data": {"stdev_id": get_id}
        },
        "scrollX":        true,
        "scrollCollapse": true,
        "paging":         true,
        "stateSave": true,

if (isset($_SERVER['HTTP_X_REQUESTED_WITH']) && ( $_SERVER['HTTP_X_REQUESTED_WITH'] == 'XMLHttpRequest' )) {
    
    //$getidloc=$_GET['action'];
    $stdev_id=$_GET['stdev_id'];

    // nama table
    $table = 'stdevice';

    // Table's primary key
    $primaryKey = 'id';

    // Array of database columns which should be read and sent back to DataTables.
    // The `db` parameter represents the column name in the database, while the `dt`
    // parameter represents the DataTables column identifier. In this case simple
    // indexes

    $columns = array(   array( 'db' => 'devn.dev_name', 'dt' => 0, 'field' => 'dev_name' ),
                        array( 'db' => 'std.dev_model', 'dt' => 1, 'field' => 'dev_model' ), 
                        array( 'db' => 'std.dev_serial', 'dt' => 2, 'field' => 'dev_serial' ),

Filtering DataTables server side processing

2 Answers2