SQL Left Join, Loading a custom profile field

[webslug]

hi,

I'm trying to modify the portal/block/friends.php script to load a custom profile field from a users profile if they have entered one.

I've tried adding various LEFT joins with no success, can somebody help with this as I really got stuck with it.

Thanks in advance.

Code: Select all

portal/block/friends.php

//added p.pf_mood to the SELECT query

$sql = $db->sql_build_query('SELECT_DISTINCT', array(
	'SELECT'	=> 'u.user_id, u.username, p.pf_mood, u.username_clean, u.user_colour, u.user_allow_viewonline, MAX(s.session_time) as online_time, MIN(s.session_viewonline) AS viewonline',
	'FROM'		=> array(
		USERS_TABLE	=> 'u',
		ZEBRA_TABLE	=> 'z',
		PROFILE_FIELDS_DATA_TABLE => 'p'

	),

	'LEFT_JOIN'	=> array(
		array(
			'FROM'	=> array(SESSIONS_TABLE => 's'),
			'ON'	=> 's.session_user_id = z.zebra_id'
		)
	),


        //Here is what I tried....
	/*
	'LEFT_JOIN'	=> array(
		array(
			'FROM'	=> array(phpbb_profile_fields_data => 'p'),
			'WHERE'	=> 'p.user_id = z.zebra_id'
		)
	),*/

	'WHERE'		=> 'z.user_id = ' . $user->data['user_id'] . '
		AND z.friend = 1
		AND u.user_id = z.zebra_id',
	'GROUP_BY'	=> 'z.zebra_id, u.user_id, u.username, u.username_clean, u.user_allow_viewonline, u.user_colour',
	'ORDER_BY'   => 'u.username_clean ASC',
));

$result = $db->sql_query_limit($sql, $portal_config['portal_max_online_friends']);

while ($row = $db->sql_fetchrow($result))
{

	$which = (time() - $update_time < $row['online_time'] && ($row['viewonline'] || $auth->acl_get('u_viewonline'))) ? 'online' : 'offline';
	$s_display_friends = ($row['user_id']) ? true : false;

	$template->assign_block_vars("b3p_friends_{$which}", array(
		'USER_ID'		=> $row['user_id'],
		'U_PROFILE'		=> get_username_string('profile', $row['user_id'], $row['username'], $row['user_colour']),
		'USER_COLOUR'	=> get_username_string('colour', $row['user_id'], $row['username'], $row['user_colour']),
		'USERNAME'		=> get_username_string('username', $row['user_id'], $row['username'], $row['user_colour']),
		'MOOD'		=> $row['pf_mood'],
		'USERNAME_FULL'	=> get_username_string('full', $row['user_id'], $row['username'], $row['user_colour']))
	);
}
$db->sql_freeresult($result);