title: Datatables joining tables search and order stuck with codeigniter
body: <p>I was follow <a href="http://mbahcoding.com/php/codeigniter/codeigniter-server-side-ajax-crud-modal-validation.html" rel="nofollow noreferrer">this</a> step to learn datatables with codeigniter. But I'm getting some error with joining two tables <a href="https://i.stack.imgur.com/JaMFN.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/JaMFN.png" alt="tables" /></a>
  to use search at datatables. This is my error, <a href="https://i.stack.imgur.com/kY8i7.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/kY8i7.png" alt="error" /></a></p>
  <p>And then as I see at the <a href="http://datatables.net/manual/tech-notes/7" rel="nofollow noreferrer">documentation</a> AJAX error, the resolution is search finding error server at network request browser. And I'm getting 500 Internal Server Error. This is I copied the response body for the fault.</p>
  <blockquote>
  <p>Error Number: 42000/1064</p>
  <p>You have an error in your SQL syntax; check the manual that corresponds to  &gt;your MySQL server version for the right syntax to use near 'as &gt;<code>nm_propinsi</code> LIKE '%c%' ESCAPE '!' ) ORDER BY <code>id_kota</code> DESC LIMIT 10' at line 7</p>
  </blockquote>
  <pre><code>SELECT * FROM `kota` as `k` LEFT JOIN `propinsi` as `p` ON `p`.`id_propinsi` = `k`.`id_propinsi` WHERE ( `k`.`id_kota` LIKE '%c%' ESCAPE '!' OR `k`.`nm_kota` LIKE '%c%' ESCAPE '!' OR `p`.`nm_propinsi` as `nm_propinsi` LIKE '%c%' ESCAPE '!' ) ORDER BY `id_kota` DESC LIMIT 10
  </code></pre>
  <p>The fault is on my query to get list data by <code>LIKE</code> for datatables.</p>
  <p>This is my model to create query search for datatables,</p>
  <pre><code>var $column = array('k.id_kota','k.nm_kota', 'p.nm_propinsi as nm_propinsi'); //set column field database for order and search
  var $order = array('id_kota' =&gt; 'desc'); // default order 
  function get_datatables(){
    $this-&gt;_get_datatables_query();
    if($_POST['length'] != -1)
    $this-&gt;db-&gt;limit($_POST['length'], $_POST['start']);
    $query = $this-&gt;db-&gt;get();
    return $query-&gt;result();
  }
  private function _get_datatables_query(){
    
    $this-&gt;db-&gt;from('kota as k');
    $this-&gt;db-&gt;join('propinsi as p', 'p.id_propinsi = k.id_propinsi');
  
    $i = 0;
  
    foreach ($this-&gt;column as $item) // loop column 
    {
       if($_POST['search']['value']) // if datatable send POST for search
       {
          
          if($i===0) // first loop
          {
             $this-&gt;db-&gt;group_start(); // open bracket. query Where with OR clause better with bracket. because maybe can combine with other WHERE with AND. 
             $this-&gt;db-&gt;like($item, $_POST['search']['value']);
          }
          else
          {
             $this-&gt;db-&gt;or_like($item, $_POST['search']['value']);
          }
  
          if(count($this-&gt;column) - 1 == $i) //last loop
             $this-&gt;db-&gt;group_end(); //close bracket
       }
       $column[$i] = $item; // set column array variable to order processing
       $i++;
    }
    
    if(isset($_POST['order'])) // here order processing
    {
       $this-&gt;db-&gt;order_by($column[$_POST['order']['0']['column']], $_POST['order']['0']['dir']);
    } 
    else if(isset($this-&gt;order))
    {
       $order = $this-&gt;order;
       $this-&gt;db-&gt;order_by(key($order), $order[key($order)]);
    }
  }
  </code></pre>
  <p>My function controller to get AJAX JSON,</p>
  <pre><code>public function list_kota(){
      $this-&gt;load-&gt;model(&quot;kota_model&quot;);
      $list = $this-&gt;kota_model-&gt;get_datatables();
      $data = array();
      $no = $_POST['start'];
      foreach ($list as $ko) {
          $no++;
          $row = array();
          $row[] = $ko-&gt;id_kota;
          $row[] = $ko-&gt;nm_kota;
          $row[] = $ko-&gt;nm_propinsi;
  
          //add html for action
          $row[] = '&lt;a class=&quot;btn btn-sm btn-primary&quot; href=&quot;javascript:void()&quot; title=&quot;Edit&quot; onclick=&quot;edit_kota('.&quot;'&quot;.$ko-&gt;id_kota.&quot;'&quot;.')&quot;&gt;&lt;i class=&quot;glyphicon glyphicon-pencil&quot;&gt;&lt;/i&gt; Edit&lt;/a&gt;
                &lt;a class=&quot;btn btn-sm btn-danger&quot; href=&quot;javascript:void()&quot; title=&quot;Hapus&quot; onclick=&quot;delete_kota('.&quot;'&quot;.$ko-&gt;id_kota.&quot;'&quot;.')&quot;&gt;&lt;i class=&quot;glyphicon glyphicon-trash&quot;&gt;&lt;/i&gt; Delete&lt;/a&gt;';
      
          $data[] = $row;
      }
  
      $output = array(
          &quot;draw&quot; =&gt; $_POST['draw'],
          &quot;recordsTotal&quot; =&gt; $this-&gt;kota_model-&gt;count_all(),
          &quot;recordsFiltered&quot; =&gt; $this-&gt;kota_model-&gt;count_filtered(),
          &quot;data&quot; =&gt; $data,
          );
      //output to json format
      echo json_encode($output);
  }
  </code></pre>
  <p>What should I do now? Any suggestion?</p>