0

I'm looking for an efficient way to change the post status on many posts at once. I have a custom post type that I'm using as a means to store content that must be approved by a moderator before being published. I'm trying to give the moderator the ability to "approve all" or "reject all" on a fairly large number of posts (100-200 would be common).

The best solution I've come up with is to make an ajax call that sends a list of post id's to the server, which loops through them calling the below function:

wp_update_post(array('ID'=> $post_id, 'post_status' => 'publish' ))

This results in a query to the DB for each post, however, and it's much slower than I'd like. Ideally, I'd be able to make a query that changes the post status to 'publish' on all of the post ID's in the list at once, but I can't find any means to do this.

  • I don't have time to dig into this right now, but you might look at how the core Bulk Edit tool works. Not that core is perfect... :) – mrwweb Mar 27 '14 at 15:38
2

What you are doing is the safe way, and the way I'd recommend. To my knowledge, there is no Core function to bulk update only single values in the $wpdb->posts table, so to do that you'd need to write your own SQL...

$ids = array(); // your IDs; I am assuming these to be validated and sanitized
$wpdb->query("UPDATE {$wpdb->posts} SET post_status = 'publish' WHERE ID IN  (".implode(',',$ids)).")");

You might be able to do the same with $wpdb->update but I rarely use that method, so I am not sure. (A comment below indicates that it is not possible.) Check the Codex and play with it if you want.

I would recommend not doing either, though, and continuing to use wp_update_post. If you skip around the Core functions you also skip around numerous actions and filters, and may cause yourself unintended consequences or other frustrations later on.

  • 1
    I second your thought on continuing to use wp_update_post. Hopefully taking away your unsureness about using $wpdb->update: this is not possible, as you can neither use the IN-operator (the WHERE-clause will be automatically generated as [column] = [value], i.e. you can not specify the operator used) nor use the OR-operator on the different WHERE-clauses (the WHERE-clauses are automatically joined with ANDs). – engelen Mar 27 '14 at 14:23
  • Would there be a better approach than using IDs, then? All of the posts in question are of the same custom post type and have the same post status. Is there a way to select them all using this criteria that's faster than what I'm doing (effectively: WHERE post_type = custom_post_type AND post_status = status)? – Josh Vickerson Mar 27 '14 at 15:32
  • No. The ID is probably the single best field to use. You can try using additional conditions but I doubt you'd see a significant improvement. The slow-down is going to be the insert, not the select. – s_ha_dum Mar 27 '14 at 15:42
  • Using MySQL Workbench, I watched the activity on the MySQL server when using a couple methods to do this. If I do a loop over all the post ids and call wp_update_post() on 100 posts, my server deals with over 500 queries per second. Alternatively, if I use my own function and pass it a post id and do the following, the server only shows activity of 8 queries per second and completes the change noticeably faster: $wpdb->query( $wpdb->prepare( "UPDATE $wpdb->posts SET post_status = 'publish' WHERE ID = %s", $post_id)); What risks are involved by doing it this way? – Josh Vickerson Mar 27 '14 at 20:55

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.