CodeIgniter Forums
Developing Mulitdiminsional Array - Printable Version

+- CodeIgniter Forums (https://forum.codeigniter.com)
+-- Forum: Archived Discussions (https://forum.codeigniter.com/forumdisplay.php?fid=20)
+--- Forum: Archived Development & Programming (https://forum.codeigniter.com/forumdisplay.php?fid=23)
+--- Thread: Developing Mulitdiminsional Array (/showthread.php?tid=54549)



Developing Mulitdiminsional Array - El Forum - 09-13-2012

[eluser]xtremer360[/eluser]
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?


Developing Mulitdiminsional Array - El Forum - 09-13-2012

[eluser]yacman[/eluser]
The sql should be something like:
Code:
Select titles.title_name,
IFNULL(champion_user.roster_name,'N/A') as champion,
IFNULL(contender_user.roster_name,'N/A') as contender1,
IFNULL(contender2_user.roster_name,'N/A') as contender2,
IFNULL(contender3_user.roster_name,'N/A') as contender3
FROM titles_champions
     INNER JOIN titles on titles.title_id = titles_champions.title_id
     LEFT JOIN roster as champion_user on champion_user.roster_id = titles_champions.champion_id
     LEFT JOIN roster as contender_user on contender_user.roster_id = titles_champions.champion_id
     LEFT JOIN roster as contender2_user on contender2_user.roster_id = titles_champions.champion_id
     LEFT JOIN roster as contender3_user on contender3_user.roster_id = titles_champions.champion_id

and the active record something like
Code:
$this->db->select($this->master_model->titles_table.'.title_name');
       $this->db->select("IFNULL(champion_user.roster_name,'N/A') as champion",FALSE);
       $this->db->select("IFNULL(contender_user.roster_name,'N/A') as contender1",FALSE);
       $this->db->select("IFNULL(contender2_user.roster_name,'N/A') as contender2",FALSE);
       $this->db->select("IFNULL(contender3_user.roster_name,'N/A') as contender3",FALSE);
       $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','INNER');
       $this->db->join("{$this->master_model->roster_table} as champion_user", "champion_user.roster_id = {$this->master_model->title_champions_table}.roster_id",'LEFT');
       $this->db->join("{$this->master_model->roster_table} as contender_user", "champion_user.roster_id = {$this->master_model->title_champions_table}.roster_id",'LEFT');
       $this->db->join("{$this->master_model->roster_table} as contender2_user", "champion_user.roster_id = {$this->master_model->title_champions_table}.roster_id",'LEFT');
       $this->db->join("{$this->master_model->roster_table} as contender3_user", "champion_user.roster_id = {$this->master_model->title_champions_table}.roster_id",'LEFT');
        $query = $this->db->get();
        return $query->result();



Developing Mulitdiminsional Array - El Forum - 09-13-2012

[eluser]xtremer360[/eluser]
Only issue is that there would never be any NULL values because its an integer field so it would be 0 if there isn't a champion or contender declared



Developing Mulitdiminsional Array - El Forum - 09-13-2012

[eluser]yacman[/eluser]
This is correct then the left joins will fail, causing a null value in that specific roster_name column for the joins.

You can get rid of the ifnull statement all together and just select the columns as inside the IFNULL statements.

Code:
$this->db->select('contender3_user.roster_name as contender3');



Developing Mulitdiminsional Array - El Forum - 09-13-2012

[eluser]xtremer360[/eluser]
Here's what I've gathered so far.

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

Code:
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

Code:
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
        )

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()
    {
        $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');



Developing Mulitdiminsional Array - El Forum - 09-14-2012

[eluser]yacman[/eluser]
It's painfully obvious what is wrong, and you should do some research with sql to understand why.


Developing Mulitdiminsional Array - El Forum - 09-14-2012

[eluser]xtremer360[/eluser]
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.


Developing Mulitdiminsional Array - El Forum - 09-14-2012

[eluser]yacman[/eluser]
Unless you have a row in Roster with the ID of Zero, then the Left Join will have a NULL value for every column associated with that because the LEFT join won't have a record to bind to.

The MySql function 'IFNULL' will replace NULL values with the second input to that function.

This is how you can apply IFNULL's to columns via CI.
Code:
$this->db->select("IFNULL(champion_user.roster_name,'Vacant') as champion",FALSE);
       $this->db->select("IFNULL(contender_user.roster_name,'TBD') as contender1",FALSE);
       $this->db->select("IFNULL(contender2_user.roster_name,'TBD') as contender2",FALSE);
       $this->db->select("IFNULL(contender3_user.roster_name,'TBD') as contender3",FALSE);

This is the same as
Code:
select IFNULL(champion_user.roster_name,'Vacant') as champion,
          IFNULL(contender_user.roster_name,'TBD') as contender1,
          IFNULL(contender2_user.roster_name,'TBD') as contender2,
          IFNULL(contender3_user.roster_name,'TBD') as contender3



Developing Mulitdiminsional Array - El Forum - 09-14-2012

[eluser]xtremer360[/eluser]
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 'FROM (`title_champions`) JOIN `titles` ON `titles`.`title_id` =`title_champions`' at line 2

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

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()
    {
        $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('IFNULL(champion.roster_name, Vacant) AS champion', 'FALSE');  
        $this->db->select('IFNULL(contender1.roster_name, TBD) AS contender1', 'FALSE');  
        $this->db->select('IFNULL(contender2.roster_name, TBD) AS contender2', 'FALSE');  
        $this->db->select('IFNULL(contender3.roster_name, TBD) AS contender3', 'FALSE');  
        $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', 'left');
        $this->db->join($database_schema->roster_table.' AS contender1', 'contender1.roster_id ='.$database_schema->title_champions_table.'.contender1_id', 'left');
        $this->db->join($database_schema->roster_table.' AS contender2', 'contender2.roster_id ='.$database_schema->title_champions_table.'.contender2_id', 'left');
        $this->db->join($database_schema->roster_table.' AS contender3', 'contender3.roster_id ='.$database_schema->title_champions_table.'.contender3_id', 'left');
        $query = $this->db->get();
        return $query->result();
    }



Developing Mulitdiminsional Array - El Forum - 09-14-2012

[eluser]yacman[/eluser]
'FALSE' is a string, and a non-empty one and this is a true statement.
FALSE is a boolean, and false which is what we want to pass to the function.

In addition, you need to encapsulate TBD and Vacant with single quotes. I see I messed that up in a earlier post.

Code:
$this->db->select('IFNULL(contender3.roster_name, TBD) AS contender3', 'FALSE');
//to
$this->db->select("IFNULL(contender3.roster_name, 'TBD') AS contender3", FALSE);