1

I'm using this function from this accepted answer, which gets all values for a custom field key (cross-post).

How could it be modified to allow an array of post statuses?

function get_meta_values( $key = '', $type = 'post', $status = 'publish' ) {
    global $wpdb;

    if( empty( $key ) )
    return;

    $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 = '%s' 
        AND p.post_type = '%s'
    ", $key, $status, $type ) );

    return $r;
}

eg something like this (doesn't work)

function get_meta_values( $key = '', $type = 'post', $status = array( 'publish', 'draft' ) ) {
    global $wpdb;

    if( empty( $key ) )
    return;

    $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 = '%s' 
        AND p.post_type = '%s'
    ", $key, $status, $type ) );

    return $r;
}
3

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.

  • Thanks for your answer. As mentioned on my other comment, I can't get either answers to work, even when using a simple function call. I'll dig deeper as I believe it may be an issue on my behalf. – Andrew Aug 22 '13 at 2:32
  • SQL error messages are always less than helpful, in WP_DEBUG mode, what does it say? What does the generated SQL look like? (Update the question with this, rather than using comments). – Stephen Harris Aug 22 '13 at 10:17
  • Actually there are no errors (I have WP_DEBUG on). The function just returns NULL all the time. So puzzled – Andrew Aug 22 '13 at 11:18
  • Great example, we used it for this function pastebin.com/PL5zisDV – Q Studio Jun 25 '14 at 12:52
-1

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.

  • thanks. Is that . supposed to be there on last line? I tried it with and without the . and tried passing in the values directly but nothing worked, just getting null. Could you please modify the example to show how it should look? – Andrew Aug 21 '13 at 5:34
  • Thanks, I've tried again but I can't get the function to pull some simple posts from the DB. I think it's a problem on my end (can't get Stephen's answer to work either) so will continue to test and see what the issue is. – Andrew Aug 22 '13 at 2:30

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.