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.
If I understand your question correctly, this should work:
Use this like so:
Please be sure the second parameter is exactly the same as the
meta_key
: in your examplemeta_key = 'Name'
but the SQL query isWHERE meta_key='name'
, case is different!You can select your posts by using the meta_query parameter in the WP_Query class. Get all the post ID’s of the posts who have a shared event_id of 19.
You can use the ID’s later on to get all necessary data.
I stumbled in the same problem and get more or less what I wanted in this way: