XF 2.0 MySQL Command to Pull Up Users With "About," "Website," & Signatures

[goobernacular]

Basically I want to check for any users who have any input for their About field, Website field, or a Signature added and remove any spam.

From that point on I'll be a good boy and use the User Change Log regularly.

Can anyone help with this? It's probably best to have 3 separate commands, and if each command can attach a user to each respective field, that'd be best, otherwise I guess it's useless since we couldn't do a dashboard search for the user.

Bonus Question: If you delete old custom user fields like Homepage, Skype, Twitter, Facebook, does it still store all of that in the database or does it remove it?

 

[S Thomas]

SELECT username, xp.user_id, website
FROM xf_user_profile xp
LEFT JOIN xf_user xu ON (xu.user_id = xp.user_id)
WHERE website <> "";

Replace both occurrences of website with about / signature.

Bonus Question: If you delete old custom user fields like Homepage, Skype, Twitter, Facebook, does it still store all of that in the database or does it remove it?

They get deleted.

 

[goobernacular]

I want to confirm to anyone searching that the solution posted above by @yoloswaggerino works perfectly.

 

[goobernacular]

@yoloswaggerino, or anyone else, any tips on using this SQL snipped to get ahold of the "Occupation" custom field and the Custom Title?

 

[goobernacular]

Here's Custom Titles:

SELECT user_id, username, custom_title FROM xf_user WHERE custom_title <> ''

 

[goobernacular]

And Here's Occupation (or other custom field):

SELECT user_id, field_id, field_value FROM xf_user_field_value WHERE field_id = 'occupation' AND field_value <> ''