Serach and ordering is not working Datatables Server Side Processing (Codeigniter)?

Question

I'am trying to display same data using datatables server side processing at Codeigniter.I should use 'where' clause and i'am using Emran ul hadi script class.(Visit https://emranulhadi.wordpress.com/2014/06/05/join-and-extra-condition-support-at-datatables-library-ssp-class/#comment-196). My controller script :

        $sql_details = array(
         'user' => 'root',
         'pass' => '',
         'db'   => 'kreatx',
         'host' => 'localhost'
       );

       $index = $this->uri->segment(3);
       $table = 'user';

       $columns = array(
         array('db' => 'Name', 'dt' => 0),
         array('db' => 'Lastname', 'dt' => 1),
         array('db' => 'Email', 'dt' => 2),
         array('db' => 'Username', 'dt' => 3),
         array('db' => 'Password', 'dt' => 4)
       );

       $primaryKey = 'ID';

       $this->load->model('employees_model');
       $department = $this->employees_model->get_department($index);

       require( 'SSP.php' );
       $where = 'Department = '.$index.'';
       echo json_encode(
         SSP::complex( $_GET, $sql_details, $table, $primaryKey, $columns, 
                      null ,$where )
        );

My view script :

    $('#employees').DataTable({
       "responsive":true,  
       "processing":true,  
       "serverSide":true,
       "searching":true,
       "ordering":true, 
       "order":[],  
       "ajax":{  
            url:"<?php echo base_url() . 
                 'employees/get_employees/'.$index.''; ?>",  
            type:"POST"  
       },
       "columnDefs":[  
            {  
                 "targets":[4],  
                 "orderable":false,  
            },  
       ],
       });

Table is displaying correctly,with no errors.But search and order does'nt work. If i try to search it just say prrocessing and show the same table. Same problem with ordering. Any sugesstion please ? Thanks !

any console errors?

G_S
– G_S

2018-03-18 14:30:27 +00:00
Commented Mar 18, 2018 at 14:30 — G_S
– G_S, Commented Mar 18, 2018 at 14:30
@G_S No ,it does'nt show errors .

Armand Rexhmati
– Armand Rexhmati

2018-03-18 14:34:46 +00:00
Commented Mar 18, 2018 at 14:34 — Armand Rexhmati
– Armand Rexhmati, Commented Mar 18, 2018 at 14:34

Jazmy · Accepted Answer · 2019-01-03 19:06:51Z

On your script you set "serverside" parameter to "true"

That means all the filtering and sorting needs to be in your php script. Every time you change the order or try to apply a filter, the dataTables javascript will send an ajax call to the php page you have listed under the ajax parameter. The ajax call will include everything you need inside of get or post variables that you can parse.

You need to write the code in php to parse the URL parameters and dynamically create a sql query that filters your database results.

I haven't done this in Codeignitor before but this is a quick example of what I would try in Laravel (note, you need to hard code your own database columns or if you're advanced then you can loop the code into an array of your database column names)

    public function MyExampleJson(Request $request)
  {
    $len = $_GET['length'];
    $start = $_GET['start'];
    $select = "SELECT *,1,2 ";
    $presql = " FROM entities a ";
    $whereused = false;
    if($_GET['search']['value']) {
      $presql .= " WHERE (id LIKE '%". $_GET['search']['value']."%' ";
      $presql .= " OR column01 LIKE '%". $_GET['search']['value']."%' ";
      $presql .= " OR column02 LIKE '%". $_GET['search']['value']."%' ";
      $presql .= " OR column03 LIKE '%". $_GET['search']['value']."%' ";
      $presql .= " OR column04 LIKE '%". $_GET['search']['value']."%' ";
      $presql .= ") ";
      $whereused = true;
    }

    $orderby = "";
    $columns = array('column01','column02','column03','column04');
    $order = $columns[$request->input('order.0.column')];
    $dir = $request->input('order.0.dir');
    $columnsearcharray = $request->columns;

    foreach ($columnsearcharray as $key => $value)
    {
      if ($value['search']['value']) {
        if ($whereused) {
          $presql .= " AND ";
        } else {
          $presql .= " WHERE ";
          $whereused = true;
        }
        $presql .= $columns[$key] . " LIKE '%" . $value['search']['value'] . "%' ";
      }
    }
    $orderby = "Order By " . $order . " " . $dir;
    $sql = $select.$presql.$orderby." LIMIT ".$start.",".$len;
    $qcount = DB::select("SELECT COUNT(a.id) c".$presql);
    $count = $qcount[0]->c;
    $results = DB::select($sql);
    $ret = [];
    foreach ($results as $row) {
      $r = [];
      foreach ($row as $value) {
        $r[] = $value;
      }
      $ret[] = $r;
    }
    $ret['data'] = $ret;
    $ret['recordsTotal'] = $count;
    $ret['iTotalDisplayRecords'] = $count;
    $ret['recordsFiltered'] = count($ret);
    $ret['draw'] = $_GET['draw'];
    echo json_encode($ret);
  }

Collectives™ on Stack Overflow

Serach and ordering is not working Datatables Server Side Processing (Codeigniter)?

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related