Register Members List Search Today's Posts Mark Forums Read

Reply
 
Thread Tools
  #1  
Old 25 Sep 2013, 13:23
alirex's Avatar
alirex alirex is offline
 
Join Date: Nov 2007
SQL Querry required for Deleting users not active.

Dear All

I need an SQL query to run from phpmyAdmin to remove all the users who are not active since 100 days. I tried from adminCP but looks like it is huge number of spammer and non active users which make my whole site getting slower. So i feel its good to run a query instead of doing it from AdminCP.

So if someone know kindly give answer.

Thank You in Advance.

I have searched from google alot but looks like i am not able to find one
Reply With Quote
  #2  
Old 25 Sep 2013, 13:32
ForceHSS's Avatar
ForceHSS ForceHSS is offline
 
Join Date: Apr 2008
have you tried doing it with prune options. Also best not to delete users with posts just make a new group and move them to that one
Reply With Quote
  #3  
Old 25 Sep 2013, 15:43
alirex's Avatar
alirex alirex is offline
 
Join Date: Nov 2007
Originally Posted by ForceHSS View Post
have you tried doing it with prune options. Also best not to delete users with posts just make a new group and move them to that one
I actually mentioned in my post but anyways.. I have tried from admincp to prune but it start lagging my site bcoz too many users are in the list.

My forum is readonly so there is no such loss for me, most of my active users are visitors they dont care to make account. Most of accounts are of Bots/Spammer/Spider etc
Reply With Quote
  #4  
Old 25 Sep 2013, 17:12
ChilaxinC ChilaxinC is offline
 
Join Date: Dec 2011
I am not responsible for any damage caused by you following this!
***THIS IS NOT RECOMMENDED FOR USERS WITH POSTS***
I put that disclaimer, but I did test it myself just to be sure

In phpMyAdmin, under your vBulletin database, look for the table "user".

This shows you all sorts of information, including the "lastactivity". This is based on Unix time, for example on my users table last activity, the first date I see is: 1380060934, which is 9/24/13 5:15:34pm EST

You can choose a date that you would like to remove users before, you wanted 100 days ago, which is June 17, 2013 ( Add/subtract Dates Here )

Go here to get the Unix time of a date: http://www.unixtimestamp.com/index.php

June 17, 2013 in Unix time is: 1371445200

Now to delete the users before that timestamp should look something like:

Block Disabled:      (Update License Status)  
Suspended or Unlicensed Members Cannot View Code.

*** DO NOT USE THIS IF YOUR USERS HAVE POSTS***

If your users may have posts use the below code instead, the above was only for alirex's special case.

Block Disabled:      (Update License Status)  
Suspended or Unlicensed Members Cannot View Code.


Last edited by ChilaxinC; 25 Sep 2013 at 22:14.
Reply With Quote
  #5  
Old 25 Sep 2013, 19:27
alirex's Avatar
alirex alirex is offline
 
Join Date: Nov 2007
Originally Posted by ChilaxinC View Post
I am not responsible for any damage caused by you following this!
I put that disclaimer, but I did test it myself just to be sure

In phpMyAdmin, under your vBulletin database, look for the table "user".

This shows you all sorts of information, including the "lastactivity". This is based on Unix time, for example on my users table last activity, the first date I see is: 1380060934, which is 9/24/13 5:15:34pm EST

You can choose a date that you would like to remove users before, you wanted 100 days ago, which is June 17, 2013 ( Add/subtract Dates Here )

Go here to get the Unix time of a date: http://www.unixtimestamp.com/index.php

June 17, 2013 in Unix time is: 1371445200

Now to delete the users before that timestamp should look something like:

Block Disabled:      (Update License Status)  
Suspended or Unlicensed Members Cannot View Code.

Thank you , it works without problem. Its really helpfull query specially when alot of spammer need to be remove. From admincp it was easy if number of members are less. But now its good.

Thanks once again.
Reply With Quote
  #6  
Old 25 Sep 2013, 19:34
ForceHSS's Avatar
ForceHSS ForceHSS is offline
 
Join Date: Apr 2008
I hope you only deleted the members with 0 posts if I was not so busy I could of helped more
Reply With Quote
  #7  
Old 25 Sep 2013, 19:36
alirex's Avatar
alirex alirex is offline
 
Join Date: Nov 2007
Originally Posted by ForceHSS View Post
I hope you only deleted the members with 0 posts if I was not so busy I could of helped more
Yes as i said above it is read only forum , so guest and registered members are not able to make any post. Only staff can post content rest all are watching it or discussing in shoutbox.
Reply With Quote
  #8  
Old 25 Sep 2013, 19:39
ForceHSS's Avatar
ForceHSS ForceHSS is offline
 
Join Date: Apr 2008
So u deleted members with 0 posts good will be less problems
Reply With Quote
  #9  
Old 25 Sep 2013, 22:12
ChilaxinC ChilaxinC is offline
 
Join Date: Dec 2011
Originally Posted by alirex View Post
Thank you , it works without problem. Its really helpfull query specially when alot of spammer need to be remove. From admincp it was easy if number of members are less. But now its good.

Thanks once again.
No problem at all, and like ForceHSS said this is not recommended to use if you have a forum with users that have posts, but a simple change to the query could prevent problems of that nature:

Block Disabled:      (Update License Status)  
Suspended or Unlicensed Members Cannot View Code.

Now it is only deleting users that have 0 posts and the last activity was before the timestamp.
Reply With Quote
  #10  
Old 26 Sep 2013, 00:35
Zachery's Avatar
Zachery Zachery is offline
 
Join Date: Jul 2002
Real name: Zachery Woods
You should really use the prune function, and decrease the number of users you're doing in a single pass. You're not removing all of the user data correctly if you only drop from the user table.
__________________
Looking for ImpEx?
Reply With Quote
Reply



Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)
 
Thread Tools

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off


New To Site? Need Help?

All times are GMT. The time now is 09:43.

Layout Options | Width: Wide Color: