Take the 2-minute tour ×
WordPress Development Stack Exchange is a question and answer site for WordPress developers and administrators. It's 100% free, no registration required.

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:

[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??)
share|improve this question

2 Answers 2

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' );
share|improve this answer
    
Thanks @kaiser, I appreciate the suggestions here, however I'm getting an SQL Error from the extra quotes that are brought in with wp_posts and wp_postmeta: SELECT ID, post_date, post_title, post_content, meta_key, meta_value FROM 'wp_posts' INNER JOIN 'wp_postmeta' ON ( 'wp_posts'.ID = 'wp_postmeta'.post_id) –  benhass Apr 25 '12 at 4:25
    
You´re right. fixed & updated. –  kaiser Apr 25 '12 at 12:54

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.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.