phpmysqlcodeigniterjoinactiverecord

Convert SELECT query with INNER JOIN on columns with the same name and WHERE clause to CodeIgniter active record

I want to do an INNER JOIN with CodeIgniter and I have tried a lot but with no success.

MySQL code for INNER JOIN:

SELECT *
FROM shop
INNER JOIN city ON city.city_id = shop.city_id
WHERE city.city_name = 'Bangalore'

The above SQL query is working perfectly in phpMyAdmin. While converting this code to a CodeIgniter active record script it is not working.

CodeIgniter code is:

$this->db->select('*');
$this->db->from('shop');
$this->db->join('city', 'city.city_id = shop.city_id');
$query = $this->db->where('city', array('city.city_name' => 'Bangalore'));

if($query->num_rows() > 0) {
    return $result = $query->result_array();
}
else {
    return 0;
}

Where am I going wrong?

Solution

  • Your query should work well:

    $this->db->select('*');
$this->db->from('shop');
$this->db->join('city', 'city.city_id = shop.city_id');
//$this->db->where('city', array('city.city_name' => 'Bangalore'));
$this->db->where('city.city_name', 'Bangalore');
$query = $this->db->get();