almost okey almost okey - 7 months ago 401
HTML Question

Order php echo output

I have a Joomla site where is installed a custom component. In that component I print the Joomla usernames into a html table. And there is a checkbox, where I can choose the users by name.

<option value="<?php echo $allUser->id; ?>" <?php echo $selected; ?> ><?php echo $allUser->name; ?></option>
<?php
}
?>
</select>
<?php } ?>


That is working ok, but I want to order the usernames. Ex. I have 3 registered user:
1-Joe
2-Katie
3-Bill

And I want to show like this order:
1-Bill
2-Joe
3-Katie

By default there are ordered by registration ID (I think). What is the right way doing this? And how to do this?

public function getUsersById($id = NULL, $select = NULL)
{
$db = JFactory::getDbo();
$query = $db->getQuery(true);
$query->select($select);
$query->from('#__users AS u');
$query->join('LEFT',' #__user_usergroup_map AS ugm ON u.id=ugm.user_id');
$query->where('ugm.group_id!="8"');
$query->where('u.id="'.$id.'"');
$db->setQuery($query);
$row = $db->loadObjectList();
return $row;
}


Thanks for your help in advance :)

Answer

Order your results using the FIELD function in MySQL:

$query->join('LEFT'," #__user_usergroup_map AS ugm ON u.id=ugm.user_id
                     order by FIELD(u.name, 'Bill', 'Joe', 'Katie')");

Make the above change to your code to achieve a custom order as specified in your question.

If you have a more complex order specification, you may also take advantage of the CASE statement such as in:

ORDER BY (
           WHEN name = 'Bill' THEN 0
           WHEN name = 'Joe' THEN 1
           WHEN name = 'Katie' THEN 2
           END
         )