move members

[ArAb4NoKiA]

Hello,

i want an SQL Querie to move the members that has not entered the forum for one year (365Days) to Banned group and i want to put ban reason if i can

i know that i can do it via :
admincp/user.php?do=prune

but it's about 40,000 Member !!

[Jose Amaral Rego]

You are better off use functions that are present, as you get support if errors occur. This on the other hand you better have a working backup of your database.
I not 100% sure I did this right as I have not test this on my test forum.

UPDATE user
SET usergroupid = 8
WHERE usergroupid = (1, 2, 3, 4)
AND lastactivity > UNIX_TIMESTAMP('2006-06-16 00:00:00')
AND lastactivity < UNIX_TIMESTAMP('2007-06-16 00:00:00')

[Andy Huang]

Woah! Don't use the "joindate" filed on the table or else you're gonna ban a lot of old (perhaps even regular) members! Instead, use the "lastactivity" or "lastvisit" field so that it actually gets people who didn't login/do stuff for a long time.

Additionally, I don't know if just updating the table alone will do the trick... there might be something that you must do w/ the userban table... which its what's keeping me puzzled on this issue as I have no place/method to actually test this without affecting any real user.

To insert a reason, you'd definetly need to touch the userban table though. So might want to get a custom script to achieve this effect for you, and get it to automatcially grab all the necessairy informations...

Be sure to update your counters by the time you're done w/ all these querie, because I'm sure there's some level of cacheing going on which those users are related to (IE: postbits, caches, etc).

[Jose Amaral Rego]

My bad, that would of been just a bad day on my part not seeing that.

Thanks for pointing it out.. I guess I should test to see, before adding to post. I still think what is on standard vBulletin is still the best way of doing this and also you need to have better control of what is going on with members on your forum. You just end up doing the same by having user register and not coming back.

[ArAb4NoKiA]

Hello,

thanks guys

but can i have another SQL Query

i want to delete the members not to move them

is that posibale ?

[Jose Amaral Rego]

Admin CP > Users > Prune / Move Users

[ArAb4NoKiA]

Hello,

i couldn't do it because it's about 40,000 Member !

that's way i need it SQL Quire

[Jose Amaral Rego]

Wait till you see Staff answer, I would hate to see this inadvertaly delete more than it is suppose or not enough.
01 DAY=86400
30 DAY(s)=2592000
60 DAY(s)=5184000
90 DAY(s)=7776000

DELETE * FROM user WHERE usergroupid = (1, 2, 3, 4, 8)
AND lastvisit > unix_timestamp(now()) - 5184000
AND lastvisit < unix_timestamp(now()) - 2592000

[ArAb4NoKiA]

Ok thanks

waiting for the staff respond

[Colin F]

Deleting members isn't easily possible with a single SQL query. Seeing as the move/prune users function is paginated you should use that.

[ArAb4NoKiA]

Hello,

i couldn't do it with move/prune function

after i give the delete order it redirect me to page it take 2Min for loading then i have an error message

this is the error message :
No Users Matched Your Query

!!!

[Colin F]

I'm afraid it's not easily possible with just a query. You might be able to make a shell script or something. I recommend you ask for help on www.vbulletin.org