1

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,
            )
        )
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. Jan 27, 2016 at 14:37
  • @Djave: Please clear your requirement latitudes and longitudes?. Jan 28, 2016 at 5:38

2 Answers 2

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 */
    }
?>
3
  • Thanks for this – the issue is I'm getting 1,500 posts each with associated latitudes and longitudes, so I'm trying to make a custom query that will get all meta in one mysql query, not n+1 / 1,501 queries!
    – Djave
    Jan 27, 2016 at 13:41
  • Ah okay. Do you accually need ALL the metainformation or just the latitudes and longitudes?
    – Jeroen
    Jan 27, 2016 at 13:50
  • pretty much just the latitude and longitudes. I'm 90% sure I can just do an INNER JOIN but I can't get my head around it – thanks so much for taking the time to answer
    – Djave
    Jan 27, 2016 at 13:53
0

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);

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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