Developing Multidiminsional Array

[Xtremer360]

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

 

[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.

 

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.

 

/**
     * 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?

[Jessica]

[1] Title One name

    [champion] Jeff,

    [contender1] kevin,

    [contentder2] brian,

    [contender3] will

 

Is not possible. What it could look like is:

 

$array = array();
$array[1] = array(
'title'=>'Title One name',
'champion'=>'Jeff'
'contender1'=>'kevin'
'contentder2'=>'brian'
'contender3'=>'will'
);

 

Array

(

    [1] => Array

        (

            [title] => Title One name

            [champion] => Jeff

            [contender1] => kevin

            [contentder2] => brian

            [contender3] => will

        )

 

)

 

Or even like this if you want:

$array = array();
$array[1] = array(
'title'=>'Title One name',
'data'=>array(
	'champion'=>'Jeff',
	'contender1'=>'kevin',
	'contentder2'=>'brian',
	'contender3'=>'will'
)
);

 

Array

(

    [1] => Array

        (

            [title] => Title One name

            [data] => Array

                (

                    [champion] => Jeff

                    [contender1] => kevin

                    [contentder2] => brian

                    [contender3] => will

                )

 

        )

 

)

 

For now, do a print_r($query->result); so we can see what you DO have. However, it'll probably be easiest to just use the result as it comes out of the DB, and modify your code that USES it.

[Xtremer360]

That's weird when I do that ind my model I get a Undefined property: CI_DB_mysql_result::$result

[Jessica]

Maybe your query failed? I am not familiar at all with CI, so I can't help with that part.

[Xtremer360]

I did a print_r on the variable I have set that returns the function from the model and got this.

 

Array
(
    [0] => stdClass Object
        (
            [title_name] => Undisputed Title
            [title_id] => 1
        )

    [1] => stdClass Object
        (
            [title_name] => Tag Team Titles
            [title_id] => 2
        )

    [2] => stdClass Object
        (
            [title_name] => Outlaw Title
            [title_id] => 3
        )

)

[Jessica]

So right now all you're getting is the titles, you still need to join to the other table to get the people.

[Xtremer360]

So I have to join the roster table 4 times. Doesn't make sense. Reason I say that is because I'd have to have the champion_id equal the roster_id, the contender1_id equal the roster_id, contender2_id equal the roster_id, contender3_id equal the roster_id.

[Jessica]

You either have to join 4 times (which is what I would do, as long as you can do an inner join), or use PHP to logically create the array you want. How did you think you would get the data?

[Xtremer360]

Why do you say inner join? Can you elaborate please so I can understand?

[premiso]

Can you elaborate please so I can understand?

 

Is that even possible?

[Xtremer360]

Can you elaborate please so I can understand?

 

Is that even possible?

 

Why would you even say that?

[premiso]

Can you elaborate please so I can understand?

 

Is that even possible?

 

Why would you even say that?

 

Because everyone is thinking it, I am just the one asshole who spits it out.

[Xtremer360]

I guess I was wrong. I was under the impression that message board forums and a way for others to talk and ask questions and find out why to your questions. My mistake.

 

[Jessica]

Contrary to how it might seem some days, we don't just sit here waiting for your next post. Some of us work for a living and use this forum as a place to take a break. I was going to answer your question about WHY I suggest inner join, rather than left join, but now I'll just say JFGI.

[premiso]

My mistake.

 

Dully noted, please take care not to let it happen again.

[Xtremer360]

I work for a living too just overnights. And I have no idea what JFGI means.

[premiso]

And I have no idea what JFGI means.

 

This is just too good to pass up.  http://lnked.me/ln09h

[Xtremer360]

Now I feel like an idiot.

[premiso]

Now I feel like an idiot.

 

Now maybe the learning process can begin, young turd on a stick.

[Xtremer360]

You all will probably shoot me but I'm getting a similar syntax error like I did in my other post but not not seeing it.

 

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'AS champion INNER JOIN `roster` ON `roster`.`roster_id` =`title_champions`.`cont' at line 4

 

SELECT `titles`.`title_name`, `titles`.`title_id` FROM (`title_champions`) JOIN `titles` ON `titles`.`title_id` =`title_champions`.`title_id` INNER JOIN `roster` ON `roster`.`roster_id` =`title_champions`.`champion_id` AS champion INNER JOIN `roster` ON `roster`.`roster_id` =`title_champions`.`contender1_id` AS contender1 INNER JOIN `roster` ON `roster`.`roster_id` =`title_champions`.`contender2_id` AS contender2 INNER JOIN `roster` ON `roster`.`roster_id` =`title_champions`.`contender3_id` AS contender3

 

 

/**
     * 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()
    {
        $database_schema = $this->master_model->database_schema();
        $this->db->select($database_schema->titles_table.'.title_name');
        $this->db->select($database_schema->titles_table.'.title_id');
        $this->db->from($database_schema->title_champions_table);
        $this->db->join($database_schema->titles_table, $database_schema->titles_table.'.title_id ='.$database_schema->title_champions_table.'.title_id');
        $this->db->join($database_schema->roster_table, $database_schema->roster_table.'.roster_id ='.$database_schema->title_champions_table.'.champion_id AS champion', 'inner');
        $this->db->join($database_schema->roster_table, $database_schema->roster_table.'.roster_id ='.$database_schema->title_champions_table.'.contender1_id AS contender1', 'inner');
        $this->db->join($database_schema->roster_table, $database_schema->roster_table.'.roster_id ='.$database_schema->title_champions_table.'.contender2_id AS contender2', 'inner');
        $this->db->join($database_schema->roster_table, $database_schema->roster_table.'.roster_id ='.$database_schema->title_champions_table.'.contender3_id AS contender3', 'inner');
        $query = $this->db->get();
        return $query->result();
    }

[Xtremer360]

Perfect I have a working print_r of my resultset however its the wrong desired results.

 

title_champions_table
title_id = 1
champion_id = 1
contender1_id = 2
contender2_id = 3
contender3_id = 3

title_id = 2
champion_id = 1
contender1_id = 2
contender2_id = 3
contender3_id = 3

title_id = 3
champion_id = 1
contender1_id = 2
contender2_id = 3
contender3_id = 3

Roster Table
roster_id  Roster Name
1 - Kid Wonder
2- Oriel
3- Ryu Satoshi

 

 

Array
(
    [0] => stdClass Object
        (
            [title_name] => Undisputed Title
            [title_id] => 1
            [champion] => Ryu Satoshi
            [contender1] => Ryu Satoshi
            [contender2] => Ryu Satoshi
            [contender3] => Ryu Satoshi
        )

    [1] => stdClass Object
        (
            [title_name] => Tag Team Titles
            [title_id] => 2
            [champion] => Ryu Satoshi
            [contender1] => Ryu Satoshi
            [contender2] => Ryu Satoshi
            [contender3] => Ryu Satoshi
        )
)
    [2] => stdClass Object
        (
            [title_name] => Outlaw Title
            [title_id] => 3
            [champion] => Ryu Satoshi
            [contender1] => Ryu Satoshi
            [contender2] => Ryu Satoshi
            [contender3] => Ryu Satoshi
        )

 

/**
     * 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()
    {
        $database_schema = $this->master_model->database_schema();
        $this->db->select($database_schema->titles_table.'.title_name');
        $this->db->select($database_schema->titles_table.'.title_id');
        $this->db->select('contender3.roster_name AS champion');  
        $this->db->select('contender3.roster_name AS contender1');  
        $this->db->select('contender3.roster_name AS contender2');  
        $this->db->select('contender3.roster_name AS contender3');  
        $this->db->from($database_schema->title_champions_table);
        $this->db->join($database_schema->titles_table, $database_schema->titles_table.'.title_id ='.$database_schema->title_champions_table.'.title_id');
        $this->db->join($database_schema->roster_table.' AS champion', 'champion.roster_id ='.$database_schema->title_champions_table.'.champion_id', 'inner');
        $this->db->join($database_schema->roster_table.' AS contender1', 'contender1.roster_id ='.$database_schema->title_champions_table.'.contender1_id', 'inner');
        $this->db->join($database_schema->roster_table.' AS contender2', 'contender2.roster_id ='.$database_schema->title_champions_table.'.contender2_id', 'inner');
        $this->db->join($database_schema->roster_table.' AS contender3', 'contender3.roster_id ='.$database_schema->title_champions_table.'.contender3_id', 'inner');
        $query = $this->db->get();
        return $query->result();
    }

[Xtremer360]

Issue was that I was using contender3 table alias situation was obviously wrong however te real issue and this is the tricky part is what if one of the values inside of the champion_id or contender1_id or contender2_id or contender3_id has a value of 0 then how would I account for that.  If the champion_id is 0 then it should put Vacant into the array and for the contenders if the value is 0 then it should put TBD for each of them.