I want to get a groups of data from postmeta table. The postmeta table have 4 colums, meta_id, post_id, meta_key,meta_value. Each post_id has a list of meta_keys. I want to run a query and get a result of

arry(number of results)(
                                             [post_id] => 123,
                                             [key1]    => x,
                                             [key2]    => y,
                                             [key3]    => z
                                             [post_id] => 456,
                                             [key1]    => x,
                                             [key2]    => y,
                                             [key3]    => z

How to do it?

  • Do you want to do this for a limited number of posts or for (almost) all posts? (The difference is between using WP meta API functions and a custom query). And what if a post has multiple meta values for the same key? Overwrite with the last one? (Use @Jan when you reply in a comment and I get a notification) – Jan Fabry May 30 '11 at 13:48
  • @Jan, I want to get ids of all posts, plus the meta_key and meta_values that belong to each post_id. The object is a graph created by a plugin, it takes info from custom field inputs,calculate in a class. Now I need to loop through all the graph so that I can display a graph on each post-- that's what I learned-- loop and display. Unless I learned wrongly, I do need to loop all the graph seperately from the post loop. I borrowed a loop code to do the job, now need to input the object into the loop code. That's why need the formate as above. Any suggestion? Thanks! – Jenny May 30 '11 at 14:44
  • Every meta_key and value is unique. The validation is done when save the inputs into the custom field. – Jenny May 30 '11 at 15:14
  • @Jan Fabry , if you think there's other better way to display the graphs, please suggest. – Jenny May 30 '11 at 15:48

I was wondering the same myself recently and this is the best solution I've come up with.

First, declare a really simple class in your functions.php

class Object
    var $data = array();

    public function __set($name, $value)
        $this->data[$name] = $value;

    public function __get($name)
        return $this->data[$name];

This is the generic object we will use to store everything

Next, we will connect to the SQL database and pull all the data we need. In this case I'm doing a very generic query to select ALL the posts in the database and attach their relevant data. If you've got a large DB, you probably need to narrow this query down a little with some conditions on the post such as adding:

"WHERE post.post_type = 'graph'"

to the SQL query, but I'm not sure exactly what you narrow down the results by, so we'll stay generic for demo purposes...

global $wpdb;
    $query = "SELECT post.id as post_id, meta.meta_key, meta.meta_value FROM wp_posts as post LEFT JOIN wp_postmeta as meta ON post.id = meta.post_id";
    $result = $wpdb->get_results($query);
    $last_post_id = -1;
    $object_array = array();
    foreach($result as $post)
        if ($last_post_id != $post->post_id)
            $instance = new Object;
            $last_post_id = $post->post_id;
            $instance->post_id = $post->post_id;

            $instance->{$post->meta_key} = $post->meta_value;
            $object_array[] = $instance;
        else {
            $meta_key = $post->meta_key;
            $instance->$meta_key = $post->meta_value;

What this is doing is it is taking the relatively "dirty" query from sql and organizing it into the format you mentioned above. lastly try printing the array out for yourself


You'll notice that your variables are embedded in another array "data" within the object but because of the getter and setter functions we wrote in the class definition, you still get to call the variable names directly.


$meta_key = "my_key";
echo $object_array[0]->$meta_key;

Hope this is useful.

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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