phpBB2Refugees.com Logo
Not affiliated with or endorsed by the phpBB Group

Register •  Login 

Continue the legacy...

Welcome to all phpBB2 Refugees!Wave Smilie

This site is intended to continue support for the legacy 2.x line of the phpBB2 bulletin board package. If you are a fan of phpBB2, please, by all means register, post, and help us out by offering your suggestions. We are primarily a community and support network. Our secondary goal is to provide a phpBB2 MOD Author and Styles area.

Take out website from member with 0 posts


 
Search this topic... | Search MOD Requests... | Search Box
Register or Login to Post    Index » MOD Requests  Previous TopicPrint TopicNext Topic
Author Message
Sylver Cheetah 53
Board Member



Joined: 17 Dec 2008

Posts: 426
Location: Milky Way


flag
PostPosted: Tue Jan 13, 2009 10:02 am 
Post subject: Take out website from member with 0 posts

Hy!

I want a MOD or a SQL command for taking out website from people with 0 posts.
I've heard that Google is punishing me if I have links for banned porn websites. And I have a lot of robots on my website. From 4400 members, I estimate that 3000-4000 are robots.
From a little while I use No more spambots MOD, wich blocks registration attempts from people choosing GMT-12 zonal time, but this is only for not long ago. Untill then, robots could register. But before I was using Simple Registration MOD, wich lets only usernam, email and password in registration (now also zonal time). So there was no website field anymore, but mayba some robots put it in the fiedl after logging in, because I never used activation by email.

So I need a few thing. First of all, I need to know how many users with 0 posts and GMT-12 are having a website on their profile. And then, how many people with 0 posts and whatever GMT are having webistes on profile.

Thanks!

_________________
Image link
My Forum || My Blog

phpBB2 forever! icon_smile.gif
Back to top
drathbun
Board Member



Joined: 24 Jul 2008

Posts: 729
Location: Texas


flag
PostPosted: Tue Jan 13, 2009 10:21 am 
Post subject: Re: Take out website from member with 0 posts

This should get you a list of users (with relevant information) that have zero posts but have a website in their profile.
Code:
select user_id, username, user_email
from phpbb_users
where user_posts = 0
and user_website <> ''

This will mark those users inactive, assuming you want to mark every single one of them:
Code:
update phpbb_users
set user_active = 0
where user_active = 1
and user_posts = 0
and user_website <> ''

As with any direct database manipulation, it's best to take a backup of your database (specifically your users table) before doing this. If you just want to make a backup copy of the users table you can do this:
Code:
create table backup_users
as
select * from phpbb_users

Then if you mess something up, here's how you can put the users back from the backup table:
Code:
truncate table phpbb_users

Code:
insert into phpbb_users
select * from backup_users

Those two statements will take the backup copy you made and restore it.

It would be best to take the board offline during this process so you don't have anyone attempting to make changes to their profile (or register) while you are working.

_________________
phpBBDoctor Blog
Back to top
Sylver Cheetah 53
Board Member



Joined: 17 Dec 2008

Posts: 426
Location: Milky Way


flag
PostPosted: Tue Jan 13, 2009 11:20 am 
Post subject: Re: Take out website from member with 0 posts

It seems that I have 1216 users with 0 posts and a website to profile. icon_eek.gif

Code:
Showing rows 0 - 29 (1,216 total, Query took 0.0051 sec)

SQL query:
SELECT user_id, username, user_email
FROM phpbb_users
WHERE user_posts =0
AND user_website <> ''
LIMIT 0 , 30

Thanks for your reply, but I do not wish to make them inactive, this is not good enough. I wish to delete theis website. Something like SET user_website = NULL or I don't know... But this only for there 1216 users, not for all. icon_smile.gif Also, I am thinking about deleting all users with 0 posts and GMT-12. There is a SQL for this?

Thanks again for your efforts, I do not know SQL. icon_redface.gif

_________________
Image link
My Forum || My Blog

phpBB2 forever! icon_smile.gif
Back to top
drathbun
Board Member



Joined: 24 Jul 2008

Posts: 729
Location: Texas


flag
PostPosted: Tue Jan 13, 2009 12:34 pm 
Post subject: Re: Take out website from member with 0 posts

I would delete them altogether, rather than just delete the website. In order to do that, you need to remove rows from three tables: phpbb_groups, phpbb_user_group, and phpbb_users. Since the user_posts is zero, we will assume you don't have to clean up any posts.

What version of MySQL are you running?

_________________
phpBBDoctor Blog
Back to top
Sylver Cheetah 53
Board Member



Joined: 17 Dec 2008

Posts: 426
Location: Milky Way


flag
PostPosted: Tue Jan 13, 2009 6:27 pm 
Post subject: Re: Take out website from member with 0 posts

PHP version: 5.2.6
MySQL version: 4.1.22-standard-log
Apache version: 2.2.9 (Unix)
PERL version: 5.8.8

I don't think I need to alter phpbb_groups and phpbb_user_group table because I do not use groups. icon_biggrin.gif

_________________
Image link
My Forum || My Blog

phpBB2 forever! icon_smile.gif
Back to top
drathbun
Board Member



Joined: 24 Jul 2008

Posts: 729
Location: Texas


flag
PostPosted: Tue Jan 13, 2009 11:46 pm 
Post subject: Re: Take out website from member with 0 posts

Sylver Cheetah 53 wrote:
MySQL version: 4.1.22-standard-log

That means you can do sub-queries.
Quote:
I don't think I need to alter phpbb_groups and phpbb_user_group table because I do not use groups. icon_biggrin.gif

Yes, you do. icon_smile.gif Every user has a group assigned to them during the registration process, a "single user" group.

There are many ways to go about this, but what I would do is run some SQL to create a copy of the user data to be removed, and then use it in a series of queries.

Step one is to capture the data you need:
Code:
create table users_to_delete
as
select ug.user_id, ug.group_id
from phpbb_users u
, phpbb_user_group ug
, phpbb_groups g
where u.user_id = ug.user_id
and ug.group_id = g.group_id
and u.user_posts = 0
and u.user_website <> ''
and g.group_single_user = 1

That gives you a copy of the rows from the phpbb_user_group table that match the users to remove. Next, you can use your SQL tool (I suspect you might have phpMyAdmin) to create an index on the user_id and the group_id (do separate indexes for each) on this temporary table. Else you can run this:
Code:
create index user_id on users_to_delete (user_id)

Code:
create index group_id on users_to_delete (group_id)

Finally, run these:
Code:
delete from phpbb_users
where user_id in (select user_id from users_to_delete)

Code:
delete from phpbb_user_group
where user_id in (select user_id from users_to_delete)

Code:
delete from phpbb_groups
where group_id in (select group_id from users_to_delete)

As always, it would be a really good idea to make a backup copy of all of the affected tables before you run any of these scripts in case I made a mistake, or in case you make one during the process.

When you're all done, drop the users_to_delete table as it's no longer required.

_________________
phpBBDoctor Blog
Back to top
Sylver Cheetah 53
Board Member



Joined: 17 Dec 2008

Posts: 426
Location: Milky Way


flag
PostPosted: Sat Feb 07, 2009 4:44 pm 
Post subject: Re: Take out website from member with 0 posts

It seems too hard for me. icon_sad.gif

Anyway, how can I make site not a link in viewprofile_body.tpl? icon_rolleyes.gif

_________________
Image link
My Forum || My Blog

phpBB2 forever! icon_smile.gif
Back to top
Ram
Board Member



Joined: 23 Dec 2008

Posts: 100
Location: Somewhere over the rainbow


flag
PostPosted: Sat Feb 07, 2009 4:56 pm 
Post subject: Re: Take out website from member with 0 posts

Isn't it possible by using admin toolkit?
Back to top
Sylver Cheetah 53
Board Member



Joined: 17 Dec 2008

Posts: 426
Location: Milky Way


flag
PostPosted: Sun Feb 08, 2009 9:03 am 
Post subject: Re: Take out website from member with 0 posts

It has nothing to do with it.

Image link

I want that link not to be a link anymore, but just a text. icon_wink.gif

_________________
Image link
My Forum || My Blog

phpBB2 forever! icon_smile.gif
Back to top
roadhog
Board Member



Joined: 18 Nov 2008

Posts: 96
Location: Central Texas


flag
PostPosted: Sun Feb 08, 2009 1:24 pm 
Post subject: Re: Take out website from member with 0 posts

You should be able to do that by adding a slash to the "display" code, ahead of the "http" with no space between it and the "h".
Back to top
Sylver Cheetah 53
Board Member



Joined: 17 Dec 2008

Posts: 426
Location: Milky Way


flag
PostPosted: Sun Feb 08, 2009 5:58 pm 
Post subject: Re: Take out website from member with 0 posts

Yes, but where? icon_sad.gif
In profile_view_body.tpl I have this:
Code:
<td align="right" nowrap="nowrap" class="explaintitle">{L_WEBSITE}:</td>
<td onMouseOver="this.style.backgroundColor='#006699'; " onMouseOut=this.style.backgroundColor="#003E5E">{WWW}</td>
</tr>

There is no HTTP, I do not understand how come it becomes a link. icon_cry.gif

_________________
Image link
My Forum || My Blog

phpBB2 forever! icon_smile.gif
Back to top
dogs and things
Board Member



Joined: 18 Nov 2008

Posts: 628
Location: Spain


flag
PostPosted: Sun Feb 08, 2009 7:59 pm 
Post subject: Re: Take out website from member with 0 posts

Have a look in includes/usercp_viewprofile.php. There you can find the code you have to change, if I'm not mistaken.
_________________
phpBB2 will never die, I hope!
Back to top
roadhog
Board Member



Joined: 18 Nov 2008

Posts: 96
Location: Central Texas


flag
PostPosted: Sun Feb 08, 2009 11:35 pm 
Post subject: Re: Take out website from member with 0 posts

I'm no programmer, so this may need to be polished a bit, to be aesthetically pleasing, (and technically correct), but this will do what you want to do. Find this line in usercp_viewprofile.php:

Code:
$www = ( $profiledata['user_website'] ) ? '<a href="' . $profiledata['user_website'] . '" target="_userwww">' . $profiledata['user_website'] . '</a>' : '&nbsp;';


The "<a" and the "</a>" convert everything in between into a link. Just change it to:

Code:
$www = ( $profiledata['user_website'] ) ? '' . $profiledata['user_website'] . '" target="_userwww">' . $profiledata['user_website'] . '' : '&nbsp;';


Or take out the "target" part of it also, if you want to keep it neat.
Back to top
Sylver Cheetah 53
Board Member



Joined: 17 Dec 2008

Posts: 426
Location: Milky Way


flag
PostPosted: Mon Feb 09, 2009 4:02 am 
Post subject: Re: Take out website from member with 0 posts

I've changed that line to this:
Code:
$www = ( $profiledata['user_website'] ) ? '' . $profiledata['user_website'] . '' : '&nbsp;';

It seems to do the job just fine. icon_biggrin.gif Thanks Dogs and Things, thanks Roadhog. icon_wink.gif

_________________
Image link
My Forum || My Blog

phpBB2 forever! icon_smile.gif
Back to top
Display posts from previous:   
Register or Login to Post    Index » MOD Requests  Previous TopicPrint TopicNext Topic
Page 1 of 1 All times are GMT - 4 Hours
 
Jump to:  

Index • About • FAQ • Rules • Privacy • Search •  Register •  Login 
Not affiliated with or endorsed by the phpBB Group
Powered by phpBB2 © phpBB Group
Generated in 0.0731 seconds using 16 queries. (SQL 0.0115 Parse 0.0011 Other 0.0605)
phpBB Customizations by the phpBBDoctor.com
Template Design by DeLFlo and MomentsOfLight.com Moments of Light Logo