phpsqlcodeigniterjoinactiverecord

How to write a CodeIgniter active record WHERE column1 = column2

I am using CodeIgniter with activerecord to access my database, I am wondering if I want to write a query like this:

Select * from table1, table2
where table1.id = table2.id

I tried this:

$where = array("table1.id" => "table2.id");     
$query = $this->db->get_where("table1, table2" , $where);   
return $query->result();

But it gives me an error because it compiled to be like:

select * from table1, table2 
where table1.id = 'table2.id'

So how to tell that I want a columns is equal to another column in active record in CodeIgniter?

Solution

  • So you can simply try this

    $query = $this->db->query("SELECT * FROM table1 INNER JOIN table2 ON table1.id = table2.id WHERE table1.id = 25");
$result = $query->result_array();
return $result;

    With active records

    $this->db->select('*');
$this->db->from('table1');
$this->db->join('table2', 'table2.id = table1.id', 'inner');
$this->db->where('table2.id', 007);

    I saw your Comment on @Kisaragi answer. No you can do that. You cant get data from multiple table without join those tables to together.