Assistance requested modifying viewtopic

[schwim]

Hi there everyone!

I would like to preface this by stating that I'm not very savvy at php. The code I write is very simplistic compared to what I find in phpbb's code. Unions, joins and arrays often leaves me confused and seeking help. Looking into phpbb's code on viewtopic.php and the relevant template file is sort of like me trying to read a foreign language that I haven't learned. I'm hoping for some guidance on how to add my feature in a way that the creators would approve of, if possible. If not possible due to my inability to grasp the meaning of the code, I would gladly welcome a hack that gets the job done

I created a membermap for my site. I would like to now place a link under the "From:" on viewtopic.php for any member that has placed a pin on the map.

I've created a table called "user_extra" that holds the map data for any member that has placed their pin. The uid of "user_extra" correlates with the phpbb_users "user_id". Here's how I get the pin data for a member:

Code: Select all

	// No starting point passed in the URL.  Let's check to see if we need to center on user's point.
	$query0="SELECT lat, lng FROM user_extra where uid = '$user_id' LIMIT 1";
	$result0=mysql_query($query0) or die (mysql_error());
	$num0=mysql_num_rows($result0);
	if($num0 != '0'){
		while ($row0 = mysql_fetch_assoc($result0)) {
			$center_lat = $row0['lat'];
			$center_lng = $row0['lng'];
		}
	}

So, it seems that I would need to integrate this action with the poster-related info in viewtopic.php and viewtopic_body.html. I think I've found the related code. I may be wrong, however.

viewtopic.php:

This looks like it's grabbing the post data, including the user info. Maybe I need to do my query here?

Code: Select all

$sql = $db->sql_build_query('SELECT', array(
	'SELECT'	=> 'u.*, z.friend, z.foe, p.*',

	'FROM'		=> array(
		USERS_TABLE		=> 'u',
		POSTS_TABLE		=> 'p',
	),

	'LEFT_JOIN'	=> array(
		array(
			'FROM'	=> array(ZEBRA_TABLE => 'z'),
			'ON'	=> 'z.user_id = ' . $user->data['user_id'] . ' AND z.zebra_id = p.poster_id'
		)
	),

	'WHERE'		=> $db->sql_in_set('p.post_id', $post_list) . '
		AND u.user_id = p.poster_id'
));

$result = $db->sql_query($sql);

I'm guessing I need to add blank values to anonymous' array and then add the lat, lng info to the user's array:

Code: Select all

$user_cache[$poster_id] = array(
				'joined'		=> $user->format_date($row['user_regdate']),
				'posts'			=> $row['user_posts'],
				'warnings'		=> (isset($row['user_warnings'])) ? $row['user_warnings'] : 0,
				'from'			=> (!empty($row['user_from'])) ? $row['user_from'] : '',

				'sig'					=> $user_sig,
				'sig_bbcode_uid'		=> (!empty($row['user_sig_bbcode_uid'])) ? $row['user_sig_bbcode_uid'] : '',
				'sig_bbcode_bitfield'	=> (!empty($row['user_sig_bbcode_bitfield'])) ? $row['user_sig_bbcode_bitfield'] : '',

				'viewonline'	=> $row['user_allow_viewonline'],
				'allow_pm'		=> $row['user_allow_pm'],

				'avatar'		=> ($user->optionget('viewavatars')) ? get_user_avatar($row['user_avatar'], $row['user_avatar_type'], $row['user_avatar_width'], $row['user_avatar_height']) : '',
				'age'			=> '',

				'rank_title'		=> '',
				'rank_image'		=> '',
				'rank_image_src'	=> '',

				'username'			=> $row['username'],
				'user_colour'		=> $row['user_colour'],

				'online'		=> false,
				'profile'		=> append_sid("{$phpbb_root_path}memberlist.$phpEx", "mode=viewprofile&u=$poster_id"),
				'www'			=> $row['user_website'],
				'aim'			=> ($row['user_aim'] && $auth->acl_get('u_sendim')) ? append_sid("{$phpbb_root_path}memberlist.$phpEx", "mode=contact&action=aim&u=$poster_id") : '',
				'msn'			=> ($row['user_msnm'] && $auth->acl_get('u_sendim')) ? append_sid("{$phpbb_root_path}memberlist.$phpEx", "mode=contact&action=msnm&u=$poster_id") : '',
				'yim'			=> ($row['user_yim']) ? 'http://edit.yahoo.com/config/send_webmesg?.target=' . urlencode($row['user_yim']) . '&.src=pg' : '',
				'jabber'		=> ($row['user_jabber'] && $auth->acl_get('u_sendim')) ? append_sid("{$phpbb_root_path}memberlist.$phpEx", "mode=contact&action=jabber&u=$poster_id") : '',
				'search'		=> ($auth->acl_get('u_search')) ? append_sid("{$phpbb_root_path}search.$phpEx", "author_id=$poster_id&sr=posts") : '',
				
				// START Anti-Spam ACP
				'user_flagged'	=> $row['user_flagged'] ? true : false,
				// END Anti-Spam ACP

				'author_full'		=> get_username_string('full', $poster_id, $row['username'], $row['user_colour']),
				'author_colour'		=> get_username_string('colour', $poster_id, $row['username'], $row['user_colour']),
				'author_username'	=> get_username_string('username', $poster_id, $row['username'], $row['user_colour']),
				'author_profile'	=> get_username_string('profile', $poster_id, $row['username'], $row['user_colour']),
			);

If there's something else to be done in viewtopic, I don't know what it would be.

viewtopic_body.html

I would like to have

Code: Select all

<a href='/membermap.php?lat=<user lat>&lng=<user lng>&zoom=8'>View Location</a>

Code: Select all

<!-- IF postrow.POSTER_POSTS != '' --><dd><strong>{L_POSTS}:</strong> {postrow.POSTER_POSTS}</dd><!-- ENDIF -->
		<!-- IF postrow.POSTER_JOINED --><dd><strong>{L_JOINED}:</strong> {postrow.POSTER_JOINED}</dd><!-- ENDIF -->
		<!-- IF postrow.POSTER_FROM --><dd><strong>{L_LOCATION}:</strong> {postrow.POSTER_FROM}</dd><!-- ENDIF -->
!!!HERE!!!

I'll need to add quite a few things to the forum, so I'm really hoping to get a decent understanding of what's going on with the code, so I can make the necessary additions.

Any help would be greatly appreciated!

[RMcGirr83]

First thing I would do is to make an edit to the includes/constants.php file like this

Code: Select all

define('USER_EXTRA_TABLE',				$table_prefix . 'user_extra');

which assumes that your new table is like phpbb_user_extra, if not then remove the $table_prefix .

then within the sql for viewtopic

Code: Select all

$sql = $db->sql_build_query('SELECT', array(
   'SELECT'   => 'u.*, z.friend, z.foe, p.*, ue.lat, ue.lng',

   'FROM'      => array(
      USERS_TABLE      => 'u',
      POSTS_TABLE      => 'p',
	  USER_EXTRA_TABLE => 'ue',
   ),

   'LEFT_JOIN'   => array(
      array(
         'FROM'   => array(ZEBRA_TABLE => 'z'),
         'ON'   => 'z.user_id = ' . $user->data['user_id'] . ' AND z.zebra_id = p.poster_id'
      )
   ),

   'WHERE'      => $db->sql_in_set('p.post_id', $post_list) . '
      AND u.user_id = p.poster_id'
));

and you will have to make changes to anonymous and $user_cache, eg for anonymous

Code: Select all

'lng' => '',

and for $user_cache

Code: Select all

'lng' => !empty($row['lng']) ? $row['lng'] : '',

as well as make the appropriate change to the $postrow array and to your html file. Hope it helps

[schwim]

Thanks very much for all your help!

I've tried to implement the changes, but have run into an issue.

The first issue is that everyone is getting the same lat & lng, (the lat and long for everyone is the first row in the table). It isn't getting it for the uid.

Does the sql statement need to be altered or have I mucked something up?

[tbackoff]

I'm assuming you added the parts that Rich added? From first glancing at it (keep in mind that it's 1:30am ), try changing this:

Code: Select all

AND u.user_id = p.poster_id'

to this (change uid to whatever your user id field is):

Code: Select all

AND u.user_id = p.poster_id 
AND u.user_id = ue.uid'

[RMcGirr83]

try this for the sql

Code: Select all

$sql = $db->sql_build_query('SELECT', array(
	'SELECT'	=> 'u.*, z.friend, z.foe, p.*, ue.lng, ue.lat',

	'FROM'		=> array(
		USERS_TABLE		=> 'u',
		POSTS_TABLE		=> 'p',
	),

	'LEFT_JOIN'	=> array(
		array(
			'FROM'	=> array(ZEBRA_TABLE => 'z'),
			'ON'	=> 'z.user_id = ' . $user->data['user_id'] . ' AND z.zebra_id = p.poster_id'
		),
		array(
			'FROM'	=> array(USER_EXTRA_TABLE => 'ue'),
			'ON'	=> 'ue.uid = p.poster_id'
		)
	),

	'WHERE'		=> $db->sql_in_set('p.post_id', $post_list) . '
		AND u.user_id = p.poster_id'
));

[schwim]

try this for the sql

Code: Select all

$sql = $db->sql_build_query('SELECT', array(
	'SELECT'	=> 'u.*, z.friend, z.foe, p.*, ue.lng, ue.lat',

	'FROM'		=> array(
		USERS_TABLE		=> 'u',
		POSTS_TABLE		=> 'p',
	),

	'LEFT_JOIN'	=> array(
		array(
			'FROM'	=> array(ZEBRA_TABLE => 'z'),
			'ON'	=> 'z.user_id = ' . $user->data['user_id'] . ' AND z.zebra_id = p.poster_id'
		),
		array(
			'FROM'	=> array(USER_EXTRA_TABLE => 'ue'),
			'ON'	=> 'ue.uid = p.poster_id'
		)
	),

	'WHERE'		=> $db->sql_in_set('p.post_id', $post_list) . '
		AND u.user_id = p.poster_id'
));

That seems to be working fantastically. I was wondering if I could ask your assistance with a slight modification to this. There's another field that I'm pulling along with lat and lng. I'm pulling a var called marker_active. The link should only show if marker_active is 1. If it's 0 or if the user doesn't have a row in user_extra at all, it shouldn't show up. I've already made the modifications to the arrays and postrow to include this variable, but don't know how to implement it.

sql:

Code: Select all

   'SELECT'   => 'u.*, z.friend, z.foe, p.*, ue.lng, ue.lat, ue.marker_active',

anon:

Code: Select all

				'lat'			=> '',
				'lng'			=> '',
				'marker_active' => '',

user:

Code: Select all

				'lat' => !empty($row['lat']) ? $row['lat'] : '',
				'lng' => !empty($row['lng']) ? $row['lng'] : '',
				'marker_active' => !empty($row['marker_active']) ? $row['marker_active'] : '',

postrow:

Code: Select all

		'POSTER_LAT'		=> $user_cache[$poster_id]['lat'],
		'POSTER_LNG'		=> $user_cache[$poster_id]['lng'],
		'POSTER_MARKER_ACTIVE'		=> $user_cache[$poster_id]['marker_active'],

Thanks so much for all your assistance

[RMcGirr83]

in the html file

<!-- IF postrow.POSTER_MARKER_ACTIVE -->stuff here<!-- ENDIF -->

[schwim]

That works super. What does that IF check for? Existence and anything other than 0?

[RMcGirr83]

It is called a switch and will only display if set to true which === 1