SQL query to completely unpublish posts

  • pro99

    (@pro99)


    8 years, 1 month ago

    Hi,

    We have a site in English, with many photos. I am making a full copy of it as we start translating it to Chinese, so that the translator can start with posts already pre-populated with English, including images. In order to avoid duplicate content, I need to first unpublish all the posts in the Chinese copy. I can do this in bulk from the WordPress editor, however if I re-publish after that, the original publish date remains. We need the publish date to be current, for the Chinese site. Could someone help us find the SQL query that would remove the publish date entirely and make the posts true drafts for this new site?

Viewing 3 replies - 1 through 3 (of 3 total)
  • Moderator bcworkz

    (@bcworkz)

    8 years, 1 month ago

    Besides setting the post_status to draft, zero out post_date_gmt and empty the post_name. A PHP update query:

    global $wpdb;
$wpdb->update('wp_posts',['post_date_gmt' => '0000-00-00 00:00:00','post_name' => '','post_status' => 'draft',],
	['ID' => $post->ID,],['%s','%s','%s',],['%d',]);

    The equivalent SQL:

    UPDATE `wp_posts` SET `post_date_gmt` = '0000-00-00 00:00:00',`post_name` = '',
	`post_status` = 'draft' WHERE `ID` = $post->ID;

    Be sure you set $post->ID to the ID you want to convert to draft.

    Thread Starter pro99

    (@pro99)

    8 years, 1 month ago

    @bcworkz, thanks! To run the SQL query for all posts do I just remove WHERE ID = $post->ID ?

    Moderator bcworkz

    (@bcworkz)

    8 years, 1 month ago

    You wouldn’t want ALL records in your DB processed, there are numerous records that do not apply, such as revisions and menu items. It’s almost unheard of to run queries without some kind of WHERE clause. To convert all posts to drafts, try this clause:
    WHERE `post_type` = 'post' AND `post_status` = 'publish';

    Make a backup of your DB before running any UPDATE queries. A tiny coding oversight can completely trash your DB! You should even initially add a LIMIT clause limiting changes to only a few posts to ensure the query is working as expected. Once you are satisfied with the query, you can remove the LIMIT and process the rest of the data.

Viewing 3 replies - 1 through 3 (of 3 total)

The topic ‘SQL query to completely unpublish posts’ is closed to new replies.

Tags