DataTable Model error

Question

This is my datatable model which combines all the tables that i need, and the console found an error in this code where VehicleNo is ambiguous.

I also need to order by ApplicationNo and distinct the ApplicationNo so that it wont duplicate other table

private function _get_datatables_query(){    
if($this->input->post('Status'))
{                                                           
    $this->db->where('Status', $this->input->post('Status'));
}
$this->db->select('*');
//$this->db->distinct('ApplicationNo');
$this->db->from($this->table);
$this->db->distinct();
$this->db->join('user', 'user.userId = loanapplication.userId');;
$this->db->join('collateraldetails', 'collateraldetails.ApplicationNo = loanapplication.ApplicationNo');
$this->db->join('vehicleinformation', 'vehicleinformation.VehicleNo = collateraldetails.VehicleNo');
$this->db->join('loanrequest', 'loanrequest.ApplicationNo = loanapplication.ApplicationNo');
$this->db->join('loanapproval', 'loanapproval.RequestNo = loanrequest.RequestNo');
$this->db->join('paymentdetails', 'paymentdetails.ApplicationNo = loanapplication.ApplicationNo');
$this->db->join('loanpayment', 'loanpayment.PaymentId = paymentdetails.PaymentId');
//echo $this->db->last_query();exit;

$i = 0;

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

            $this->db->or_like($item, $_POST['search']['value']);
        }

        if(count($this->column_search) - 1 == $i) //last loop
            $this->db->group_end(); //close bracket
    }
    $i++;
}

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

    $ApplicationNo = $this->ApplicationNo;
    $this->db->order_by(key($ApplicationNo), $ApplicationNo[key($ApplicationNo)]);
}}

Possible duplicate of [PHP & MYSQL: How to resolve ambiguous column names in JOIN operation?](https://stackoverflow.com/questions/431391/php-mysql-how-to-resolve-ambiguous-column-names-in-join-operation) — cwallenpoole, Jul 14 '17 at 18:36
The message tells you that the error is in the WHERE clause. The ambiguous means that more than one reference to the a column named `VehicleNo` was found (among all the tables in the join), and you need to specifically reference which one you want. To do so, simply add the table you need: \`table\`.\`VehicleNo\` — Paul T., Jul 14 '17 at 18:37

score 1 · Answer 1 · answered Jul 15 '17 at 05:23

1

It's ambiguous because it's confused which you are referring to, use alias in your table.

answered Jul 15 '17 at 05:23

winnie damayo

426
9
17

score 0 · Answer 2 · answered Jul 14 '17 at 19:54

0

You have column VehicleNo in more than one table. You joined it and don't specify column VehicleNo from which table, is used in where condition. That's you get this error.

You need to specify like vehicleinformation.VehicleNo. Also, you should use table alias, It's good practice.

answered Jul 14 '17 at 19:54

Virendra Jadeja

821
1
10
20

the problem i think is that dataTable have predifined VehicleNo where clause. $this->db->group_start(); $this->db->like($item, $_POST['search']['value']); – Hanthony Tagam Jul 14 '17 at 19:58
1

You should work around `foreach ($this->column_search as $item)` line. You should either use key pair value or multidimensional array for $item. Basic idea is to specify $item from which table. For example: If you are using key pair value format than `foreach ($this->column_search as $table=>$item)` and this line `$this->db->like($item, $_POST['search']['value']);` would be `$this->db->like($table.$item, $_POST['search']['value']);` – Virendra Jadeja Jul 14 '17 at 20:09

DataTable Model error

2 Answers2