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!

2

3 Answers 3

1

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


 
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.

1
  • Thanks Mihai!! I don't really know how / where to put in that kind of query. Would I have to write a plugin? Commented Apr 3, 2017 at 15:20
0

If you only need to run something once, or occasionally, instead of a plugin you can also create another php file you can run it from on the side without impacting your site's speed.

Just

require_once('wp-load.php'); global $wpdb;

Then you can do whatever you want or need, using $wpdb

Load it from your browser to run it. If you want the look and feel of your site you can:

get_header(); { your code here } get_footer();

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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