How to set posts to draft in bulk based on the content of the post

Question

I have thousands of offline links in my posts, and I'd like to set them all to draft. I know the links are in the format example.com/fileX. So I needed a SQL query to search the post content and whatever post that contain that url will be set to draft.

I think this might be a good start:

UPDATE tb_posts 
SET post_status = 'draft' 
WHERE 

But I don't know to do the search inside the WHERE clause. I intend to use ARI Adminer Plugin to edit the database.

Answer 1

You could try to loop through all the posts and if the content contains the link, call this: change_post_status(get_the_ID(),'private'); or try draft if appropriate.

Register this function first:

function change_post_status($post_id,$status){
    $current_post = get_post( $post_id, 'ARRAY_A' );
    $current_post['post_status'] = $status;
    wp_update_post($current_post);
}