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).
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,
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):
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.
I suggest
LEFT JOIN
here because if you use ordinary innerJOIN
, any posts with missing metadata values will be suppressed from the result set. TheLEFT 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:
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.