0

Is there a method/plugin by which we can filter users by email address? I have lots of spam posts recently coming from users of a specific email domain.

  • 1
    Users or commentators? – fuxia Jun 2 '13 at 9:44
  • users (Contributors) and more specifically bots that keep creating posts. I see lots of pending posts created by these bots. – Monty Swanson Jun 2 '13 at 12:51
  • See my updated answer. I made two widgets – one for each type. – fuxia Jun 2 '13 at 13:13
2

I wasn’t sure if you meant users or commentators, so I took both.

SQL

The email is saved in $wpdb->comments in the column comment_author_email for comments.
It is saved in $wpdb->users in the column user_email for users.

To get the domain part from the email in comments, we can use:

SELECT (
    SUBSTR(
        `comment_author_email`,
        INSTR( `comment_author_email`, '@' ) + 1,
        LENGTH( `comment_author_email` ) - INSTR( `comment_author_email`, '@' )
    )
) AS domain

A complete SQL query to get the most used domains from WordPress’ comments table could look like this:

SELECT (
    SUBSTR(
        `comment_author_email`,
        INSTR( `comment_author_email`, '@' ) + 1,
        LENGTH( `comment_author_email` ) - INSTR( `comment_author_email`, '@' )
    )
) AS domain,
COUNT(*) AS amount
FROM        `$wpdb->comments`
WHERE       `comment_approved` = 'spam'
GROUP BY    domain
HAVING      amount > 1
ORDER BY    amount DESC
LIMIT 0, 150

Dashboard Widget

I have used that query to create a simple dashboard widget.

Download on GitHub.

screenshot

<?php # -*- coding: utf-8 -*-
namespace WPSE;
/**
 * Plugin Name: Top Mail Domains
 * Description: List the most used domain names from spammers and users in two dashboard widgets.
 * Version:     2013.06.02
 * Author:      Thomas Scholz
 * Author URI:  http://wpkrauts.com
 * License:     MIT
 */

\is_admin() && \add_action(
    'wp_dashboard_setup',
    array (
        Top_Spam_Mail_Domains::get_instance(),
        'setup'
    )
);

class Top_Spam_Mail_Domains
{
    protected static $instance;

    public static function get_instance()
    {
        if ( NULL === self::$instance )
            self::$instance = new self;

        return self::$instance;
    }

    public function setup()
    {
        $comment_model  = new Comment_Data;
        $comment_widget = new Widget( $comment_model );

        \wp_add_dashboard_widget(
            'top_comment_spam_domains',
            'Top Comment Mail Domains',
            array ( $comment_widget, 'display' )
        );

        $user_model  = new User_Data;
        $user_widget = new Widget( $user_model );

        \wp_add_dashboard_widget(
            'top_user_spam_domains',
            'Top User Mail Domains',
            array ( $user_widget, 'display' )
        );
    }
}

class Widget
{
    protected $source;

    public function __construct( Data $source )
    {
        $this->source = $source;
    }

    public function display()
    {
        $data = $this->source->get_domains(1);

        if ( empty ( $data ) )
            return print 'No domains found.';

        print $this->data_to_table( $data );
    }

    protected function data_to_table( Array $data )
    {
        $table  = '<table class="widefat">';
        $header = '<tr><th>Domain</th><th>Amount</th></tr>';
        $table .= "<thead>$header</thead><tfoot>$header</tfoot>";

        foreach ( $data as $result )
            $table .= sprintf(
                '<tr class="%1$s"><td>%2$s</td><td>%3$s</td></tr>',
                $this->row_class(),
                \esc_html( $result->domain ),
                \esc_html( $result->amount )
            );

        return "$table</table>";
    }

    protected function row_class()
    {
        static $count = 1;

        return ( $count++ % 2 ) ? '' : 'alt';
    }
}

abstract class Data
{
    public function get_domains( $min = 5 )
    {
        global $wpdb;

        $sql = $this->get_sql( absint( $min ) );
        return $wpdb->get_results( $sql );
    }

    abstract protected function get_sql( $min );
}

class Comment_Data extends Data
{
    protected function get_sql( $min )
    {
        global $wpdb;

        return "
SELECT (
    SUBSTR(
        `comment_author_email`,
        INSTR( `comment_author_email`, '@' ) + 1,
        LENGTH( `comment_author_email` ) - INSTR( `comment_author_email`, '@' )
    )
) AS domain,
COUNT(*) AS amount
FROM        `$wpdb->comments`
WHERE       `comment_approved` = 'spam'
GROUP BY    domain
HAVING      amount > $min
ORDER BY    amount DESC
LIMIT 0, 150
        ";
    }
}


class User_Data extends Data
{
    protected function get_sql( $min )
    {
        global $wpdb;

        return "
        SELECT (
        SUBSTR(
        `user_email`,
        INSTR( `user_email`, '@' ) + 1,
        LENGTH( `user_email` ) - INSTR( `user_email`, '@' )
        )
        ) AS domain,
        COUNT(*) AS amount
        FROM        `$wpdb->users`
        GROUP BY    domain
        HAVING      amount > $min
        ORDER BY    amount DESC
        LIMIT 0, 150
        ";
    }
}

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.