Retrieving multiple results from the database

Question

I don't have much Joomla or PHP experience so I'm probably missing something really simple. I'm trying to load a certain list of user IDs into an array. This code works except it only loads one result.

$sql = 'SELECT user_id FROM #__user_usergroup_map WHERE group_id = ' . $usergroup;
$usersInGroup = array();
$usersInGroup[] = $this->app->database->queryResult($sql);

Does queryresult() only support single results? Is there a different method that I can use that supports multiple results?

Thanks,

Tim

Answer 1

The "official" Joomla way is to use the JDatabaseQuery class to create the query and give it to the Database layer. Then you use the loadAssocList or loadObjectList function to get a array with all rows.

$db = JFactory::getDbo();
$query = $db->getQuery(true);
$query->select('*');
$query->from('#__users_usergroup_map');
$query->where($db->quoteName('group_id').' = ' . $db->quote($usergroup));
$db->setQuery($query);
$result = $db->loadObjectList(); // alternative $db->loadAssocList();

The $result is a array with all rows from the Database.

Answer 2

If your query is meant to return just one value per row, then you are wanting to return a "column" of data. This is best packaged as an indexed, one-dimensional array.

loadColumn() is the best tool for the job.

$db = JFactory::getDbo();
$query = $db->getQuery(true)
    ->select("user_id")
    ->from("#__users_usergroup_map")
    ->where("group_id = " . $db->quote($usergroup));
$db->setQuery($query);
$ids = $db->loadColumn();

Answer 3

$db = JFactory::getDbo();
$list = $db->setQuery('select * from #__users')->loadAssocList();
print_r($list);