Custom WordPress SQL query to output metadata based on post ID – php, database (custom post type&fields)

I am running a database query from another WordPress database, and therefore using standard sql queries instead of various wordpress functions.

Essentially, I want to show all the meta values/data associated with a postID. I have setup a customer post type ‘event’ with its own custom fields (start date etc.).

Read More

I have almost everything I need, I just need to write the proper php loop to output the data for each post ID (to display the post’s metadata).

Table Structure Image

note: meta_values can be null and more meta_keys may be added in the future.

$newdb = //already setup new db connection (don't worry about this)
$query =
                "
                    SELECT * 
                    FROM wp_posts, wp_postmeta
                    WHERE wp_posts.ID = wp_postmeta.post_ID
                    AND wp_postmeta.meta_key NOT LIKE '_%'   
                    AND wp_posts.post_type='event' 
                    AND wp_posts.post_status = 'publish';
                " ;  


$events = $newdb->get_results($query, OBJECT);
//get_results() is a wordpress function, nearly equiv to my_sqli
//OBJECT - result will be output as an object.
//ARRAY_A - result will be output as an associative array.


foreach ( $events as $event ) {
echo $event->post_title;
echo $event->meta_value;    
}

Current Result:

Title_1, 11/02/2016

Title_1, 05:00

Title_1, 12/01/2016

Title_2, 05/02/2016

…


**Desired Result: ** (for each post)

(structurally):

post_ID: 1, post_title: Title_1, start_date: 11/02/2016, start_time: 05:00, end_date: 12/01/2016

$event->post_title;
$event->meta_value”;  //meta_key = start_date
$event->meta_value”;  //meta_key = end_time
$event->meta_value”; //meta_key = end_date

(visually)

Title_1,
11/02/2016,
05:00,
12/1/2016,

Title_2,
05/02/2016,
07:00,
07/02/2016,

Related posts

2 comments

  1. I ended up making my own pivoting php loop that checks each resulting array (from the SQL query) and groups it into an another associative array based on the posts’ ID. Everything works extremely well and I can place each of the posts’ metavalues in the appropriate HTML tags to be displayed.

    Here is my full working code to select my custom post type (‘event’) and custom fields (stored in the meta key-value pairs):

    //credentials can be found in your wp-config.php file
    $newdb = new wpdb('DB_USER', 'DB_PASSWORD', 'DB_NAME', 'DB_HOST');
    
    //sql query
    $querystr =
            "
            SELECT wp_posts.ID, wp_posts.post_title, wp_postmeta.meta_key, wp_postmeta.meta_value
            FROM wp_posts, wp_postmeta
            WHERE wp_posts.ID = wp_postmeta.post_ID
            AND wp_postmeta.meta_key NOT LIKE '_%'
            AND wp_posts.post_type='event'
            AND wp_posts.post_status = 'publish';
            " ;
    
    //wordpress function that returns the query as an array of associative arrays
    $results = $newdb->get_results($querystr, ARRAY_A);
    
    //create an empty array
    $events = array();
    foreach ($results as $row) {
        //if row's ID doesn't exist in the array, add a new array
        if (!isset($events[$row['ID']])) {
            $events[$row['ID']] = array();
        }
        //add all the values to the array with matching IDs
        $events[$row['ID']] = array_merge($events[$row['ID']], array('post_title'=>$row['post_title']), array($row['meta_key']=>$row['meta_value']));
    }
    
    //extract data from each event
    foreach ($events as $event) {
        var_dump($event); //do something
        echo $event['start_date']; //can also echo specific metavalues etc.
    }
    
  2. What you’re trying to do is a notorious pain in the neck in SQL, because wp_postmeta is a key-value store. The all metadata is the painful part.

    It’s hard to convert the key-value store items into their own columns in your result set in a way that allows for new columns to appear when new key values are added.

    Pro tip: SELECT * is not your friend when trying to do this kind of thing.

    It’s easier if you know the metadata items ahead of time. If you know you need columns for startdate, endtime, and enddate, for example, you can do this.

     SELECT wp_posts.*, 
            a.meta_value start_date, 
            b.meta_value end_time,
            c.meta_value end_date
       FROM wp_posts
       LEFT JOIN wp_postmeta a ON wp_posts.ID = a.post_ID AND a.meta_key='start_date'
       LEFT JOIN wp_postmeta b ON wp_posts.ID = b.post_ID AND a.meta_key='end_time'
       LEFT JOIN wp_postmeta c ON wp_posts.ID = c.post_ID AND a.meta_key='end_date'
      WHERE wp_posts.post_type = 'event' etc etc
    

    I suggest LEFT JOIN here because if you use ordinary inner JOIN, any posts with missing metadata values will be suppressed from the result set. The LEFT JOIN allows them to appear in the result set with NULL values.

    If you must do the all metadata thing, you’ll need to do a lot of the formatting in your php code. You’d start by figuring out the distinct metadata items:

     SELECT DISTINCT wp_metadata.meta_key
       FROM wp_posts
       JOIN wp_postmeta ON wp_posts.ID = wp_postmeta.post_ID
      WHERE wp_posts.post_type = 'event'  etc etc
    

    You’ll then build a data structure in php suitable for storing columns named for each meta_key value. (Explaining that is beyond the scope of a SO answer).

    Next you’ll run a query like the one in your question and use it to populate your data table in php. Finally, you’ll display the table.

    The jargon for this general type of operation is pivoting your table. It’s unreasonably hard to do in MySQL in the general case.

Comments are closed.