Array Objects with queries

Question

What I'm trying to do is create an array that contains a list of the titles with the title name, id of the database row, then the names of the champion and contenders. For the values of the champion and contenders I'm needing to do an additional query to retrieve the roster name of the person. If the value was 0 for the champion it needs to add Vacant to the array for that spot and if its 0 for the contender then it uses TBD for the array. Here's what I'm working with so for which includes the query and the print_r output.

My question is I"m not sure where/how I need to be running an additional query with those values of the champion and contenders for each of the titles.

    /**
     * Get titles champions
     *
     * @return  object/NULL
     */
    function getTitlesChampions()
    {      
        $this->db->select('titlesList.id');
        $this->db->select('titlesList.titleName');
        $this->db->select('titlesChampions.championID');
        $this->db->select('titlesChampions.contender1ID');
        $this->db->select('titlesChampions.contender2ID');
        $this->db->select('titlesChampions.contender3ID');
        $this->db->from('titlesChampions');
        $this->db->join('titlesList', 'titlesList.id = titlesChampions.titlesListID');
        $query = $this->db->get();  
        if ($query->num_rows() > 0) {
            echo "<pre>";
            print_r ($query->result());
            echo "</pre>";

        }                                    
    }

Array
(
[0] => stdClass Object
    (
        [id] => 1
        [titleName] => Undisputed Heavyweight Title
        [championID] => 1
        [contender1ID] => 1
        [contender2ID] => 1
        [contender3ID] => 1
    )

[1] => stdClass Object
    (
        [id] => 2
        [titleName] => Outlaw Title
        [championID] => 1
        [contender1ID] => 0
        [contender2ID] => 0
        [contender3ID] => 0
    )

[2] => stdClass Object
    (
        [id] => 3
        [titleName] => Tag Team Titles
        [championID] => 1
        [contender1ID] => 0
        [contender2ID] => 0
        [contender3ID] => 0
    )

)

Not quite sure how I shoudl create the addional ids and do the second query. — Jeff Davidson
– Jeff Davidson, Commented Apr 24, 2012 at 18:30

Seabass · Accepted Answer · 2012-04-25 20:13:49Z

1

Best practice is a set of additional joins and subqueries, but given the (assumed) state of your schema, having a secondary function might be easiest.

For each result that is found, you will iterate through and assign a Name by calling getRosterName(). That function will return and add additional information to the Champions object.

/**
 * Get titles champions
 *
 * @return  object/NULL
 */
function getTitlesChampions()
{
    $this->db->select('titlesList.id');
    $this->db->select('titlesList.titleName');
    $this->db->select('titlesChampions.championID');
    $this->db->select('titlesChampions.contender1ID');
    $this->db->select('titlesChampions.contender2ID');
    $this->db->select('titlesChampions.contender3ID');
    $this->db->from('titlesChampions');
    $this->db->join('titlesList', 'titlesList.id = titlesChampions.titlesListID');
    $query = $this->db->get();  

    if ($query->num_rows() > 0) {

        $result = query->result();

        foreach($result as $row)
        {
            // Iterate through
            $row->championName      =   $this->getRosterName($row->championID);
            $row->contender1Name    =   $this->getRosterName($row->contender2ID);
            $row->contender2Name    =   $this->getRosterName($row->contender2ID);
            $row->contender3Name    =   $this->getRosterName($row->contender3ID);
        }

        // Return it
        return $result;

    }
    return null;
}

/*

/* Returns the name */
function getRosterName($rosterId = null)
{

    if($rosterId && $rosterID > 0)
    {
        $this->db->select('RosterName');
        $this->db->where('rosterId', $rosterId);
        $query = $this->db->get('roster'); // Or whatever your `roster` table is named.
        return $query->row()->firstName; // Or whatever the name is you want returned
    }
    return null;
}

I don't know anything about your schema, so this is a shot in the dark.

Good luck.

edited Apr 25, 2012 at 20:13

answered Apr 24, 2012 at 22:45

Seabass

1,97312 silver badges15 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Jeff Davidson Over a year ago

The problem tho is what if the value is a 0 that's what I"m trying to get at.

Seabass Over a year ago

I changed the getRosterName function: it will return null for the name, if the value is 0.

Collectives™ on Stack Overflow

Array Objects with queries

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related