codeigniter inner join not working

Question

I want to do an inner join with codeigniter and i have tried a lot but 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 in codeigniter 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 going wrong. am beginner in codeigniter.

Answer 1

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();

Answer 2

Try this:

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

Answer 3

you have missed to get() data from db,

change this to

$query = $this->db->where('city', array('city.city_name' => 'Bangalore'));

this

$this->db->where('city', array('city.city_name' => 'Bangalore'));
$query = $this->db->get();// add this line.

No need of from() you can use get() and pass the table name to it like this.

$this->db->select('*');
//$this->db->from('shop');   <-- no need of this line
$this->db->join('city', 'city.city_id = shop.city_id');
$this->db->where('city', array('city.city_name' => 'Bangalore'));
$query = $this->db->get("shop");//<-- give table name here only.