I have a lot of spam profiles on my WordPress website. They're linking to their spam sites using their profiles (homepage and description).
That is a good method to recognize them. Normal users don't write long AI generated texts with links on their profiles.
How can I bulk delete them (or first find them) with sql?
The data can be found in the wp_usermeta table in the meta_key description.
I tried this:
SELECT meta_key FROM `wp_usermeta` WHERE description != '';
but got this error message:
#1054 - Unknown column 'description' in 'where clause'
Seems that I don't understand the structure of this table at the moment.
Can you help me?
Thanks.
The correct working SQL query is:
SELECT * FROM `wp_usermeta` WHERE `meta_key` = 'description' AND `meta_value` != '';
Instead of searching in the database, you could filter users that have a description from admin users list, and then Bulk delete them.
The code displays a new button called "Spam" in admin users list, that filter users that have a description (see the screenshot below).
Try the following:
add_action( 'restrict_manage_users', 'add_filter_users_has_description', 20 );
function add_filter_users_has_description() {
echo '<input id="post-query-submit" type="submit" class="button" value="'.__('Spam').'" name="has_description">';
}
add_filter( 'pre_get_users', 'filter_users_with_description' );
function filter_users_with_description( $query ) {
global $pagenow;
if ( is_admin() && 'users.php' == $pagenow && isset($_GET['has_description']) && !empty($_GET['has_description']) ) {
$meta_query = array(
array(
'key' => 'description',
'value' => '',
'compare' => '!=',
)
);
$query->set( 'meta_query', $meta_query );
}
}
Code goes in functions.php file of your child theme (or in a plugin). Tested and works.
Addition: Display trimmed description in WordPress Users List custom column
// add a custom column admin users list
add_filter( 'manage_users_columns', 'add_spam_column_to_users_list' );
function add_spam_column_to_users_list( $columns ) {
$columns['description'] = __('Spam');
return $columns;
}
// Users custom column: Display user description (trimmed)
add_filter( 'manage_users_custom_column', 'add_description_to_users_list_spam_column', 10, 3 );
function add_description_to_users_list_spam_column( $output, $column, $user_id ) {
if ( 'description' === $column ) {
if ( $description = get_user_meta( $user_id, 'description', true ) ) {
$output = '<small style="word-break:break-word;">'.wp_trim_words( $description, 5 ).' ...</small>';
}
}
return $output;
}
Code goes in functions.php file of your child theme (or in a plugin). Tested and works.