0

I have this functions in my model:

     function getIDByUsername($username)
        {

              $this->db->select('id');
              $this->db->from('users');
              $this->db->where('username', $username);
              return $this->db->get()->row()->id;

        }

    public function getData($rowno,$rowperpage,$username='', $title='', $tag='', $category='', $status='', $type='', $sort='', $order='') 
        {
 
            $this->db->select('id, title, duration, active');
            $this->db->from('videos');

            if($title != ''){
                  $this->db->like('title', $title);
            }
            if($status != 0) {
                  $this->db->like('active', $status);
            }

            if($username != ''){
                  $this->db->like('user_id', $this->getIDByUsername($username));
            }

            $this->db->limit($rowperpage, $rowno); 

            $result = $this->db->get();
            
            return $result->result_array();
    
        }

and I getting this error:

Column 'id' in field list is ambiguous

SELECT id, title, duration, active, id FROM videos, users WHERE active LIKE %1% ESCAPE '!' AND username = 'testing'

My question is why this two selects is merged? This from getIDByUsername function and select from getData function?

My tables are:

Tabel videos:

  id bigint(20) NOT NULL
  user_id bigint(20) NOT NULL DEFAULT 0
  title varchar(120) NOT NULL DEFAULT ''
  description text DEFAULT NULL

Tabel users:

  id bigint(20) NOT NULL
  username varchar(100) NOT NULL DEFAULT ''
SoftBeko
  • 1
  • 3

1 Answers1

-1

getIDByUsername should return the user id but it returns an object

function getIDByUsername($username){
   
    $this->db->select('id');
    $this->db->from('users');

    $this->db->where('username', $username);
    
    $query = $this->db->get();   
    if ($query->num_rows() > 0) 
    {
        return (int)$query->row()->id;
    }
    
    return 0;
}
Pavel12398
  • 47
  • 6
  • `Column 'id' in field list is ambiguous SELECT `id` as `video_id`, `title`, `duration`, `active`, `id` FROM `videos`, `users` WHERE `active` LIKE '%1%' ESCAPE '!' AND `username` = 'testing' Same error – SoftBeko Jan 06 '22 at 11:40
  • try to specify $this->db->select('videos.id as video_id, title, duration, active'); and in the getIDByUsername function $this->db->select('users.id'); – Pavel12398 Jan 06 '22 at 11:51
  • it show me: `No tables used SELECT * WHERE 'user_id' LIKE '%1%' ESCAPE '!' LIMIT 5` – SoftBeko Jan 06 '22 at 12:01
  • same error `No tables used SELECT * WHERE 'user_id' LIKE '%1%' ESCAPE '!' LIMIT 5` – SoftBeko Jan 06 '22 at 12:12
  • show a schema of the videos and users tables – Pavel12398 Jan 06 '22 at 12:23
  • I have edited my question and I added videos and users tables – SoftBeko Jan 06 '22 at 12:58