I need a SQL command to delete specific authors and all posts connected with them

Question

I unfortunately have authors with a lot of unreadable/spam posts published (thousands some of them)

I have tried deleting through admin,but its a slow painful process-does anyone have a specific SQL command i can use that can be edited for each authors name,and delete all posts associated with that author?

Thanks in advance

Answer 1

You could also massively delete author's posts using a php file like:

require('wp-blog-header.php');
global $wpdb, $current_site;

query_posts('author=11,161,23,6');  //the ID authors 

while ( have_posts() ) : the_post();
    wp_delete_post( $post->ID, true );
endwhile;

After that you just need to delete the authors

Answer 2

This removes the posts and all associated post meta data:

DELETE a,b,c FROM `wp_posts` a
LEFT JOIN wp_term_relationships b ON (a.ID = b.object_id)
LEFT JOIN wp_postmeta c ON (a.ID = c.post_id)
WHERE a.post_author = '0';

Replace the 0 with the ID number for the post_author you wish to remove.

Answer 3

Not an SQL command but a simple function that gets an author id and deletes all of his posts, that way you delete post meta of these posts too.

function delete_posts_by_author($author_ID){
    $args = array('author' => $author_ID, 'posts_per_page' => -1);
    $the_query = new WP_Query( $args );
    if ( have_posts() ) { 
        while ( have_posts() ) { 
            the_post(); 
            wp_delete_post( $post->ID, true );
        }
    }
}

usage: delete_posts_by_author(3);

Answer 4

I'm not an SQL expert, so make sure to test this code before using it (tip: replace DELETE with SELECT * to test). Make sure to replace the indicated sections with the correct table and user information. And always make a backup before messing with your database!

Deletes posts for a specific user:

DELETE FROM <YOUR WORDPRESS POSTS TABLE> p, <YOUR WORDPRESS USERS TABLE> u WHERE u.ID = <USER ID NUMBER YOU WANT TO REMOVE>

Deletes post meta for a specific user:

DELETE FROM <YOUR WORDPRESS POSTMETA TABLE> p, <YOUR WORDPRESS USERS TABLE> u WHERE u.ID = <USER ID NUMBER YOU WANT TO REMOVE>

Deletes the user's account:

DELETE FROM <YOUR WORDPRESS USERS TABLE> WHERE ID = <USER ID NUMBER YOU WANT TO REMOVE>

Deletes the user's account meta:

DELETE FROM <YOUR WORDPRESS USERMETA TABLE> WHERE user_id = <USER ID NUMBER YOU WANT TO REMOVE>

Deletes any comments from that user:

DELETE FROM <YOUR WORDPRESS COMMENTS TABLE> where user_id = <USER ID NUMBER YOU WANT TO REMOVE>

Hope that helps!