[2.0.12] Hide Zero Posters

The cleanup is complete. This forum is now read only.
Post Reply

Rating:

Excellent!
13
65%
Very Good
2
10%
Good
0
No votes
Fair
2
10%
Poor
3
15%
 
Total votes: 20

User avatar
JulieJesta
Registered User
Posts: 116
Joined: Mon Oct 16, 2006 8:39 am
Location: England
Contact:

Post by JulieJesta » Mon Dec 04, 2006 11:50 am

Hiya,
Sorry to ask, as i think this maybe common question but i'm occasionally getting this error message when posting:

Code: Select all

Could not obtain common word list

DEBUG MODE

SQL Error : 1104 The SELECT would examine more than MAX_JOIN_SIZE rows; check your WHERE and use SET SQL_BIG_SELECTS=1 or SET SQL_MAX_JOIN_SIZE=# if the SELECT is okay

SELECT word_id FROM phpbb_search_wordmatch GROUP BY word_id HAVING COUNT(word_id) > 3168

Line : 309
File : functions_search.php
I wonder if you could tell me how to fix this.

Also, if i use that code directly below this, will my memberlist be in date order, ie will myself and the other admins who jioned first be on the first page of the memberlist now?

And also, do you know a way of better linking your zero posters mod within wengrics admin userlist?

i'd would like to be able to see the profiles of zero post members in the admin list.
public profile viewing is based on the u=50 number. i've worked that out, but is there an easyier way to view ip addresses of members that have yet to post? (the ip link is on the users public profile visible to admins. )

this is what i've explained to my fellow admins on my site...
you can view any registered persons profile if you know thier member id.

for example member manic has zero posts. not visible to members via memer lists.

all profiles are in this format .. phpbb/profile.php?mode=viewprofile&u=NUMBER

where i wrote number, delete NUMBER and add 522
you find the number by logging into the admin userlist then select "M" hover your mouse over here name... the link is

phpbb/admin/admin_users.php?mode=edit&u=522&sid=7043b43b4c7411a4bb32d06fdf499683
(the sid= is session id, so number varies)

its the u= thats the user number/id.

User avatar
TerraFrost
Former Team Member
Posts: 5957
Joined: Sun Dec 26, 2004 3:40 am
Location: Austin, TX

Post by TerraFrost » Mon Dec 04, 2006 5:35 pm

JulieJesta wrote: Hiya,
Sorry to ask, as i think this maybe common question but i'm occasionally getting this error message when posting:

Code: Select all

Could not obtain common word list

DEBUG MODE

SQL Error : 1104 The SELECT would examine more than MAX_JOIN_SIZE rows; check your WHERE and use SET SQL_BIG_SELECTS=1 or SET SQL_MAX_JOIN_SIZE=# if the SELECT is okay

SELECT word_id FROM phpbb_search_wordmatch GROUP BY word_id HAVING COUNT(word_id) > 3168

Line : 309
File : functions_search.php
I wonder if you could tell me how to fix this.

That has nothing to do with this MOD. The phpBB Support forum may be able to provide some assistance for that, though.
Also, if i use that code directly below this, will my memberlist be in date order, ie will myself and the other admins who jioned first be on the first page of the memberlist now?

Yup - it'll be sorted by the "real" Join date :)
And also, do you know a way of better linking your zero posters mod within wengrics admin userlist?

I'm not sure what you mean?
i'd would like to be able to see the profiles of zero post members in the admin list.

There's not an admin list built into phpBB and no modifications need to be made to wGEric's admin userlist mod to see people with zero posts...
public profile viewing is based on the u=50 number. i've worked that out, but is there an easyier way to view ip addresses of members that have yet to post? (the ip link is on the users public profile visible to admins. )

You'd have to install a separate mod to do that - one that logs registration ip addresses. Neither this mod, wGEric's userlist mod, or phpBB do that, by default.

User avatar
JulieJesta
Registered User
Posts: 116
Joined: Mon Oct 16, 2006 8:39 am
Location: England
Contact:

Post by JulieJesta » Wed Dec 06, 2006 2:45 pm

ok, i did that terrafrost. well, i think i did. i dont fully understand what i'm meant to do with the inline find bit. nothing has noticably changed though?

i've saved a backup of original first, but it didnt actually make any difference? i still shw as regestering in november when i jioned in june?

below is my current memberlist file, please, what have i got wrong?

thanks so much.

Code: Select all

<?php
/***************************************************************************
 *                              memberlist.php
 *                            -------------------
 *   begin                : Friday, May 11, 2001
 *   copyright            : (C) 2001 The phpBB Group
 *   email                : support@phpbb.com
 *
 *   $Id: memberlist.php,v 1.36.2.12 2006/02/07 20:42:51 grahamje Exp $
 *
 ***************************************************************************/

/***************************************************************************
 *
 *   This program is free software; you can redistribute it and/or modify
 *   it under the terms of the GNU General Public License as published by
 *   the Free Software Foundation; either version 2 of the License, or
 *   (at your option) any later version.
 *
 ***************************************************************************/

define('IN_PHPBB', true);
$phpbb_root_path = './';
include($phpbb_root_path . 'extension.inc');
include($phpbb_root_path . 'common.'.$phpEx);

//
// Start session management
//
$userdata = session_pagestart($user_ip, PAGE_VIEWMEMBERS);
init_userprefs($userdata);
//
// End session management
//

$start = ( isset($HTTP_GET_VARS['start']) ) ? intval($HTTP_GET_VARS['start']) : 0;

if ( isset($HTTP_GET_VARS['mode']) || isset($HTTP_POST_VARS['mode']) )
{
	$mode = ( isset($HTTP_POST_VARS['mode']) ) ? htmlspecialchars($HTTP_POST_VARS['mode']) : htmlspecialchars($HTTP_GET_VARS['mode']);
}
else
{
	$mode = 'joined';
}

if(isset($HTTP_POST_VARS['order']))
{
	$sort_order = ($HTTP_POST_VARS['order'] == 'ASC') ? 'ASC' : 'DESC';
}
else if(isset($HTTP_GET_VARS['order']))
{
	$sort_order = ($HTTP_GET_VARS['order'] == 'ASC') ? 'ASC' : 'DESC';
}
else
{
	$sort_order = 'ASC';
}

//
// Memberlist sorting
//
$mode_types_text = array($lang['Sort_Joined'], $lang['Sort_Username'], $lang['Sort_Location'], $lang['Sort_Posts'], $lang['Sort_Email'],  $lang['Sort_Website'], $lang['Sort_Top_Ten']);
$mode_types = array('joined', 'username', 'location', 'posts', 'email', 'website', 'topten');

$select_sort_mode = '<select name="mode">';
for($i = 0; $i < count($mode_types_text); $i++)
{
	$selected = ( $mode == $mode_types[$i] ) ? ' selected="selected"' : '';
	$select_sort_mode .= '<option value="' . $mode_types[$i] . '"' . $selected . '>' . $mode_types_text[$i] . '</option>';
}
$select_sort_mode .= '</select>';

$select_sort_order = '<select name="order">';
if($sort_order == 'ASC')
{
	$select_sort_order .= '<option value="ASC" selected="selected">' . $lang['Sort_Ascending'] . '</option><option value="DESC">' . $lang['Sort_Descending'] . '</option>';
}
else
{
	$select_sort_order .= '<option value="ASC">' . $lang['Sort_Ascending'] . '</option><option value="DESC" selected="selected">' . $lang['Sort_Descending'] . '</option>';
}
$select_sort_order .= '</select>';

//
// Generate page
//
$page_title = $lang['Memberlist'];
include($phpbb_root_path . 'includes/page_header.'.$phpEx);

$template->set_filenames(array(
	'body' => 'memberlist_body.tpl')
);
make_jumpbox('viewforum.'.$phpEx);

$template->assign_vars(array(
	'L_SELECT_SORT_METHOD' => $lang['Select_sort_method'],
	'L_EMAIL' => $lang['Email'],
	'L_WEBSITE' => $lang['Website'],
	'L_FROM' => $lang['Location'],
	'L_ORDER' => $lang['Order'],
	'L_SORT' => $lang['Sort'],
	'L_SUBMIT' => $lang['Sort'],
	'L_AIM' => $lang['AIM'],
	'L_YIM' => $lang['YIM'],
	'L_MSNM' => $lang['MSNM'],
	'L_ICQ' => $lang['ICQ'], 
	'L_JOINED' => $lang['Joined'], 
	'L_POSTS' => $lang['Posts'], 
	'L_PM' => $lang['Private_Message'], 

	'S_MODE_SELECT' => $select_sort_mode,
	'S_ORDER_SELECT' => $select_sort_order,
	'S_MODE_ACTION' => append_sid("memberlist.$phpEx"))
);

switch( $mode )
{
   case 'joined': 
      $order_by = ($board_config['zero_list'] ? "user_firstpost" : "user_regdate") . " $sort_order LIMIT $start, " . $board_config['topics_per_page']; 
      break; 
	case 'username':
		$order_by = "username $sort_order LIMIT $start, " . $board_config['topics_per_page'];
		break;
	case 'location':
		$order_by = "user_from $sort_order LIMIT $start, " . $board_config['topics_per_page'];
		break;
	case 'posts':
		$order_by = "user_posts $sort_order LIMIT $start, " . $board_config['topics_per_page'];
		break;
	case 'email':
		$order_by = "user_email $sort_order LIMIT $start, " . $board_config['topics_per_page'];
		break;
	case 'website':
		$order_by = "user_website $sort_order LIMIT $start, " . $board_config['topics_per_page'];
		break;
	case 'topten':
		$order_by = "user_posts $sort_order LIMIT 10";
		break;
	   default: 
      $order_by = ($board_config['zero_list'] ? "user_firstpost" : "user_regdate") . " $sort_order LIMIT $start, " . $board_config['topics_per_page']; 
      break; 
}

$sql = "SELECT username, user_id, user_viewemail, user_posts, user_regdate, user_firstpost, user_from, user_website, user_email, user_icq, user_aim, user_yim, user_msnm, user_avatar, user_avatar_type, user_allowavatar 
	FROM " . USERS_TABLE . "
	WHERE user_id <> " . ANONYMOUS . ($board_config['zero_list'] ? " AND user_posts <> 0" : "") . "
	ORDER BY $order_by";
if( !($result = $db->sql_query($sql)) )
{
	message_die(GENERAL_ERROR, 'Could not query users', '', __LINE__, __FILE__, $sql);
}

if ( $row = $db->sql_fetchrow($result) )
{
	$i = 0;
	do
	{
		$username = $row['username'];
		$user_id = $row['user_id'];

		$from = ( !empty($row['user_from']) ) ? $row['user_from'] : '&nbsp;';
		$joined = create_date($lang['DATE_FORMAT'],  $board_config['zero_list'] ? $row['user_firstpost'] : $board_config['zero_list'] ? $row['user_firstpost'] : $row['user_regdate'], $board_config['board_timezone']);
		$posts = ( $row['user_posts'] ) ? $row['user_posts'] : 0;

		$poster_avatar = '';
		if ( $row['user_avatar_type'] && $user_id != ANONYMOUS && $row['user_allowavatar'] )
		{
			switch( $row['user_avatar_type'] )
			{
				case USER_AVATAR_UPLOAD:
					$poster_avatar = ( $board_config['allow_avatar_upload'] ) ? '<img src="' . $board_config['avatar_path'] . '/' . $row['user_avatar'] . '" alt="" border="0" />' : '';
					break;
				case USER_AVATAR_REMOTE:
					$poster_avatar = ( $board_config['allow_avatar_remote'] ) ? '<img src="' . $row['user_avatar'] . '" alt="" border="0" />' : '';
					break;
				case USER_AVATAR_GALLERY:
					$poster_avatar = ( $board_config['allow_avatar_local'] ) ? '<img src="' . $board_config['avatar_gallery_path'] . '/' . $row['user_avatar'] . '" alt="" border="0" />' : '';
					break;
			}
		}

		if ( !empty($row['user_viewemail']) || $userdata['user_level'] == ADMIN )
		{
			$email_uri = ( $board_config['board_email_form'] ) ? append_sid("profile.$phpEx?mode=email&" . POST_USERS_URL .'=' . $user_id) : 'mailto:' . $row['user_email'];

			$email_img = '<a href="' . $email_uri . '"><img src="' . $images['icon_email'] . '" alt="' . $lang['Send_email'] . '" title="' . $lang['Send_email'] . '" border="0" /></a>';
			$email = '<a href="' . $email_uri . '">' . $lang['Send_email'] . '</a>';
		}
		else
		{
			$email_img = '&nbsp;';
			$email = '&nbsp;';
		}

		$temp_url = append_sid("profile.$phpEx?mode=viewprofile&" . POST_USERS_URL . "=$user_id");
		$profile_img = '<a href="' . $temp_url . '"><img src="' . $images['icon_profile'] . '" alt="' . $lang['Read_profile'] . '" title="' . $lang['Read_profile'] . '" border="0" /></a>';
		$profile = '<a href="' . $temp_url . '">' . $lang['Read_profile'] . '</a>';

		$temp_url = append_sid("privmsg.$phpEx?mode=post&" . POST_USERS_URL . "=$user_id");
		$pm_img = '<a href="' . $temp_url . '"><img src="' . $images['icon_pm'] . '" alt="' . $lang['Send_private_message'] . '" title="' . $lang['Send_private_message'] . '" border="0" /></a>';
		$pm = '<a href="' . $temp_url . '">' . $lang['Send_private_message'] . '</a>';

		$www_img = ( $row['user_website'] ) ? '<a href="' . $row['user_website'] . '" target="_userwww"><img src="' . $images['icon_www'] . '" alt="' . $lang['Visit_website'] . '" title="' . $lang['Visit_website'] . '" border="0" /></a>' : '';
		$www = ( $row['user_website'] ) ? '<a href="' . $row['user_website'] . '" target="_userwww">' . $lang['Visit_website'] . '</a>' : '';

		if ( !empty($row['user_icq']) )
		{
			$icq_status_img = '<a href="http://wwp.icq.com/' . $row['user_icq'] . '#pager"><img src="http://web.icq.com/whitepages/online?icq=' . $row['user_icq'] . '&img=5" width="18" height="18" border="0" /></a>';
			$icq_img = '<a href="http://wwp.icq.com/scripts/search.dll?to=' . $row['user_icq'] . '"><img src="' . $images['icon_icq'] . '" alt="' . $lang['ICQ'] . '" title="' . $lang['ICQ'] . '" border="0" /></a>';
			$icq =  '<a href="http://wwp.icq.com/scripts/search.dll?to=' . $row['user_icq'] . '">' . $lang['ICQ'] . '</a>';
		}
		else
		{
			$icq_status_img = '';
			$icq_img = '';
			$icq = '';
		}

		$aim_img = ( $row['user_aim'] ) ? '<a href="aim:goim?screenname=' . $row['user_aim'] . '&message=Hello+Are+you+there?"><img src="' . $images['icon_aim'] . '" alt="' . $lang['AIM'] . '" title="' . $lang['AIM'] . '" border="0" /></a>' : '';
		$aim = ( $row['user_aim'] ) ? '<a href="aim:goim?screenname=' . $row['user_aim'] . '&message=Hello+Are+you+there?">' . $lang['AIM'] . '</a>' : '';

		$temp_url = append_sid("profile.$phpEx?mode=viewprofile&" . POST_USERS_URL . "=$user_id");
		$msn_img = ( $row['user_msnm'] ) ? '<a href="' . $temp_url . '"><img src="' . $images['icon_msnm'] . '" alt="' . $lang['MSNM'] . '" title="' . $lang['MSNM'] . '" border="0" /></a>' : '';
		$msn = ( $row['user_msnm'] ) ? '<a href="' . $temp_url . '">' . $lang['MSNM'] . '</a>' : '';

		$yim_img = ( $row['user_yim'] ) ? '<a href="http://edit.yahoo.com/config/send_webmesg?.target=' . $row['user_yim'] . '&.src=pg"><img src="' . $images['icon_yim'] . '" alt="' . $lang['YIM'] . '" title="' . $lang['YIM'] . '" border="0" /></a>' : '';
		$yim = ( $row['user_yim'] ) ? '<a href="http://edit.yahoo.com/config/send_webmesg?.target=' . $row['user_yim'] . '&.src=pg">' . $lang['YIM'] . '</a>' : '';

		$temp_url = append_sid("search.$phpEx?search_author=" . urlencode($username) . "&showresults=posts");
		$search_img = '<a href="' . $temp_url . '"><img src="' . $images['icon_search'] . '" alt="' . sprintf($lang['Search_user_posts'], $username) . '" title="' . sprintf($lang['Search_user_posts'], $username) . '" border="0" /></a>';
		$search = '<a href="' . $temp_url . '">' . sprintf($lang['Search_user_posts'], $username) . '</a>';

		$row_color = ( !($i % 2) ) ? $theme['td_color1'] : $theme['td_color2'];
		$row_class = ( !($i % 2) ) ? $theme['td_class1'] : $theme['td_class2'];

		$template->assign_block_vars('memberrow', array(
			'ROW_NUMBER' => $i + ( $start + 1 ),
			'ROW_COLOR' => '#' . $row_color,
			'ROW_CLASS' => $row_class,
			'USERNAME' => $username,
			'FROM' => $from,
			'JOINED' => $joined,
			'POSTS' => $posts,
			'AVATAR_IMG' => $poster_avatar,
			'PROFILE_IMG' => $profile_img, 
			'PROFILE' => $profile, 
			'SEARCH_IMG' => $search_img,
			'SEARCH' => $search,
			'PM_IMG' => $pm_img,
			'PM' => $pm,
			'EMAIL_IMG' => $email_img,
			'EMAIL' => $email,
			'WWW_IMG' => $www_img,
			'WWW' => $www,
			'ICQ_STATUS_IMG' => $icq_status_img,
			'ICQ_IMG' => $icq_img, 
			'ICQ' => $icq, 
			'AIM_IMG' => $aim_img,
			'AIM' => $aim,
			'MSN_IMG' => $msn_img,
			'MSN' => $msn,
			'YIM_IMG' => $yim_img,
			'YIM' => $yim,
			
			'U_VIEWPROFILE' => append_sid("profile.$phpEx?mode=viewprofile&" . POST_USERS_URL . "=$user_id"))
		);

		$i++;
	}
	while ( $row = $db->sql_fetchrow($result) );
	$db->sql_freeresult($result);
}

if ( $mode != 'topten' || $board_config['topics_per_page'] < 10 )
{
	$sql = "SELECT count(*) AS total
		FROM " . USERS_TABLE . "
		WHERE user_id <> " . ANONYMOUS . ($board_config['zero_list'] ? " AND user_posts <> 0" : "");

	if ( !($result = $db->sql_query($sql)) )
	{
		message_die(GENERAL_ERROR, 'Error getting total users', '', __LINE__, __FILE__, $sql);
	}

	if ( $total = $db->sql_fetchrow($result) )
	{
		$total_members = $total['total'];

		$pagination = generate_pagination("memberlist.$phpEx?mode=$mode&order=$sort_order", $total_members, $board_config['topics_per_page'], $start). '&nbsp;';
	}
	$db->sql_freeresult($result);
}
else
{
	$pagination = '&nbsp;';
	$total_members = 10;
}

$template->assign_vars(array(
	'PAGINATION' => $pagination,
	'PAGE_NUMBER' => sprintf($lang['Page_of'], ( floor( $start / $board_config['topics_per_page'] ) + 1 ), ceil( $total_members / $board_config['topics_per_page'] )), 

	'L_GOTO_PAGE' => $lang['Goto_page'])
);

$template->pparse('body');

include($phpbb_root_path . 'includes/page_tail.'.$phpEx);

?>
Julie, Mum to 1 boy, 1 boyfriend, and many fluffy pets. :)
http://www.parentstime.co.uk

User avatar
TerraFrost
Former Team Member
Posts: 5957
Joined: Sun Dec 26, 2004 3:40 am
Location: Austin, TX

Post by TerraFrost » Thu Dec 07, 2006 4:58 am

Last time I asked you to undo some stuff and it doesn't appear that that was done. Here, instead, are the exact changes you'll need to make. I could have posted them earlier, instead of what I did post, but I was kinda being lazy :)

Code: Select all

#
#-----[ OPEN ]------------------------------------------
#
memberlist.php

#
#-----[ FIND ]------------------------------------------
#
	case 'joined':
		$order_by = ($board_config['zero_list'] ? "user_firstpost" : "user_regdate") . " $sort_order LIMIT $start, " . $board_config['topics_per_page'];
		break;
#
#-----[ REPLACE WITH ]----------------------------------
#
	case 'joined':
		$order_by = "user_regdate $sort_order LIMIT $start, " . $board_config['topics_per_page'];
		break;

#
#-----[ FIND ]------------------------------------------
#
	default:
		$order_by = ($board_config['zero_list'] ? "user_firstpost" : "user_regdate") . " $sort_order LIMIT $start, " . $board_config['topics_per_page'];
		break;
#
#-----[ REPLACE WITH ]----------------------------------
#
	default:
		$order_by = "user_regdate $sort_order LIMIT $start, " . $board_config['topics_per_page'];
		break;
#
#-----[ FIND ]------------------------------------------
#
		$joined = create_date($lang['DATE_FORMAT'], $row['user_regdate'], $board_config['board_timezone']);

#
#-----[ IN-LINE FIND ]---------------------------------
#
 $board_config['zero_list'] ? $row['user_firstpost'] :

#
#-----[ IN-LINE REPLACE WITH ]---------------------------
# delete it
#

freerider381
Registered User
Posts: 10
Joined: Tue Jul 26, 2005 10:01 am

Post by freerider381 » Tue Dec 12, 2006 7:24 am

Hi, not that much of a techie with code etc and I installed this mod but I received an error halfway.

SQL PROCESSING HALTED

An error was encountered while processing the SQL commands. Further SQL processing has been halted. You may choose to complete the MOD installation anyway and perform the SQL commands manually yourself. However, at this point EM cannot guarantee the MOD will work correctly so you are best off seeking support from the Author before continuing further.

The failed line was:
UPDATE phpbb_users AS u,phpbb_posts AS p1 LEFT OUTER JOIN phpbb_posts AS p2 ON p1.poster_id = p2.poster_id AND p2.post_time < p1.post_time INNER JOIN phpbb_posts AS p3 ON p3.post_time = p1.post_time SET u.user_firstpost=p3.post_time WHERE p2.post_time IS NULL AND p1.poster_id = u.user_id;;

SQL ERROR: 1064
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '; p1.post_time INNER JOIN phpbb_posts AS p3 ON p3.post_time = p1.post_time SET u' at line 1

and i have been reading through this topic and tried that db_update file you were saying at the start and this is what I got when i used it lol


UPDATE phpbb_users SET (user_firstpost) = SELECT MIN(post_time) FROM phpbb_posts WHERE phpbb_posts.poster_id = phpbb_users.user_id
+++ Error: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '(user_firstpost) = SELECT MIN(post_time) FROM phpbb_posts WHERE phpbb_posts.post' at line 1

So Yes, i Have no idea what I am doing lol, any help will be much appreciated.

User avatar
TerraFrost
Former Team Member
Posts: 5957
Joined: Sun Dec 26, 2004 3:40 am
Location: Austin, TX

Post by TerraFrost » Tue Dec 12, 2006 3:37 pm

My guess would be that you're using MySQL 3.23 - a version of MySQL that this MOD doesn't support.

Sir Flaco
Registered User
Posts: 14
Joined: Sat Dec 09, 2006 8:52 am

Problems enabling Hide Zero Posters

Post by Sir Flaco » Tue Dec 12, 2006 8:34 pm

Hello

I was wondering if anyone has had the problem of not being able to enable the Hide Zero Posters mod in the General Configuration panel. I select the radio buttons and press submit but the radio buttons then appear back in the
Disabled status

Thanks for your help

User avatar
TerraFrost
Former Team Member
Posts: 5957
Joined: Sun Dec 26, 2004 3:40 am
Location: Austin, TX

Post by TerraFrost » Tue Dec 12, 2006 8:54 pm

Sounds like you didn't perform the SQL.

Sir Flaco
Registered User
Posts: 14
Joined: Sat Dec 09, 2006 8:52 am

Post by Sir Flaco » Tue Dec 12, 2006 9:55 pm

Thanks so much for the speedy reply

Could you possibly clarify the exact steps to take to perform the SQL? Or direct me to a good resource for this proceedure.

Thanks again

User avatar
TerraFrost
Former Team Member
Posts: 5957
Joined: Sun Dec 26, 2004 3:40 am
Location: Austin, TX

Post by TerraFrost » Tue Dec 12, 2006 9:57 pm

Go to http://sql.phpmix.com , copy / paste the SQL into the window, read the directions, and run the resultant file.

Sir Flaco
Registered User
Posts: 14
Joined: Sat Dec 09, 2006 8:52 am

Post by Sir Flaco » Tue Dec 12, 2006 10:08 pm

I am fairly new to this process, where do I find the SQL to copy/paste. After that I am sure I can follow the directions and implement the file.

Thanks

Sir Flaco
Registered User
Posts: 14
Joined: Sat Dec 09, 2006 8:52 am

Post by Sir Flaco » Tue Dec 12, 2006 10:18 pm

Please disregard last post, I figured it out. Thanks so much for your time on this issue, you have been very helpfull.

Flaco

Sir Flaco
Registered User
Posts: 14
Joined: Sat Dec 09, 2006 8:52 am

Post by Sir Flaco » Tue Dec 12, 2006 11:08 pm

Sorry, I may have jumped the gun on resolving tthis problem.

Here is what I did:

Cut and pasted the code below into the parser:

ALTER TABLE phpbb_users ADD user_firstpost int(11) NOT NULL;

UPDATE phpbb_users AS u, phpbb_posts AS p1
LEFT OUTER JOIN
phpbb_posts AS p2
ON p1.poster_id = p2.poster_id AND
p2.post_time < p1.post_time
INNER JOIN
phpbb_posts AS p3
ON p3.post_time = p1.post_time
SET u.user_firstpost=p3.post_time
WHERE p2.post_time IS NULL AND
p1.poster_id = u.user_id;

UPDATE phpbb_users
SET user_firstpost = user_regdate
WHERE user_posts <> 0 AND user_firstpost = 0;

INSERT INTO phpbb_config (config_name, config_value) VALUES ('zero_count',1),('zero_newest',1),('zero_group',1),('zero_list',1);


Selected MySQL from the drop down and it gave me this result:

<?php
/**
*
* @package SQL Parser
* @script install/db_update.php
* @copyright (c) 2005 phpBB Group
* @license http://opensource.org/licenses/gpl-license.php GNU General Public License
*
*
* - WARNINGS:
* *** This script contains SQL/DLL statements that will modify your database!!!
* *** The SQL/DDL statements contained in this script are optimized for MySQL only!
*
* - Installation:
* 1) Make backups of your database before proceeding!
* 2) Create a subdirectory named "install" (without quotes) in your phpBB installation.
* 3) Save this file as "db_update.php" and upload to your newly created install directory.
* 4) Now, open the script using your browser of choice as in the following example:
* http://www.example.com/forums/install/db_update.php
* ...and follow instructions.
* 5) Once, your DB has been updated, remove the install directory and this file.
*
* - Notes:
* - This script can only be run by board administrators.
* - First, a confirmation panel will show all SQL statements.
* - Your database will only be updated once the confirmation panel has been confirmed.
*
*/

define('IN_PHPBB', true);
$phpbb_root_path = '../';
include($phpbb_root_path . 'extension.inc');
include($phpbb_root_path . 'common.'.$phpEx);
$gen_simple_header = true;

//
// Language entries used in this script.
//
$lang += array(
'Update_confirm' => 'This panel will update your database with the SQL statements detailed below.<br /><br />Remember to make backups of your database before proceeding!<hr /><table><tr><td><pre>%s</pre></td></tr></table><hr />Click <i>Yes</i> to proceed or <i>No</i> to return to your board index.',
'Updating_database' => 'Updating the Database',
'Installation_complete' => 'Installation Complete',
'Delete_this_file' => 'Please, be sure to delete your install directory and this file from your phpBB installation now.',
'Successful' => 'Successful'
);

//
// Session Management.
//
$userdata = session_pagestart($user_ip, PAGE_INDEX);
init_userprefs($userdata);

//
// Only administrators here, please
//
if( !$userdata['session_logged_in'] )
{
redirect(append_sid("login.$phpEx?redirect=".basename(__FILE__), true));
}
if( $userdata['user_level'] != ADMIN )
{
if ( @file_exists($phpbb_root_path . 'language/lang_' . $board_config['default_lang'] . '/lang_admin.' . $phpEx) )
{
include($phpbb_root_path . 'language/lang_' . $board_config['default_lang'] . '/lang_admin.' . $phpEx);
}
else
{
include($phpbb_root_path . 'language/lang_english/lang_admin.' . $phpEx);
}
message_die(GENERAL_MESSAGE, $lang['Not_admin']);
}

//
// Build Array of SQL Statements.
//
$sql = array();
$sql[] = 'ALTER TABLE ' . $table_prefix . 'users ADD user_firstpost INTEGER(11) NOT NULL';
$sql[] = 'UPDATE ' . $table_prefix . 'users AS u,' . $table_prefix . 'posts AS p1 LEFT OUTER JOIN ' . $table_prefix . 'posts AS p2 ON p1.poster_id = p2.poster_id AND p2.post_time < p1.post_time INNER JOIN ' . $table_prefix . 'posts AS p3 ON p3.post_time = p1.post_time SET u.user_firstpost=p3.post_time WHERE p2.post_time IS NULL AND p1.poster_id = u.user_id';
$sql[] = 'UPDATE ' . $table_prefix . 'users SET user_firstpost = user_regdate WHERE user_posts <> 0 AND user_firstpost = 0';
$sql[] = 'INSERT INTO ' . $table_prefix . 'config(config_name,config_value) VALUES(\'zero_count\',1),(\'zero_newest\',1),(\'zero_group\',1),(\'zero_list\',1)';
$sql_count = count($sql);

//
// Output confirmation page?
//
$cancel = isset($HTTP_POST_VARS['cancel']) ? true : false;
$confirm = isset($HTTP_POST_VARS['confirm']) ? true : false;
$mode = isset($HTTP_POST_VARS['mode']) ? trim(htmlspecialchars($HTTP_POST_VARS['mode'])) : '';

if( $cancel )
{
redirect(append_sid("index.$phpEx", true));
}

if( !$confirm || $mode != 'db_update' )
{
include($phpbb_root_path . 'includes/page_header.'.$phpEx);

$template->set_filenames(array(
'confirm_body' => 'confirm_body.tpl')
);

$message = sprintf($lang['Update_confirm'], implode(";\n\n", $sql));

$s_hidden_fields = '<input type="hidden" name="mode" value="db_update" />';

$template->assign_vars(array(
'L_INDEX' => '',
'MESSAGE_TITLE' => $lang['Information'],
'MESSAGE_TEXT' => $message,
'L_YES' => $lang['Yes'],
'L_NO' => $lang['No'],
'S_CONFIRM_ACTION' => append_sid(basename(__FILE__)),
'S_HIDDEN_FIELDS' => $s_hidden_fields)
);

$template->pparse('confirm_body');

include($phpbb_root_path . 'includes/page_tail.'.$phpEx);
}

//
// Send Page Header.
//
$page_title = $lang['Updating_database'];
include($phpbb_root_path . 'includes/page_header.'.$phpEx);

//
// Execute SQL and get Results.
//
$sql_rows = '';
for( $i = 0; $i < $sql_count; $i++ )
{
if( !($result = $db->sql_query($sql[$i])) )
{
$error = $db->sql_error();
$color = '#FF0000';
$success = $lang['Error'] . ':';
$errmsg = ' ' . $error['message'];
}
else
{
$color = '#00AA00';
$success = $lang['Successful'];
$errmsg = '';
}
$class = ($i%2) == 0 ? 'row1' : 'row2';
$sql_rows .= '<tr><td class="'.$class.'"><div class="genmed">' . $sql[$i] . ';<br /><br /><b style="color:' . $color . ';">' . $success . '</b>' . $errmsg . '</div></td></tr>';
}

//
// Build the Report.
//
$click_return_index = sprintf($lang['Click_return_index'], '<a class="genmed" href="' . append_sid($phpbb_root_path . "index.$phpEx") . '">', '</a>');

$html = <<<EOT
<table width="100%" cellpadding="0" cellspacing="0" border="0" class="forumline">
<tr>
<th>{$page_title}</th>
</tr>
<tr>
<td>
<table cellpadding="8" cellspacing="1" border="0" align="center">
{$sql_rows}
</table>
</td>
</tr>
<tr>
<td class="row3"><img src="{$phpbb_root_path}images/spacer.gif" border="0" height="4" alt="" /></td>
</tr>
<tr>
<th>{$lang['Installation_complete']}</th>
</tr>
<tr>
<td align="center">
<table cellpadding="8" cellspacing="0" border="0" align="center">
<tr>
<td>
<b class="gen" style="color:#EE0000;">{$lang['Delete_this_file']}</b>
</td>
</tr>
</table>
</td>
</tr>
<tr>
<td class="catBottom" align="center">
<span class="genmed">{$click_return_index}</span>
</td>
</tr>
</table>
EOT;
echo $html;

//
// Send Page Footer.
//
include($phpbb_root_path . 'includes/page_tail.'.$phpEx);

?>

I then created a directory in my phpbb folder on my server called "install" without quotes and pasted the above code into a plain txt file named db_update.php and uploaded that file into the folder install.

I tried pointing a browser to that directory but it gave me this message:

Please ensure both the install/ and contrib/ directories are deleted

That is as far as I can get


Thanks for your help on this

Flaco

User avatar
TerraFrost
Former Team Member
Posts: 5957
Joined: Sun Dec 26, 2004 3:40 am
Location: Austin, TX

Post by TerraFrost » Tue Dec 12, 2006 11:47 pm

Please ensure both the install/ and contrib/ directories are deleted

That is as far as I can get

Try deleting those directories, then ;)

Sir Flaco
Registered User
Posts: 14
Joined: Sat Dec 09, 2006 8:52 am

Post by Sir Flaco » Wed Dec 13, 2006 12:02 am

Both those directories are gone and I am still not able to enable the Hide Zero Posters

Post Reply

Return to “[2.0.x] MOD Database Cleanup”

Who is online

Users browsing this forum: No registered users and 15 guests