View only showing one row from sql result set

Question

I am creating a component and trying to display a list within a model. The code I have shows the multiple arrays that should be on the list, but when I add echo inside the view table, I am only getting one result each time.

$db    = Factory::getDBO();
$query = $db->getQuery(true);
$query
    ->select("*")
    ->from($db->quoteName('#__football_player'))
    ->where($db->quoteName('teamid') . ' = ' . $db->quote($value) . ' AND active = 1');
$db->setQuery($query);
$results = $db->loadObjectList();
    
foreach($results as $result) {
    $playid = $result->id;
    $players = explode(',', $playid);
    print_r($players)
}
$this->_item->players = $players;
$this->_item->lname = $result->lname;

The print_r() is showing both items, but the table on the frontend only shows one, or if I move my print outside the brackets it goes down to one as well.

For more context:

Here is a sql fiddle with some sample data: http://sqlfiddle.com/#!9/d08e77/1.

The page currently loads all the details from #__football_team and lists it on the page, but looking to have a list of #__football_player below it.

Answer 1

The good news is that this is a very basic task.

I would pass the entire players result set to your view via $this->_item->players.

$query
    ->select("*")
    ->from($db->qn('#__football_player'))
    ->where(
        [
            $db->qn('teamid') . ' = ' . $db->q($value),
            "active = 1"
        ]
    );
$db->setQuery($query);

$this->_item->players = $db->loadObjectList();

This will generate an empty array or an array of object.

In your view, use a foreach to iterate each row and then access column data using property syntax (->).