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 trying to create a query in order to get all the metadata from a custom post type named match_reports that is associated with a meta_key and meta_value in another custom post type named team_page. The common denominator is the id of team_page which is a meta_value in match_reports. Therefore each team has match reports associated with it whether they are the home or away team. Eventually the results will be looped through in a table so that each team is listed next to its own statistics.

I've tried using WP_Query to do this but without much luck: Looping through tabular data

and today I've been trying with $wpdb where I was having some trouble with the syntax:

Here is what I've come up with so far:

global $wpdb;
$querydetails = "
  SELECT wposts.* 
  FROM $wpdb->posts wposts, 
  INNER JOIN $wpdb->postmeta wpostmeta.* ON wposts.ID = wpostmeta.post_id
  WHERE wposts.post_status = 'publish'
  AND wposts.post_type = 'match_report'
  AND wpostmeta.meta_key = 'report_home-select'
  AND wpostmeta.meta_value = '198'
";

$teaminfo = $wpdb->get_results($querydetails, OBJECT);

I would love any help or suggestions on the best way to approach this. I know it can be done but its a bit beyond me. I'm also happy to read any tutorials or articles on dealing with $wpdb or complex queries using WP_Query.

share|improve this question
    
why a custom query? just use WP_Query with a meta query. –  Milo Jun 17 '13 at 14:08
    
I tried that yesterday but since the common denominator between the two tables is a post id inside a meta query, I was having a hard time formulating it link –  mantis Jun 17 '13 at 14:15

2 Answers 2

This will work fine.

 global $wpdb;
 $querydetails = "
SELECT
    wposts.*
FROM 
    $wpdb->posts AS wposts
INNER JOIN 
    $wpdb->postmeta AS wpostmeta ON wposts.ID = wpostmeta.post_id
WHERE
    wposts.post_status = 'publish' 
AND wposts.post_type = 'match_report'
AND wpostmeta.meta_key = 'report_home-select'
AND wpostmeta.meta_value = '198' 
";

$teaminfo = $wpdb->get_results($querydetails, OBJECT);

Try it.

share|improve this answer
    
Thanks, it does work in that I don't get an error but I was actually trying to get all the post meta associated with the value 198 (it represents a team so I need all the information from all the matches in which that team played.) I was hoping to do that by joining the table but perhaps that's not the way to go about it? –  mantis Jun 17 '13 at 11:41
1  
Why not just use get_post_custom()? –  Pat J Jun 17 '13 at 12:56
    
I am creating a table that uses information from two seperate custom post types that are intertwined based on the team id (198). I basically need to format a query, do some calculations and then loops through the data to create a table. I am trying $wpdb because it makes more sense to me (or I thought it would). I'm basically after all the meta information in the match report post type but it has to be cross referenced by the teams playing in the matches. –  mantis Jun 17 '13 at 13:59
1  
^^ That comment suggests to me that the real question/problem is more complicated than described in the actual question. Please edit your question with all the relevant details, including information about how your various pieces of information relate to one another. –  s_ha_dum Jun 17 '13 at 14:12
    
Alright, I had originally thought it was just a syntax problem but it seems the issue goes a lot deeper. I'll work on a better description. –  mantis Jun 17 '13 at 14:17

I did end up finding the answer using WP_Query. In case anyone is curious: Looping through tabular data

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.