0

I would like to change the post status for particular month via MySQL command Using SSH...Can you anyone tell me how to do this via SSH. I have number of post more than 40,000 posts, each month i'm having arround 2000 / 3000 posts, So before going to do the bulk update i have to be careful in SSH MySql commands.

Thanks in advance.

  • Have you considered using WP CLI? – Tom J Nowell Dec 10 '14 at 11:02
  • I'm not familiar with that, can you guide me ? – John Peter Dec 10 '14 at 11:17
1

Use WP CLI!

First grab the posts you want, we'll use the wp post list command, and we need to run the command in our WordPress directory on the server. Here I'm grabbing all posts in October 2014 on my blog:

wp post list --year=2014 --monthnum=10

This gives me ( oh noes my secret draft posts ):

+-----+-------------------------------+-------------------------------+---------------------+-------------+
| ID  | post_title                    | post_name                     | post_date           | post_status |
+-----+-------------------------------+-------------------------------+---------------------+-------------+
| 534 | In Defence of WordPress       | defence-of-wordpress          | 2014-10-23 23:23:37 | draft       |
| 588 | WordCamp Europe Vs PHPNW 2014 | wordcamp-europe-vs-phpnw-2014 | 2014-10-09 14:46:32 | draft       |
+-----+-------------------------------+-------------------------------+---------------------+-------------+

But we want something that's easy to parse, so lets output it as a csv:

wp post list --year=2014 --monthnum=10 --format=csv

I've used the csv format so that I can parse it, this gives me:

ID,post_title,post_name,post_date,post_status
534,"In Defence of WordPress",defence-of-wordpress,"2014-10-23 23:23:37",draft
588,"WordCamp Europe Vs PHPNW 2014",wordcamp-europe-vs-phpnw-2014,"2014-10-09 14:46:32",draft

We only need the IDs though, so lets change our command to:

wp post list --year=2014 --monthnum=10 --format=csv --fields=ID

Giving us:

ID
534
588

We now have something we can loop over in a shell script! Instead of executing an sql query, you can run a shell script e.g. updateposts.sh and execute that instead

cd /my/path/to/wordpress/
posts=$(wp post list --year=2014 --monthnum=10 --format=csv --fields=ID);
for post in $posts; do
    (
        if [[ "$post" == "ID" ]]; then
            echo "skipping header column"
        else
            # update the post status
        fi
    )
done

To update the post status, we can use the wp post update command. E.g.:

wp post update 123 --post_status=draft

Putting this all together we get:

cd /my/path/to/wordpress/
posts=$(wp post list --year=2014 --monthnum=10 --format=csv --fields=ID);
for post in $posts; do
    (
        if [[ "$post" == "ID" ]]; then
            echo "skipping header column"
        else
            wp post update $post --post_status=publish
        fi
    )
done

Now everything in October 2014 on my blog is going to be a published post if I run that. Of course you will want to adjust the date, and the post status it sets. You may also want to limit what posts it grabs even further. If you do, look at the query args for WP_Query, they are the same as the parameters taken by wp post list.

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.