我遵循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();
}
(编辑:李大同)
【声明】本站内容均来自网络,其相关言论仅代表作者个人观点,不代表本站立场。若无意侵犯到您的权利,请及时与联系站长删除相关内容!
|