0

I am trying to clean up a DB of 4000+ posts and the editors TAGGED the posts that they want to keep with an "audit2017" tag. Is there a way with SQL to select all posts *without this tag and delete them?

I am not very experienced with SQL but can manage this if I had the right query.

Any help appreciated, thanks!

0

Was looking into this to help you come with a solution. I'm not able to find one for the sql panel, but I guess you could first get all the posts that do not have that tag id (as shown here https://codex.wordpress.org/Class_Reference/WP_Query):

$query = new WP_Query( array( 'tag__not_in' => array( 37, 47 ) ) );

And then delete them using some php:

  • https://developer.wordpress.org/reference/functions/wp_delete_post/
  • another example: http://wordpress.stackexchange.com/questions/48214/manually-delete-post-from-database

This plugin was perfect if the client was doing the exact opposite, adding a specific tag to all the posts he DOESN'T WANT to keep: https://wordpress.org/plugins/bulk-delete/

Hope this info is helpful.

| improve this answer | |
  • Thanks Mihai!! I don't really know how / where to put in that kind of query. Would I have to write a plugin? – pixelkicker Apr 3 '17 at 15:20
0

I just created a function that can bulk delete posts that doesn't have specific tag(s).

You need to have the IDs of the tags that you want to keep their posts. [In case you don't know how to get those IDs: Quick solution is go to tags page on the website admin panel. Click on the tag. In the new page address bar you can see the tag_id]

Replace the "TagID"s with your actual tag IDs on this line:

$tags_to_delete = array( $TagID1, $TagID2 , ... );

Then place the code at the end of your theme's functions.php file. Open your homepage. It will move all the posts that don't have specific tag(s) to trash.

This code will be run every time that you open a page in your frontend.

The runtime depends on you number of post and your server configuration. I tested it for about 3500 posts on a not very high config server and it took about 40 seconds to finish.

I strongly suggest to remove the code from your website after you are done with it.

// Change the $TagIDs with the tag IDs in your database
$tags_to_delete = array( $TagID1, $TagID2 , ... );

// This function will bulk delete posts that don't have specific tag(s)
function delete_posts_by_not_tag( $tags ) {

// WP_Query arguments
$args = array(
        'post_type'              => array( 'post' ),
        'nopaging'               => true,
        'posts_per_page'         => '-1',
         'tag__not_in'           => array( $tags ),
);

// The Query
$query = new WP_Query( $args );

// The Loop
if ( $query->have_posts() ) {

        $totalpost = $query->found_posts;
        echo "Number of Posts: " . $totalpost;

        echo "<hr>";

        while ( $query->have_posts() ) {
                $query->the_post();

                $current_id = $post->ID;
                wp_delete_post( $current_id );
                echo "Post by ID '" . $current_id . "' is deleted.<br>";
        }

        echo "<hr>";

} else {

  echo "No post found to delete!";

}

// Restore original Post Data
wp_reset_postdata();

}

// Run the function to delete all the posts that don't have specific tag(s)
delete_posts_by_not_tag( $tags_to_delete );


 
| improve this answer | |

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.