How to select particular month post from table and update the post status using MySQL?

Question

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.

Related Posts: