CODEWITHSUNDEEP
phpmysqlcodeigniterjoin
Roozbeh J
Roozbeh J

Reputation: 77

Codeigniter JOIN issue with merging tables

I have two tables that I'm trying to get a join result however this is what I get on var_dump();

object(CI_DB_mysql_result)#20 (8) { ["conn_id"]=> resource(29) of type (mysql link persistent) ["result_id"]=> resource(39) of type (mysql result) ["result_array"]=> array(0) { } ["result_object"]=> array(0) { } ["custom_result_object"]=> array(0) { } ["current_row"]=> int(0) ["num_rows"]=> int(3) ["row_data"]=> NULL }

My table structure is

Table Name: bands Fields: bands_ref, name

and then I have another table which takes an artists id and band id (This is how I associated bands with artists) and I need to keep it this way as an Artist could be in 2 other bands

Table Name: artist_bands Fields: artist_id, band_id

And finally Artist tables is as below:

Table name: artist Fields: name, artist_ref

So what I'm doing here is, to list all bands an artist is associated with. But I think I am making a mistake that I can't see. All other data comes through perfectly in my edit function and I don't get any errors in regards to my view or controllers but this is what I have done in my model which may help

Please note that my variables are passed to model and is returning result for rest of artist table - its only JOIN that doesn't work.

public function get_my_band($artist_ref) {

$this->db->select('*');
$this->db->from('bands');
$this->db->join('artist_bands','artist_bands.band_id = bands.bands_ref');
$this->db->where('artist_bands.artist_id', $artist_ref);
$query = $this->db->get();
return $query;

}

Any help and advise would be appreciated.

Thanks

Upvotes: 1

Views: 197

Answers (1)

mrsrinivas
mrsrinivas

Reputation: 35404

It seems you are not running the query. You just framed it.

public function get_my_band($artist_ref) {

    $this->db->select('*');
    $this->db->from('bands');
    $this->db->join('artist_bands','artist_bands.band_id = bands.bands_ref');
    $this->db->where('artist_bands.artist_id', $artist_ref);

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

Upvotes: 2

Related Questions