TOPIC: How to get the Names of Attendees for a Drop Down Field

<?php 
//get config variables
$config =& JFactory::getConfig();

// db cnxn string
$hostname_dbc = "localhost";
$database_dbc = $config->getValue( 'config.db' );
$username_dbc = $config->getValue( 'config.user' );
$password_dbc = $config->getValue( 'config.password' );

$dbc = mysql_connect($hostname_dbc, $username_dbc, $password_dbc) or die('<span style="color: #990000;">Could not connect to MySQL: '.mysql_error());

mysql_select_db($database_dbc, $dbc);

function subval_sort($a,$subkey) {
	foreach($a as $k=>$v) {
		$b[$k] = strtolower($v[$subkey]);
	}
	asort($b);
	foreach($b as $k=>$v) {
		$c[] = $a[$k];
	}
	return $c;
}

?>
   

<?php 

// Get Dinner Choice Values, 26 = this custom field's ID
$fieldID = 26;
$eventID = 5;
$dquery  = '';
$dquery .= 'SELECT `values`, `usagelimit` FROM `jos_dtregister_fields` WHERE id = ' . $fieldID;
$dinners = mysql_query($dquery, $dbc) or die(mysql_error());

if (!$dinners) {
    echo 'Could not run query: ' . mysql_error();
    exit;
}
$drow = mysql_fetch_row($dinners);

// set arrays for query above
$restaurants  = array();
$seatlimits   = array();
$dinnergroups = array();
$attendees    = array();

// get names of restaurants
$restaurants = explode("|", $drow[0]);

// get number of seats
$seatlimits = explode("|", $drow[1]);

mysql_free_result($dinners);


// Build query to get attendees
$query = '';
$query .= 'SELECT  jos_dtregister_user_field_values.user_id, jos_dtregister_user_field_values.field_id, jos_dtregister_user_field_values.value FROM jos_dtregister_user_field_values, jos_dtregister_user WHERE jos_dtregister_user.userId = jos_dtregister_user_field_values.user_id and jos_dtregister_user.eventId = ' . $eventID .' ORDER BY jos_dtregister_user_field_values.user_id, jos_dtregister_user_field_values.field_id';

$result    = mysql_query($query, $dbc) or die(mysql_error());

$registrants = array(); // all registrants array


$i = 0; 
$empty = '<p>None yet. Be the first!</p>';

while ($row = mysql_fetch_object($result)) {
		
		// sort by user's id
		$sort_switch = $row->field_id;
		switch ($sort_switch):
		
		//get field id
		case 15:		// fname
			$registrants[$i]['fname'] = $row->value;
			$fname = $registrants[$i]['fname'];
			break;
		case 16:		// lname
			$registrants[$i]['lname'] = $row->value;
			$lname = $registrants[$i]['lname'];
			break;
		case $fieldID:		// dinner choice
			$registrants[$i]['dg'] = $row->value;
			
			// add Lname Fname to dinner groups' arrays
			$dg_switch = $row->value;
			
			// Add lname|fname to attendees array
			$attendees[$dg_switch][] = $registrants[$i]['lname'] .'|'.$registrants[$i]['fname'];
			
			// old dgx switch went here			
			
			
			//echo $html;
			//$html = ''; 
			$i++;
			break;
		default:
			   
		endswitch;
	
}


// Build Dinner Groups Array
// Set some text vars
$rname    = 'Restaurant: ';
$rlimits  = 'Max: ';
$ravail   = 'Available Seats: ';
$reserved = 'Reserved: ';
$total = 0; // total count

for($i = 0; $i < count($restaurants); $i++){

	//echo $restaurants[$i] . '<br />';
	
	$dinnergroups[$i]['href']  = '<a name="dg'.$i.'">';
	$dinnergroups[$i]['hdr']   = $restaurants[$i];
	$dinnergroups[$i]['seats'] = $seatlimits[$i];
	$dinnergroups[$i]['html']  = '';
	
	// iterate thru the attendee
	sort($attendees[$i]);
	foreach ($attendees[$i] as $key => $val) {
		$pos = strpos($val, '|');
		// separate the fname and laname from the array element
		$dinnergroups[$i]['html'] .= ucwords(substr($val, $pos+1, strlen($val))) . ' ' . ucwords(substr($val, 0, $pos)) . '<br />';
	}
	
	echo $dinnergroups[$i]['href'];
	echo '<h3>' . $dinnergroups[$i]['hdr'] . '</h3>';
	
	// If the limit is 0 then this is the NONE group
	if ($dinnergroups[$i]['seats'] != 0) {
		echo '<p><b>' . $rlimits . $dinnergroups[$i]['seats'] . ' &mdash; ' . $reserved . count($attendees[$i]) . ' / <span style="color:red;">' . $ravail . ($dinnergroups[$i]['seats'] - count($attendees[$i])) . '</span></b></p>';		
	}
	
	echo $dinnergroups[$i]['html'];
	$total = $total + count($attendees[$i]);
}
echo '<p><b>' . 'Total Attendees: ' . $total . '</b></p>';

?>
<?php mysql_free_result($result); ?>