Developing Multidiminsional Array

CoolAsCarlito · 09-13-2012, 08:34 AM

I'm trying to develop a resultset that appears like so:

Code:

[1] Title One name
     [champion] Jeff
     [contender1] kevin
     [contentder2] brian
     [contender3] will

[2] Title Two name
     [champion] Jeff
     [contender1] kevin
     [contentder2] brian
     [contender3] will

[3] Title Three name
     [champion] Jeff
     [contender1] kevin
     [contentder2] brian
     [contender3] will

I'm using the codeigniter's active record to develop this query and have this so far. To understand how my db structure looks this is what I am using.

To understand what the structure means is that the titles table defines the title id and the title name. The roster table defines the roster id and roster name. The title champions table has the table id which is the same as the title id from the titles table and champion id, contender1 id, contender2 id, contender3 id all represent the id of the roster table.

Code:

Titles Table
title_id
title_name

Titles Champions Table    
title_id
champion_id
contender1_id
contender2_id
contender3_id

Roster Table
roster_id
roster_name

This is what I have so far.

Code:

/**
     * Gets the listing of all title champions and contenders.
     * 
     * @return mixed (object/NULL) Object of title champions/contenders if query returned any data
     */
    public function get_title_champions()
    {
        $this->db->select($this->master_model->titles_table.'.title_name');
        $this->db->select($this->master_model->titles_table.'.title_id');
        $this->db->from($this->master_model->title_champions_table);
        $this->db->join($this->master_model->titles_table, $this->master_model->titles_table.'.title_id ='.$this->master_model->title_champions_table.'.title_id');
        $query = $this->db->get();
        return $query->result();
    }

Is there any additional ideas from anyone?