Pruning users with sql

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