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

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.
}

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.