Codeigniter active record query with concat as join parameter

Question

I currently have 2 tables in which I need to get data from.

My users table looks like: ID | first_name | last_name | username

My data table looks like: ID | filename | doctorname | viewed

What I need to do match the first name and last name from the users table to the doctor name in the data table and the return the "filename" and "viewed" rows from this.

I can't seem to get the query right for it though. I'm using the active record class with code igniter. Ive also tried to have a separate function that concats the first and last name and returns it but I can't seem to pass that variable to the join section of the query.

  $avail = '1';
    $this->db->select('*');
    $this->db->from("users a");
    $this->db->where("username",$username);
    $this->db->where('CONCAT(first_name, '.', last_name) AS doctorname', FALSE);


    $this->db->join('data b', "doctor name = b.doctorName", "left");

    $result = $this->db->get();
    // print_r($this->db->last_query());
    return $result->result();

Anyone know whats going wrong? Thanks

Please post your error... Also post the query by, adding the following before return : echo $this->db->last_query(); exit; — Giri Annamalai M

Suyash Suyash · Accepted Answer · 2016-06-22T13:26:00

Try like this

$avail = '1';
$this->db->select('a.*,b.filename,b.viewed');
$this->db->from("users as a");
$this->db->join('data as b', "CONCAT(a.first_name, '.', a.last_name) = b.doctorName", "left");
$this->db->where("a.username",$username);
$result = $this->db->get();
// print_r($this->db->last_query());
return $result->result();

Codeigniter active record query with concat as join parameter

1 Answers