Datatables search not working

hacker

I am using datatables plugin in codeigniter framework. I have a query in model like this

var $column = array('id','name','mobile','address','created_date','designation');
    var $order = array('id' => 'desc'); // default order
    var $table = 'gms_admin';

    private function _get_datatables_query()
    {
        $this->db->select('gms_admin.id,CONCAT(admin_detail.firstname," ",admin_detail.lastname) as `name`,admin_detail.mobile,admin_detail.address,gms_admin.created_date,designation.`name` as designation ');
        $this->db->from($this->table);
        $this->db->join('admin_detail', 'gms_admin.`id` = admin_detail.userid');
        $this->db->join('designation', 'gms_admin.role = designation.`id`');
        $this->db->where('gms_admin.gym_id',$_SESSION['gymid']); 
        $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)]);
        }
    } 

 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();
    }

But this gives me error Column 'id' in where clause is ambiguous

It shows the table clearly but the problem starts only when search is used. What is the wrong in this ?? Can we not use where clause if we are gonna use search feature ?? Please give me some idea or any link with example would be of great help. Thankyou

Edited by Colin - Syntax highlighting. Details on how to highlight code using markdown can be found in this guide.

allan

I'm afraid I can't help you debug your CI code - you'd need to ask on Stack Overflow or a CI specific forum if you have questions about that. However, I'd suggest attempting to log your generated SQL statement (I don't know how to do that in CI, but there must be a way) and seeing what is wrong with it.

Can we not use where clause if we are gonna use search feature ??

I don't see why you wouldn't be able to. Presumably in CI you can have multiple where conditions applied.

Allan