Retrieving custom fields with $wpdb->get_results

1 Views

I’m trying to use $wpdb->get_results to retrieve an array of all posts including custom fields. I can retrieve the meta_key and meta_value columns like this: 

[meta_key] => state [meta_value] => california

but I’m trying to list them in the object like this:

Read More
[meta_key] => meta_value [state] => california [city] => san francisco

The Query: 

global $wpdb;
$query = "
SELECT ID, post_date, post_title, post_content, guid, meta_key, meta_value
FROM wp_posts INNER JOIN wp_postmeta
ON (wp_posts.ID = wp_postmeta.post_id)
";

$results = $wpdb->get_results($query);

foreach($results as $result) {
    print_r($result);
}

Is it possible to use an alias and/or subquery to achieve this? 

SELECT ID, post_date, post_title, post_content, guid, meta_value AS (subquery here??)
Post Views: 1

Related posts

Leave a Reply

2 comments

  1. Note, before going further: Take care about portability and security:

    function wpse50056_get_post_meta()
{
    global $wpdb;

    $results = $wpdb->get_results( "
        SELECT 
            ID, 
            post_date, 
            post_title, 
            post_content, 
            meta_key, 
            meta_value
        FROM {$wpdb->posts} 
            INNER JOIN {$wpdb->postmeta} 
        ON ( $wpdb->posts.ID = {$wpdb->postmeta}.post_id )
    " );

    foreach( $results as $result )
    {
        printf( '<pre>%s</pre>', htmlspecialchars( var_export( $result, true ) ) );
    }
}
add_action( 'shutdown', 'wpse50056_get_post_meta' );

  2. Just for clarity, to not get the extra quotes that benhass was talking about (i still get them with the code provided by kaiser and WP 3.7.1), don’t use the prepare method at all (ok in this case, as we only use parameters provided by the $wpdb object itself):

    function wpse50056_get_post_meta() {
    global $wpdb;

    $query = 
    "   SELECT 
            ID,
            post_date,
            post_title,
            post_content,
            meta_key,
            meta_value
        FROM $wpdb->posts
            INNER JOIN $wpdb->postmeta
        ON (  $wpdb->posts.ID = $wpdb->postmeta.post_id)
    ";

    $results = $wpdb->get_results( $query );

    foreach( $results as $result ) {
        echo '<pre>'; print_r( $result ); echo '</pre>';
    }
}
add_action( 'shutdown', 'wpse50056_get_post_meta' );

    But of course, REMEMBER, as soon as you use other parameters in the query, the use of $wpdb->prepare is recommended to prevent SQL injection.