Query and get meta as object(stdClass) on wp postmeta table?

Question

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)(
                      [0]=>object(stdClass)#1(
                                             [post_id] => 123,
                                             [key1]    => x,
                                             [key2]    => y,
                                             [key3]    => z
                                             )
                      [1]=>=>object(stdClass)#2(
                                             [post_id] => 456,
                                             [key1]    => x,
                                             [key2]    => y,
                                             [key3]    => z
                                             )
                      )

How to do it?

Answer 1

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

print_r($object_array);

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.

i.e.

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

Hope this is useful.