php – 数据表连接表搜索和命令与codeigniter

我遵循this步骤学习使用codeigniter的数据表.但是我加入两个表

然后正如我在documentation AJAX错误中看到的那样,解决方案是在网络请求浏览器中搜索错误服务器.而且我得到500内部服务器错误.这是我复制了故障的响应体.

Error Number: 42000/1064

You have an error in your SQL syntax; check the manual that corresponds to >your MySQL server version for the right syntax to use near ‘as >nm_propinsi LIKE ‘%c%’ ESCAPE ‘!’ ) ORDER BY id_kota DESC LIMIT 10′ at line 7

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

我的查询出现的错误是通过LIKE获取数据表的列表数据.

这是我为数据表创建查询搜索的模型,

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' => 'desc'); // default order 
function get_datatables(){
  $this->_get_datatables_query();
  if($_POST['length'] != -1)
  $this->db->limit($_POST['length'],$_POST['start']);
  $query = $this->db->get();
  return $query->result();
}
private function _get_datatables_query(){

  $this->db->from('kota as k');
  $this->db->join('propinsi as p','p.id_propinsi = k.id_propinsi');

  $i = 0;

  foreach ($this->column as $item) // loop column 
  {
     if($_POST['search']['value']) // if datatable send POST for search
     {

        if($i===0) // first loop
        {
           $this->db->group_start(); // open bracket. query Where with OR clause better with bracket. because maybe can combine with other WHERE with AND. 
           $this->db->like($item,$_POST['search']['value']);
        }
        else
        {
           $this->db->or_like($item,$_POST['search']['value']);
        }

        if(count($this->column) - 1 == $i) //last loop
           $this->db->group_end(); //close bracket
     }
     $column[$i] = $item; // set column array variable to order processing
     $i++;
  }

  if(isset($_POST['order'])) // here order processing
  {
     $this->db->order_by($column[$_POST['order']['0']['column']],$_POST['order']['0']['dir']);
  } 
  else if(isset($this->order))
  {
     $order = $this->order;
     $this->db->order_by(key($order),$order[key($order)]);
  }
}

我的函数控制器获取AJAX JSON,

public function list_kota(){
    $this->load->model("kota_model");
    $list = $this->kota_model->get_datatables();
    $data = array();
    $no = $_POST['start'];
    foreach ($list as $ko) {
        $no++;
        $row = array();
        $row[] = $ko->id_kota;
        $row[] = $ko->nm_kota;
        $row[] = $ko->nm_propinsi;

        //add html for action
        $row[] = '

private function _get_datatables_query($term=''){ //term is value of $_REQUEST['search']['value']
    $column = array('k.id_kota','p.nm_propinsi');
    $this->db->select('k.id_kota,k.nm_kota,p.nm_propinsi');
    $this->db->from('kota as k');
    $this->db->join('propinsi as p','p.id_propinsi = k.id_propinsi','left');
    $this->db->like('k.id_kota',$term);
    $this->db->or_like('k.nm_kota',$term);
    $this->db->or_like('p.nm_propinsi',$term);
    if(isset($_REQUEST['order'])) // here order processing
    {
       $this->db->order_by($column[$_REQUEST['order']['0']['column']],$_REQUEST['order']['0']['dir']);
    } 
    else if(isset($this->order))
    {
       $order = $this->order;
       $this->db->order_by(key($order),$order[key($order)]);
    }
}

function get_datatables(){
  $term = $_REQUEST['search']['value'];   
  $this->_get_datatables_query($term);
  if($_REQUEST['length'] != -1)
  $this->db->limit($_REQUEST['length'],$_REQUEST['start']);
  $query = $this->db->get();
  return $query->result(); 
}

function count_filtered(){
  $term = $_REQUEST['search']['value']; 
  $this->_get_datatables_query($term);
  $query = $this->db->get();
  return $query->num_rows();  
}

public function count_all(){
  $this->db->from($this->table);
  return $this->db->count_all_results();  
}