I use WP-CLI constantly, and the ability to --format
many of its commands to output results as JSON (or a CSV, or a nice table printed in your terminal) is a powerful tool for routing WP-CLI output into other commands. The wp db query
command doesn’t support --format
, though—it outputs the results from MySQL directly, and that output is designed for eyes, not machines. Even if all you want is a list of IDs, you can’t use this output in another application without piping it through a variety of cleaning steps:
wp db query 'select ID from wp_posts where post_status like "draft" limit 10'
+-------+
| ID |
+-------+
| 9830 |
| 10169 |
| 13612 |
| 13616 |
| 13617 |
| 13620 |
| 13621 |
| 13622 |
| 13623 |
| 13624 |
+-------+
I was frustrated by this again yesterday, and realized I’d missed a key comment in the wp db query documentation page:
# Pass extra arguments through to MySQL
$ wp db query 'SELECT * FROM wp_options WHERE option_name="home"' --skip-column-names
+---+------+------------------------------+-----+
| 2 | home | http://wordpress-develop.dev | yes |
+---+------+------------------------------+-----+
“Pass extra arguments through to MySQL,” you say? That’s exactly what we want! With --skip-column-names
and --silent
(to remove the | |
table-formatting) we now receive a list of IDs on newlines, which can easily be used as input for other commands:
wp db query 'select ID from wp_posts where post_status like "draft" limit 10' --skip-column-names --silent
9830
10169
13612
13616
13617
13620
13621
13622
13623
13624
If we wanted to bulk-publish these draft posts, for example, we can pass this ID list to wp post update
:
wp post update $( wp db query 'select ID from wp_posts where post_status like "publish" limit 10' --skip-column-names --silent ) --post_status=publish
The nested commands can get unwieldy, but knowing how to get query output without the table formatting has already significantly sped up some tricky bulk tasks. It’s been a major time saver for me, and I hope it is useful to you as well.