Help Deleting Multiple Subscribers

[morestar]

Good day members of WebProWorld.com.

Currently I'm having hundreds of people subscribing to my article directory, people whom I can tell have no interest in submitting articles and I would like to get rid of them.

The problem is that there are thousands and Wordpress's current way of dealing with subscribers/members turns out to be a really slow process.

Does anyone know of a way I can delete the whole shebang of subscribers? Admins, Editors and Authors are permitted but I don't want that huge list of subscribers on my site.

Any help with this will be appreciated.

:)

[HTMLBasicTutor]

I'm not sure why you think this is a slow process (maybe I found the wrong article at Wordpress?) Users Users SubPanel (http://codex.wordpress.org/Users_Users_SubPanel)

What I was looking for was a way to delete via the database but didn't find it yet.

[morestar]

Yes I found deleting subscribers through the database isn't as easy as we'd suspect it to be.

Yes the information at the link you sent me is the way I have been doing the deleting but there are thousands of subscribers and it would take a long time going page by page.

Still looking...

[ronniethedodger]

Maybe wrangling this bit of code from user.php may help. I am sure there may be a way to call all those dudes up with a little tweaking.



function get_users_of_blog( $id = '' ) {
global $wpdb, $blog_id;
if ( empty($id) )
$id = (int) $blog_id;
$blog_prefix = $wpdb->get_blog_prefix($id);
$users = $wpdb->get_results( "SELECT user_id, user_id AS ID, user_login, display_name, user_email, meta_value FROM $wpdb->users, $wpdb->usermeta WHERE {$wpdb->users}.ID = {$wpdb->usermeta}.user_id AND meta_key = '{$blog_prefix}capabilities' ORDER BY {$wpdb->usermeta}.user_id" );
return $users;
}

[chrisJumbo]

You should be able to delete them with a query using phpMyadmin. The query provided by Ronnie shouldn't be that hard to tweak so that you can delete by type. Of course back-up your tables before running a delete query. :O)

[ronniethedodger]

I tried using some join syntax, but my SQL is pretty rusty. Beyond rusty, just plain rotten. I can do simple one-table queries.

Another possibility that comes to mind is creating a relationship. I think there is a utility in phpMyAdmin that should work out pretty well for linking the two tables together via the user_id columns.

[ronniethedodger]

If you do come up with the SQL syntax, it would make for a nice plugin! ;)

[morestar]

Hey Ronnie, ok before I go and throw the snippet you provided into user.php, what's it going to do?

I see that it will select 'users' but...

what, where, when, how?

[ronniethedodger]

I pulled that code from users.php and by the looks of it, it is fairly close to what you need it to do.

The select statement is the important part here:


SELECT user_id, user_id
AS ID, user_login, display_name, user_email, meta_value
FROM $wpdb->users, $wpdb->usermeta
WHERE {$wpdb->users}.ID = {$wpdb->usermeta}.user_id
AND meta_key = '{$blog_prefix}capabilities'
ORDER BY {$wpdb->usermeta}.user_id

There are two tables and this select combines the two by linking usermeta.user_id with users.id

It is the part after the AND (in red) that needs to be tweaked. This is actually pulling all of the meta_key values for "capabilities", which is what you are looking for. I think adding another AND to check for just subscribers will give you your list.


SELECT user_id, user_id
AS ID, user_login, display_name, user_email, meta_value
FROM $wpdb->users, $wpdb->usermeta
WHERE {$wpdb->users}.ID = {$wpdb->usermeta}.user_id
AND meta_key = '{$blog_prefix}capabilities'
AND meta_value LIKE '%subscriber%'
ORDER BY {$wpdb->usermeta}.user_id

If you run that select statement and have the results printed to screen, and it is all of the subscribers ... then all you have to do is execute a delete for each ID from both tables. Remember that the usermeta table has multiple occurances of the user.id to delete.

[ronniethedodger]

Actually by looking at that select statement, you can hardcode the actual table names as they appear in the database. {$wpdb->users}.ID is probably wp_users.ID, for instance. Then you will have a SQL select query that you can run in phpMyAdmin. When the list pops up ... voila ... you can mass delete like crazy.

[L R Hand]

I think that Wordpress should make the deletion and blocking of spammers etc. a lot easier. I too have to go into the Control Panel and delete them one by one. It's part of my daily routine.

I shall follow this thread with interest, to see if anyone can come up with a neat idea or plugin - I'm really not an SQL aficionado.

[ronniethedodger]

I think that Wordpress should make the deletion and blocking of spammers etc. a lot easier. I too have to go into the Control Panel and delete them one by one. It's part of my daily routine.

I shall follow this thread with interest, to see if anyone can come up with a neat idea or plugin - I'm really not an SQL aficionado.

Unless you do not have a need for subscribers, then the easiest method that I know of is to not allow them to subscribe in the first place. You can allow or disallow registrations under your General Settings. If you need just a couple of authors, then you can set the accounts up yourself. Setting the accounts with their proper email addresses will also send them an email with the password to log in with.

[dgswilson]

I've seen a lot of articles on article sites that had links to affiliate (type) product pages in the article. The last I saw was at Ezinearticles dot com. I guess my idea would be to combine subscriptions and article submissions. No one could subscribe without an accompanying article. That would eliminate a lot of bots. Next I would not allow links in articles. That would eliminate a lot of spam article writers. Now the only link to the author would be in the author's profile. Some people would still write articles just to get the link in their profile - but not as many. This process would have no negative effect on legitimate authors so article quality would increase.

[ronniethedodger]

I guess it depends on your managing style and preferences. Links in the articles that point to citations on the web should be welcomed. I think linking to those citations adds context to the article itself, and in turn supports the article. But linking to just an authors web development (http://www.webstractions.com) site all namby-pamby-like is ridiculous, you have to lay down the ground rules beforehand

[NJ]

What I was looking for was a way to delete via the database but didn't find it yet.

Personally, I find the wordpress database design to be a little odd.

[ronniethedodger]

Personally, I find the wordpress database design to be a little odd.

Odd? How so? Each CMS has its own methods for database interaction, and it just takes a little getting used to their way of thinking.

[NJ]

Odd? How so? Each CMS has its own methods for database interaction, and it just takes a little getting used to their way of thinking.

Not that I want to take away anyone's right to build a database anyway they want, but I believe that putting posts and pages and updates in the same table does not follow the best practices of database normalization.

I have found that the Akismet plugin works great to keep out spam. They do have an ongoing charge now, but the price is much less than the time cost of removing spammy comments. Although that does not answer the question of removing subscribers.

[ronniethedodger]

Not that I want to take away anyone's right to build a database anyway they want, but I believe that putting posts and pages and updates in the same table does not follow the best practices of database normalization.

Really? Since pages, posts, and their revisions all share have identical column fields, why create another table for them? The only major difference between a post and a page is one get thrown into a feed and pings, the other doesn't. I think data-wise, this IS pretty normal.


I have found that the Akismet plugin works great to keep out spam. They do have an ongoing charge now, but the price is much less than the time cost of removing spammy comments. Although that does not answer the question of removing subscribers.

Good point. There does seem to be a need for subscriber spam too. That would be a good question to ask the Akismet folk. I am sure, they can at least can half of that spam just by email address and/or IP data that they have on hand.