little help with a mySQL query to wp database

2 Views

@t31os gave a very helpful response to:

getting all values for a custom field key (cross-post) by providing this function

Read More
if ( ! function_exists( 'get_meta_values' ) ) {
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;
}
}

(slightly adapted)

I need to adapt this function to retrieve not one but two columns; meta_value and post_id, both from the postmeta table and store these in an array.

I’m not knowledgeable at all with mySQL.

Any help is appreciated.

Post Views: 2

Related posts

1 comment

  1. Following the recomendations of @toscho i had a look at get_results and the following edits to the function ended up doing the trick.

    if ( ! function_exists( 'get_meta_values' ) ) {
function get_meta_values( $key = '', $type = 'post', $status = 'publish' ) {
    global $wpdb;
    if( empty( $key ) )
        return;
    $r = $wpdb->get_results($wpdb->prepare( "
        SELECT      pm.meta_value, pm.post_id 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 ), ARRAY_A );
    return $r;
}
}

    It only worked when i added the second parameter ARRAY_A (ARRAY_N or OBJECT(default) would have been possible too) to get_results() as per http://codex.wordpress.org/Class_Reference/wpdb

    Thanks to everyone for their help.

Comments are closed.