mysql - $wpdb select all meta for each post

I'm running the following query trying to get the data and associated meta data of a huge number of posts:

SELECT wp_posts.post_title, wp_posts.post_date,
    wp_posts.ID, wp_postmeta.post_id, wp_postmeta.meta_key
    FROM wp_postmeta
    INNER JOIN wp_posts
    ON wp_postmeta.post_id=wp_posts.id
    WHERE wp_posts.post_status = 'publish'
    LIMIT 5

And that returns the following:

Array
(
    [0] => stdClass Object
        (
            [post_title] => Hello world!
            [post_date] => 2015-11-11 08:44:54
            [ID] => 1
            [post_id] => 1
            [meta_key] => _edit_lock
        )

    [1] => stdClass Object
        (
            [post_title] => Home
            [post_date] => 2015-11-11 08:44:54
            [ID] => 2
            [post_id] => 2
            [meta_key] => _wp_page_template
        )

    [2] => stdClass Object
        (
            [post_title] => Home
            [post_date] => 2015-11-11 08:44:54
            [ID] => 2
            [post_id] => 2
            [meta_key] => _edit_last
        )
...

But now I realise I need a much more complex query, as what I'd like is something like the following:

Array
(
    [0] => stdClass Object
        (
            [post_title] => Hello world!
            [post_date] => 2015-11-11 08:44:54
            [ID] => 1
            [post_id] => 1
            [meta] => array(
                '_wp_page_template' => 'home.php',
                '_edit_last' => 2015-11-11 08:44:54,
            )
        )

    [1] => stdClass Object
        (
            [post_title] => Home
            [post_date] => 2015-11-11 08:44:54
            [ID] => 2
            [post_id] => 2
            [meta] => array(
                '_wp_page_template' => 'home.php',
                '_edit_last' => 2015-11-11 08:44:54,
            )
        )

    [2] => stdClass Object
        (
            [post_title] => About us
            [post_date] => 2015-11-11 08:44:54
            [ID] => 3
            [post_id] => 3
            [meta] => array(
                '_wp_page_template' => 'home.php',
                '_edit_last' => 2015-11-11 08:44:54,
            )
        )

I'm running the following query trying to get the data and associated meta data of a huge number of posts:

SELECT wp_posts.post_title, wp_posts.post_date,
    wp_posts.ID, wp_postmeta.post_id, wp_postmeta.meta_key
    FROM wp_postmeta
    INNER JOIN wp_posts
    ON wp_postmeta.post_id=wp_posts.id
    WHERE wp_posts.post_status = 'publish'
    LIMIT 5

And that returns the following:

Array
(
    [0] => stdClass Object
        (
            [post_title] => Hello world!
            [post_date] => 2015-11-11 08:44:54
            [ID] => 1
            [post_id] => 1
            [meta_key] => _edit_lock
        )

    [1] => stdClass Object
        (
            [post_title] => Home
            [post_date] => 2015-11-11 08:44:54
            [ID] => 2
            [post_id] => 2
            [meta_key] => _wp_page_template
        )

    [2] => stdClass Object
        (
            [post_title] => Home
            [post_date] => 2015-11-11 08:44:54
            [ID] => 2
            [post_id] => 2
            [meta_key] => _edit_last
        )
...

But now I realise I need a much more complex query, as what I'd like is something like the following:

Array
(
    [0] => stdClass Object
        (
            [post_title] => Hello world!
            [post_date] => 2015-11-11 08:44:54
            [ID] => 1
            [post_id] => 1
            [meta] => array(
                '_wp_page_template' => 'home.php',
                '_edit_last' => 2015-11-11 08:44:54,
            )
        )

    [1] => stdClass Object
        (
            [post_title] => Home
            [post_date] => 2015-11-11 08:44:54
            [ID] => 2
            [post_id] => 2
            [meta] => array(
                '_wp_page_template' => 'home.php',
                '_edit_last' => 2015-11-11 08:44:54,
            )
        )

    [2] => stdClass Object
        (
            [post_title] => About us
            [post_date] => 2015-11-11 08:44:54
            [ID] => 3
            [post_id] => 3
            [meta] => array(
                '_wp_page_template' => 'home.php',
                '_edit_last' => 2015-11-11 08:44:54,
            )
        )

• mysql
• wpdb

Share Improve this question edited Jan 27, 2016 at 13:43 Djave asked Jan 27, 2016 at 13:10 DjaveDjave 25844 silver badges2525 bronze badges 2

• Don't use direct SQL queries unless you don't have an API option (or an extremely good reason), which you do have for this. – Mark Kaplun Commented Jan 27, 2016 at 14:37
• @Djave: Please clear your requirement latitudes and longitudes?. – AddWeb Solution Pvt Ltd Commented Jan 28, 2016 at 5:38

Add a comment  | 

2 Answers 2

Sorted by: Reset to default 0

This is really simple.

get_post_meta($postid);

This will return all values in about the same syntax.

In a more workable loop:

<?php
    $custom = new WP_Query(array("posts_per_page" => -1))
    if( $custom->have_posts() ) {
        while ( $custom->have_posts() ) {
            the_post();
            $data = get_post_meta(get_the_ID());
        }
    } else {
        /* No posts found */
    }
?>

For anyone stumbling across this.. I resolved this by doing the following:

(adapted for your code)

SELECT wp_posts.post_title, wp_posts.post_date,
wp_posts.ID, wp_postmeta.post_id, wp_postmeta.meta_key, 
GROUP_CONCAT(wp_postmeta.meta_key SEPARATOR ':') AS meta_keys

FROM wp_postmeta
INNER JOIN wp_posts
ON wp_postmeta.post_id=wp_posts.id
WHERE wp_posts.post_status = 'publish'
GROUP BY wp_postmeta.post_id
LIMIT 5

By doing GROUP_CONCAT(wp_postmeta.meta_key SEPARATOR ':') AS meta_keys and GROUP BY wp_postmeta.post_id it creates a results meta_keys where you can then in PHP explode on :

$meta_keys = explode(':', $item->meta_keys);