I am using someone else’s WP plugin which stores lots of related data in wp_postmeta
using an “artificial key”. So effectively we hijack wp_postmeta
to create a nested relational database. So one row will have
meta_id = 999
post_id = 130
meta_key = 'event_id'
meta_value="19"
Then 4 other actual wp_postmeta rows that share that post_id contain the rest of the information for that virtual “row” of data. For example
meta_id = 989
post_id = 130
meta_key = 'Name'
meta_value="Fred"
And we will have lots of “virtual rows” (hundreds) that all share that event_id
of 19 (and of course lots of others with different event_id
s of their own.
I have tried many SQL queries, but I am stumped. How can I get a table that has grouped info for a common event_id? It doesn’t have to have all the data, just one piece, such as the ‘Name’ field. I tried a subquery like this:
SELECT meta_value from wp_postmeta
WHERE meta_key='name' AND
post_id IN
(SELECT post_id FROM wp_postmeta
WHERE meta_key='event_id' AND meta_value="19");
That probably looks laughably stupid – it also doesn’t work. I also tried JOIN. This seems like something that would come up a lot in WP coding, but I have searched and come up pretty much empty.
3 Answers
If I understand your question correctly, this should work:
function get_event_info($event_id = 0, $info = '' ) {
global $wpdb;
return $wpdb->get_col(
$wpdb->prepare(
"SELECT meta_value from $wpdb->postmeta WHERE
meta_key = '%s' AND post_id IN
(SELECT DISTINCT post_id FROM $wpdb->postmeta WHERE meta_key = 'event_id' AND meta_value= %s )",
$info, $event_id
)
);
}
Use this like so:
$names = get_event_info( 19 , 'Name' );
if ( ! empty($names) ) {
echo '<ul>';
foreach ($names as $name) {
echo '<li>' . $name . '</li>';
}
echo '</ul>';
}
Please be sure the second parameter is exactly the same as the meta_key
: in your example meta_key = 'Name'
but the SQL query is WHERE meta_key='name'
, case is different!