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

Question

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.

Related Posts: