How to Modify this $wpdb query to accept an array of post statuses

3 comments

1. Use the IN operator for arrays.

   When using $wpdb->prepare() you don’t have to use quotes around the string placeholder %s. The string placeholder will be quoted automatically.
   That’s also the reason why you have to take precautions when using $wpdb->prepare() with the IN operator if the array values are strings.

   This first example doesn’t use the placeholders for the post stati but inserts them (comma separated and quoted) in the query string directly

   function get_meta_values( $key = '', $type = 'post', $status = array( 'publish', 'draft' ) ) {
       global $wpdb;
   
       if ( empty( $key ) ) {
           return;
       }
   
       // escape the status values
       $status = array_map( 'esc_sql', (array) $status );
   
       // $status_string: a comma separated string with quoted post stati
       // $status_string = "'publish', 'draft'";
       $status_string = "'" . implode( "', '", $status ) . "'";
   
       $query = "
           SELECT pm.meta_value FROM {$wpdb->postmeta} pm
           LEFT JOIN {$wpdb->posts} p ON p.ID = pm.post_id
           WHERE pm.meta_key = '%s'
           AND p.post_status IN ( $status_string )
           AND p.post_type = '%s'";
   
       $r = $wpdb->get_col( $wpdb->prepare( $query, $key, $type ) );
   
       return $r;
   }
   

   This second example converts the array with the method from this answer https://stackoverflow.com/a/10634225

   function get_meta_values( $key = '', $type = 'post', $status = array( 'publish', 'draft' ) ) {
       global $wpdb;
   
       if ( empty( $key ) ) {
           return;
       }
   
       // $status_string: a comma separated string with string placeholders (%s)
       // $status_string = "%s, %s";
       $status_string = implode( ', ', array_fill( 0, count( $status ), '%s' ) );
   
       //parameters array for call_user_func_array callback function $wpdb->prepare
       $prepare = array();
   
       // the query is the first parameter
       $prepare[] = "
           SELECT pm.meta_value FROM {$wpdb->postmeta} pm
           LEFT JOIN {$wpdb->posts} p ON p.ID = pm.post_id
           WHERE pm.meta_key = %s
           AND p.post_status IN ($status_string)
           AND p.post_type = %s
       ";
   
       // from here on out add to the $prepare array in the same order as inside the query
       $prepare[] = $key;
   
       foreach ( $status as $s ) {
           $prepare[] = $s;
       }
   
       $prepare[] = $type;
   
       // calling $wpdb->prepare() with the $prepare array
       $query = call_user_func_array( array( $wpdb, 'prepare' ), $prepare );
   
       $r = $wpdb->get_col( $query );
   
       return $r;
   }
   

   Note: both examples should be coded more defensively (is_array() etc.) if used in production.

2. Just implode the passed array:

   function get_meta_values( $key = '', $type = 'post', $status = array( 'publish', 'draft' ) ) {
       global $wpdb;
   
       if( empty( $key ) )
           return;
   
       //First escape the status, since we don't use it with $wpdb->prepare()    
       $status = esc_sql( $status );
   
       //If its an array, convert to string
       if( is_array( $status ) ){
           $status = implode( ', ', $status ); //e.g. "publish, draft"
       }
   
       $r = $wpdb->get_col( $wpdb->prepare( 
           "SELECT pm.meta_value FROM {$wpdb->postmeta} AS pm
           LEFT JOIN {$wpdb->posts} p ON p.ID = pm.post_id
           WHERE pm.meta_key = %s
           AND p.post_status IN( {$status} ) 
           AND p.post_type = %s", 
           $key, $type ) );
   
       return $r;
   }
   

   This allows you to pass $status as either a string, or an array.

3. Instead of taking status as an array take it as a string in query like

   function get_meta_values( $key = '', $type = 'post', $status = array( 'publish', 'draft' ) ) {
       global $wpdb;
   
       if ( empty( $key ) ) {
           return;
       }
   
       if( is_array( $status ) ){
           $status = implode( ', ', $status ); //e.g. "publish, draft"
       }
       $r      = $wpdb->get_col( $wpdb->prepare( "
       SELECT pm.meta_value FROM {$wpdb->postmeta} pm
       LEFT JOIN {$wpdb->posts} p ON p.ID = pm.post_id
       WHERE pm.meta_key = '%s'
       AND p.post_status IN ({$status})
       AND p.post_type = '%s'
       LIMIT 10", $key, $type ) );
   
       return $r;
   }
   

   This works, I’ve tested it.

Comments are closed.