0

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

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.

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

  • I just add the authors ID to this file and run it as a SQL command? i just want to double check before i do it – user3506 Feb 25 '11 at 17:25
  • @Patrick This isn't a SQL command (although it ultimately uses some in the back-end). This uses the WordPress delete function, essentially like it would through the Dashboard. If you want to use this method, you need to save this in a .php file and then execute it. – Evan Wondrasek Feb 25 '11 at 18:33
  • @Patrick as @Evan says, you need to put that code in a php file. You can create in your root path a file called deleteposts.php, put the code inside and then execute it by entering to the address of your site (i.e: example.com/deleteposts.php). And that's it! – konus Feb 26 '11 at 6:47
0

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);

  • Thanks for all your help lads-this site is a great resource – user3506 Feb 25 '11 at 16:38
0

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!

  • Nice, just remember that this will not delete post meta data for deleted posts – Bainternet Feb 25 '11 at 16:42
  • Note: To add additional user IDs, you can change the code to ... WHERE user_id = 2 OR user_id = 3 OR user_id = 4, etc. – Evan Wondrasek Feb 25 '11 at 18:37
  • To make real queries from these pseudo-queries you must certainly add the correct JOIN clauses to link posts to their authors - otherwise you might delete all posts! – Jan Fabry May 10 '11 at 14:13

Your Answer

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