Author |
Message |
Sylver Cheetah 53 Board Member
Joined: 17 Dec 2008
Posts: 426 Location: Milky Way
|
Posted: 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! |
|
Back to top |
|
|
drathbun Board Member
Joined: 24 Jul 2008
Posts: 729 Location: Texas
|
Posted: 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
|
Posted: 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.
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. 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. _________________ Image link
My Forum || My Blog
phpBB2 forever! |
|
Back to top |
|
|
drathbun Board Member
Joined: 24 Jul 2008
Posts: 729 Location: Texas
|
Posted: 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
|
Posted: 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. _________________ Image link
My Forum || My Blog
phpBB2 forever! |
|
Back to top |
|
|
drathbun Board Member
Joined: 24 Jul 2008
Posts: 729 Location: Texas
|
Posted: 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. |
Yes, you do. 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
|
Posted: Sat Feb 07, 2009 4:44 pm Post subject: Re: Take out website from member with 0 posts |
|
|
It seems too hard for me.
Anyway, how can I make site not a link in viewprofile_body.tpl? _________________ Image link
My Forum || My Blog
phpBB2 forever! |
|
Back to top |
|
|
Ram Board Member
Joined: 23 Dec 2008
Posts: 100 Location: Somewhere over the rainbow
|
Posted: 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
|
Posted: 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. _________________ Image link
My Forum || My Blog
phpBB2 forever! |
|
Back to top |
|
|
roadhog Board Member
Joined: 18 Nov 2008
Posts: 96 Location: Central Texas
|
Posted: 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
|
Posted: Sun Feb 08, 2009 5:58 pm Post subject: Re: Take out website from member with 0 posts |
|
|
Yes, but where?
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. _________________ Image link
My Forum || My Blog
phpBB2 forever! |
|
Back to top |
|
|
dogs and things Board Member
Joined: 18 Nov 2008
Posts: 628 Location: Spain
|
Posted: 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
|
Posted: 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>' : ' '; |
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'] . '' : ' '; |
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
|
Posted: 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'] . '' : ' '; |
It seems to do the job just fine. Thanks Dogs and Things, thanks Roadhog. _________________ Image link
My Forum || My Blog
phpBB2 forever! |
|
Back to top |
|
|
|