Forums

I have the following in a Custom Code action on a form:

<?php
$building = "Middle School"; // Building to choose from
$group = 9; // Usergroup ID to use
$db =& JFactory::getDBO();
$query="SELECT 25teac_user_usergroup_map.user_id, 25teac_users.name, 25teac_users.email as email_address, 25teac_users.id AS userid, 25teac_community_fields_values.value
FROM 25teac_community_fields_values INNER JOIN (25teac_user_usergroup_map INNER JOIN 25teac_users ON 25teac_user_usergroup_map.user_id = 25teac_users.id) ON 25teac_community_fields_values.user_id = 25teac_user_usergroup_map.user_id
WHERE 25teac_user_usergroup_map.group_id = ".$group." AND 25teac_users.block = 0 AND 25teac_community_fields_values.value LIKE '%".$building."%' GROUP BY 25teac_users.id";
$db->setQuery($query);
$staff_list = $db->loadObjectList('userid');
// print_r($staff_list);

// Second data
$db2 =& JFactory::getDBO();
$query2="SELECT 25teac_user_usergroup_map.group_id, 25teac_community_fields_values.field_id, 25teac_community_fields_values.value AS thelastname, 25teac_user_usergroup_map.user_id AS lastnameid
FROM 25teac_user_usergroup_map INNER JOIN 25teac_community_fields_values ON 25teac_user_usergroup_map.user_id = 25teac_community_fields_values.user_id
WHERE 25teac_user_usergroup_map.group_id = ".$group." AND 25teac_community_fields_values.field_id = 20 GROUP BY 25teac_user_usergroup_map.user_id";
$db2->setQuery($query2);
$last_names = $db2->loadObjectList('lastnameid');
// print_r($last_names);

?>
<table style="width:98%; margin-bottom:30px;">
<tr valign="bottom" style="border-bottom:2px solid #444444;">
<td>Name</td>
<td>Email</td>
<td>Building(s)</td>
<td>Last Name</td>
<tr>
<?php
foreach($staff_list as $detail):
?>
<td><?php
echo $detail->name;
?></td>
<td><?php
echo "<a href=mailto:".$detail->email_address.">".$detail->email_address."</a>";
?></td>
<td><?php
echo $detail->value;
?></td>
<td><?php
$user_last_name = $last_names[$detail->userid];
$user_last_name = $user_last_name->thelastname;
echo $user_last_name;
?>
</td>
</tr>
<?php
endforeach;
?>
</table>

Is there a way that I can have the table output sorted, by default, ascending by the Last Name?

Hi Rick,

Trying to understand the queries here.

<?php
$building = "Middle School"; // Building to choose from
$group = 9; // Usergroup ID to use
$db =& JFactory::getDBO();$query = "
  SELECT map.user_id, users.name, users.email as email_address, users.id AS userid, cf.value
    FROM `25teac_community_fields_values` AS cf 
    INNER JOIN ( `25teac_user_usergroup_map` AS map
      INNER JOIN `25teac_users` AS users ON map.user_id = users.id ) 
      ON cf.user_id = map.user_id
    WHERE map.group_id = {$group} 
      AND users.block = 0 
      AND cf.value LIKE '%{$building}%' 
    GROUP BY users.id
";
$db->setQuery($query);
$staff_list = $db->loadObjectList('userid');

$query = "
  SELECT map.group_id, cf.field_id, cf.value AS thelastname, map.user_id AS lastnameid
    FROM `25teac_user_usergroup_map` AS map
      INNER JOIN 25teac_community_fields_values AS cf ON map.user_id = cf.user_id
    WHERE map.group_id = {$group} 
      AND cf.field_id = 20 
    ORDER BY thelastname ASC
    GROUP BY map.user_id
";
$db->setQuery($query);
$last_names = $db2->loadObjectList('lastnameid');
. . .

Hmmm makes my eyes cross but I think I'd build the table rows in an array indexed on the lastname and then sort the array before outputting it.

Bob

Thanks, Bob. It worked!!!