SQL Querry required for Deleting users not active.

[alirex]

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

[ForceHSS]

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

[alirex]

Originally Posted by ForceHSS

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

[ChilaxinC]

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:

DELETE FROM user WHERE lastactivity < '1371445200'

*** 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.

DELETE FROM user WHERE lastactivity < '1371445200' AND posts = '0'

[alirex]

Originally Posted by ChilaxinC

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:

DELETE FROM user WHERE lastactivity < '1371445200'

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.

[ForceHSS]

I hope you only deleted the members with 0 posts if I was not so busy I could of helped more

[alirex]

Originally Posted by ForceHSS

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.

[ForceHSS]

So u deleted members with 0 posts good will be less problems

[ChilaxinC]

Originally Posted by alirex

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:

DELETE FROM user WHERE lastactivity < '1371445200' AND posts = '0'

Now it is only deleting users that have 0 posts and the last activity was before the timestamp.

[Zachery]

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.