Pruning users with sql

[John Lester]

I find the default user pruning options to be pretty lame so I want to run a query or two to delete some spammers rather than going through them one by one.

I did some searching but either nobody has done what I want, or I'm waaaay off base on how to do it

Can you use multiple conditions in a query like that? Would you just use AND or is there a more efficient way? An example of what I want to do (and yes it probably isn't right ) is below. I'm not sure if I can use lastvisit (or what value to use) and I guess it's not critical but sure would be better if I could.

DELETE FROM 'users' WHERE 'username' LIKE %! AND 'postcount' =0 AND 'lastvisit' =

I want to delete users with ! in their names, have zero posts, and have not visited in X days.

Is it possible to use more than the ! in the query (ie ! and #)?

--------------- Added [DATE]1340372363[/DATE] at [TIME]1340372363[/TIME] ---------------

Would it be possible to use the condition that they have a url in their homepage?

[kh99]

Originally Posted by John Lester

I want to delete users with ! in their names, have zero posts, and have not visited in X days.

Is it possible to use more than the ! in the query (ie ! and #)?

DELETE FROM user WHERE (username LIKE '%!%' OR username LIKE '%#%') AND postcount = 0 AND lastactivity < (UNIX_TIMESTAMP() - (86400 * X))

You'd replace X with the number of days of inactivity. I removed the quotes from table and field names because they'd would need to be 'back ticks' and not single quotes, but they normally aren't needed anyway.

Would it be possible to use the condition that they have a url in their homepage?

You could use a regular expression to match a url (if you google you'll find a number of examples), but I suppose if you're not concerned in matching only valid urls, you could just add "AND homepage LIKE '%http://%'.


Also I should mention, I've seen other people suggest deleting users with a query like this, but it doesn't remove everything the way deleting from the adminCP does. I guess you're going for users who don't have any posts (and so wouldn't have any attachments), and probably wouldn't have any PMs, but there will still be rows in the userfield and usertextfield tables. But I guess it works because none of those things would cause problems.

[John Lester]

Thank you Kevin

I didn't think about it leaving anything behind in the userfield and never even thought about the usertextfield Not sure why I used ' when I darn well know it's ` *shrug*, and about them not being needed "usually" I did not know

I don't want to do it half assed and leave crumbs behind. I'll just do it in batches through the acp then

Although I would like to ask, in the example %!% would that include users that have more than one ! ? I would assume so, but that's gotten me into trouble before

[kh99]

Originally Posted by John Lester

I don't want to do it half assed and leave crumbs behind. I'll just do it in batches through the acp then

I was wondering if doing a JOIN of those tables would delete the rows from those tables as well. But I don't even know if you can have a join in a delete statement.

I didn't think about it leaving anything behind in the userfield and never even thought about the usertextfield Not sure why

I believe so. I'd have to test it to know for sure.

After posting this I was out going about my business and was thinking that I really should have tested that sql before posting it, because a typo could have ended up deleting all your users or something. You should probably do something like test with a SELECT before doing the delete (if you end up doing it that way).

Another thing you could do is write some php to do it (maybe steal the "delete user" code from the admincp), then set it up as a scheduled task (if it hasn't been done already - I didn't do a search of mods).

[John Lester]

Oh I don't use the code on the live site until it's been used and abused on the test site I have yet to use it though because now you have me wondering about joining those tables