联接表的数据表搜索和订单被codeigniter卡住 [英] Datatables joining tables search and order stuck with codeigniter
问题描述
我一直关注 在数据表上使用搜索.这是我的错误,
I was follow to use search at datatables. This is my error,
然后,正如我在文档 AJAX错误中看到的那样,解决方案是搜索在网络请求浏览器中查找错误服务器.而且我收到500 Internal Server Error.这是我复制故障的响应正文.
And then as I see at the documentation 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.
错误号:42000/1064
Error Number: 42000/1064
您的SQL语法有错误;检查与您的MySQL服务器版本相对应的手册,以获取正确的语法,以在'as> nm_propinsi
LIKE'%c%'ESCAPE'!'附近使用. )ORDER BY id_kota
DESC LIMIT 10'at line 7
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
获取数据表的列表数据.
The fault is on my query to get list data by LIKE
for datatables.
这是我的模型,用于为数据表创建查询搜索,
This is my model to create query search for datatables,
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,
My function controller to get 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[] = '<a class="btn btn-sm btn-primary" href="javascript:void()" title="Edit" onclick="edit_kota('."'".$ko->id_kota."'".')"><i class="glyphicon glyphicon-pencil"></i> Edit</a>
<a class="btn btn-sm btn-danger" href="javascript:void()" title="Hapus" onclick="delete_kota('."'".$ko->id_kota."'".')"><i class="glyphicon glyphicon-trash"></i> Delete</a>';
$data[] = $row;
}
$output = array(
"draw" => $_POST['draw'],
"recordsTotal" => $this->kota_model->count_all(),
"recordsFiltered" => $this->kota_model->count_filtered(),
"data" => $data,
);
//output to json format
echo json_encode($output);
}
我现在该怎么办?有什么建议吗?
What should I do now? Any suggestion?
推荐答案
根据您的模型,您可以像下面一样对其进行修改
based on your models, you can modify it just like below
private function _get_datatables_query($term=''){ //term is value of $_REQUEST['search']['value']
$column = array('k.id_kota','k.nm_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();
}
这篇关于联接表的数据表搜索和订单被codeigniter卡住的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!