Take the 2-minute tour ×
WordPress Development Stack Exchange is a question and answer site for WordPress developers and administrators. It's 100% free, no registration required.

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

share|improve this question

2 Answers 2

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.

share|improve this answer

If I understand your question, this shoul works:

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 param is exactly the same to the meta_key: in you example meta_key = 'Name' but the query is WHERE meta_key='name', case is different!

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.