This was used in a project management system to show the logged in user the projects in which they formed a part of the project team. When the user logged in, they would see a menu with quick access to projects and project cases. A cck user reference field with the multiple value option was used for the project team reference.
<?php
// get the currently logged in users uid
global $user;
$this_user = $user->uid;
// sql query that quieries 2 tables
$result = db_query("SELECT DISTINCT (n.nid), n.title
FROM node n
LEFT JOIN content_field_project_team cp ON ( n.vid = cp.vid )
WHERE n.type
IN ('project')
AND n.status =1
AND cp.field_project_team_uid =$this_user");
// the loop to extract the data and print links
while ($anode = db_fetch_object($result)) {
$links[] = l($anode->title, "node/". $anode->nid) .'<ul>';
// Custom paths using casetracker module to output the data
$links[] .= l("All Cases", "casetracker/cases/". $anode->nid ."/all");
$links[] .= l("My Cases", "casetracker/cases/". $anode->nid ."/my") .'</ul>';
}
// Drupal theme function for producing data in list format
return theme_item_list($links);
?>