How to work with join data in codeigniter?

Question

Hi,

I am having a play around with codeigniter and trying to get my head around the active record system and such like. I have set up a couple of tables and am attempting to run a join on them, as such:

function GetOrganisationsAndBuildingDetails()
{
 $this->db->select('organisations.organisation_name,
        organisations.organisation_id,
        buildings.building_name,
                    buildings.address1');
 $this->db->from('organisations')->join('buildings', 'buildings.organisation_id = organisations.organisation_id');
 $query = $this->db->get();
 return $query->result();
}

In my database i have one organisation with two related buildings. The above query returns two objects (one for each building) - however, the organisation is duplicated.

• stdClass Object ( [organisation_name] => This is an example org [organisation_id] => 1 [building_name] => test building [address1] => 123456 )

• stdClass Object ( [organisation_name] => This is an example org [organisation_id] => 1 [building_name] => teeeest building [address1] => 123456 )

I suppose I was expecting something along the lines of one return object with a series of nested objects for related buildings. Is this possible? If not, is their a recommend way of arranging the return data so I can easily loop through it in the view? (foreach org, foreach building etc etc).

Apologies if I'm being a little dense here. Im coming from .net and (linq to SQL in particular) where this stuff is a little different)

Answer 1

http://stackoverflow.com/questions/793807/codeigniter-php-mysql-retrieving-data-with-join

Check this link........

Answer 2

The query will inevitably return duplicate data as you say, you have to organize them after you get the result like this

$buildings = array();

foreach ( $result_object as $organization ) {
    $building_data = array(
        'building_name' => $organization->building_name,
        'address'       => $organization->address,
    );
    $buildings[$organization->organization_name][] = $building_data;
}

this way organizations will be "compacted" in the first key of the multidimensional array, and one level deeper you will have info about the buildings. Hope this helps.