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??)
2 Answers
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' );