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

Question

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

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,

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