字段列表中的“student_id”列不明确

Question

have a function get_autocomplete in model search

public function get_autocomplete()
    {
        $x = array();
        $search_data = "sah"; 
        $this->db->select('student_id,filename,random,type,mime');
        $this->db->from('uploads a');
        $this->db->or_like('filename',$search_data);
        $this->db->or_like('random',$search_data);
        $this->db->or_like('type',$search_data);
        $this->db->or_like('mime',$search_data);
        $this->db->join('student-record c', 'c.student_id=a.student_id', 'left');
        $this->db->like('name',$search_data);
        $this->db->or_like('email',$search_data);
        $this->db->or_like('phone',$search_data);
        $res   = $this->db->get();
    }

but I get error which states

Error Number: 1052

Column 'student_id' in field list is ambiguous

SELECT student_id, filename, random, type, mime FROM (uploads a) LEFT JOIN student-record c ON c.student_id=a.student_id WHERE filename LIKE '%sah%' OR random LIKE '%sah%' OR type LIKE '%sah%' OR mime LIKE '%sah%' AND name LIKE '%sah%' OR email LIKE '%sah%' OR phone LIKE '%sah%'

Filename: C:\wamp\www\ededge2\system\database\DB_driver.php

Line Number: 330

Help me to recover this.

Answer 1

That's because both the tables(uploads,student-record) have student_id key in it.

When you use JOIN make sure you use alias to access the particular column in case of the both tables have similar column names.

So you can update your query like this,

public function get_autocomplete()
    {
        $x = array();
         $search_data = "sah"; 
        $this->db->select('a.student_id,filename,random,type,mime');
        $this->db->from('uploads a');
        $this->db->or_like('filename',$search_data);
        $this->db->or_like('random',$search_data);
        $this->db->or_like('type',$search_data);
        $this->db->or_like('mime',$search_data);
        $this->db->join('student-record c', 'c.student_id=a.student_id', 'left');
        $this->db->like('name',$search_data);
        $this->db->or_like('email',$search_data);
        $this->db->or_like('phone',$search_data);
        $res   = $this->db->get();
    }

Column 'student_id' in field list is ambiguous,
It means it doesn't know which column value it has to return, either from uploads or student-record.

---

Update:

$this->db->select('a.student_id,filename,random,type,mime,name,phone');
// add name and phone to get it in the result-set.