3

Hi – I'm hoping this is the right place to be asking this question.

I am in the process of moving my full football club website onto Wordpress, therefore using a single database for the information I hold. I'm doing this (for context) to make things easier in the long-run.

Before, there was a database table for Opponents, Matches, Players etc. Now I have Custom Post Types for these. Each entry in these tables has a unique ID that could be referenced in other tables where appropriate: Opponent ID, Match ID, Player ID and so on.

In a Match page, for example, I'd extract and output data from the Opponent and Player tables with joins - a basic example of which would be:

$query = "SELECT me.date, me.opponent, me.score, ce.name, ce.ID, me.ID, ce.badge FROM  
matchengine me RIGHT JOIN clubengine ce ON me.opponent=ce.ID WHERE me.ID = ".$id."";

From this, I get

me.date      ce.name (from ID)    me.score
==========================================
01/01/1900   Team Name United     2-1

instead of

me.date      ce.id                me.score
==========================================
01/01/1900   200                  2-1

What I haven't yet been able to do is to replicate this in Wordpress. I can output info from a single post type i.e. Match, but not yet found the way to extract the relevant data from another post type too (if there is one). My instinct is joining two post types on a shared meta_value – or meta_key?

EDIT (11 August): What I've done is taken @SallyCJ's advice and made the Opponent field a Post Object (finally finding out how to import data from CSV as this!). Using the following code within the query calling the 'Match' posts, I am able to output the team name and not just three digit ID code. I'm sure it's rough and ready in Wordpress circles, but it does the job for me.

$opposition = get_field('club');
if($opposition) echo $opposition->post_title;
  • So I suppose you have CPT match and club? And assuming $wpdb->posts is wp_posts; for match posts, me.date = wp_posts.post_date, me.opponent = the meta (i.e. custom field) named competition, me.score = the meta named score, and me.ID = wp_posts.ID; and for club posts, ce.name = wp_posts.title, ce.ID = wp_posts.ID, and ce.badge = the meta named badge? – Sally CJ Jul 29 '18 at 4:49
  • That is, I think, more or less correct @SallyCJ – except for the final two; ce.ID corresponds to the post slug (is that wp_posts.name?) and ce.badge will be the featured image. I have no qualms adding an ID field to the club CPT if it makes things easier. Not sure why I didn't in the first place, to be honest... – Pete Hayman Jul 29 '18 at 16:12
  • It's wp_posts.post_name and not wp_posts.name. :) So if the meta competition for match posts have its value set to the corresponding club post's ID, slug, or title, then we can relate the match and club posts via the competition meta/field. Otherwise, you'd need to add a meta (to match posts) which would relate the two CPTs - maybe best if the meta value = the club post ID. – Sally CJ Jul 30 '18 at 8:23
  • Sorry @SallyCJ, I had made a mistake in the code - it's the meta opposition that corresponds to the club post's slug e.g. opposition 248 corresponds to /club/248 if that makes sense. So, my aim (or hope?) is to be able to make that relation in the query. – Pete Hayman Jul 30 '18 at 20:17
  • You actually made another typo in the question.. WHERE p.post_type = 'match'" - remove the ". :) Anyway, I've posted an answer and hopefully it helps you! Just let me know if you need further assistance. =) – Sally CJ Jul 31 '18 at 3:55
0

So based on our discussion, the meta named opposition (for match posts) has its value set to the slug of the corresponding club post; e.g. club-1, club-two, etc., right?

If so, then you first get the ID of the club post using a custom query (see in the code below) and then you can access any data you want from the club post such as the post title and content. Just make sure to always check if the post ID (identified by $club_id in the code below) is greater than 0 (zero), because otherwise the data you get would be for the current post in the foreach loop, which is for the current match post and not the club post associated with that match post. (I hope you can understand this..)

if ( $latestresult )
{
    foreach ( $latestresult as $post )
    {
        setup_postdata( $post );

        // Get the ID of the `club` post associated with the current `match` post.
        $club_id = $wpdb->get_var( $wpdb->prepare( "
            SELECT ID
            FROM $wpdb->posts
            WHERE post_name = %s
            AND post_type = 'club'
        ", get_field( 'opposition', $post->ID ) ) );

        // Don't use `get_the_title()` with a zero value; so check if $club_id > 0
        $player_name = $club_id > 0 ? get_the_title( $club_id ) : 'N/A';
    ?>
    ...
    <div class="result-info">       
    <a href="<?php the_permalink(); ?>">
    ...
    <span class="result-column"><?php echo $player_name; ?></span>
    ... 
    <span class="thumbnail">
        <?php echo get_the_post_thumbnail( $club_id, 'thumbnail' ); ?>
    </span>
    </a>
    </div>
    <?php
    }   
}

However, if possible, you should probably better change the ACF field type to Post Object, where the meta value would be the ID of the club post and not its slug. But for that, make sure to set the "Allow Null" to "Yes", and "Select multiple values" to "No" — see this image.

And if you can do that, then the code would be simpler:

if ( $latestresult )
{
    foreach ( $latestresult as $post )
    {
        setup_postdata( $post );

        // Get the ID of the `club` post associated with the current `match` post.
        // Set the third argument to `false` to get the post ID instead of object.
        $club_id = get_field( 'opposition', $post->ID, false );

        // Don't use `get_the_title()` with a zero value; so check if $club_id > 0
        $player_name = $club_id > 0 ? get_the_title( $club_id ) : 'N/A';
    ?>
    ...
    <div class="result-info">       
    <a href="<?php the_permalink(); ?>">
    ...
    <span class="result-column"><?php echo $player_name; ?></span>
    ... 
    <span class="thumbnail">
        <?php echo get_the_post_thumbnail( $club_id, 'thumbnail' ); ?>
    </span>
    </a>
    </div>
    <?php
    }   
}

Additional Note

Although this code works:

$latestresult = $wpdb->get_results( 
"SELECT * FROM $wpdb->wp_posts
WHERE p.post_type = 'match'
ORDER BY post_date DESC
LIMIT 2"
);

I did it using get_posts():

$latestresult = get_posts( [
    'post_type'      => 'match',
    'orderby'        => 'date',
    'order'          => 'DESC',
    'posts_per_page' => 2, // LIMIT
] );

Because that's how we're supposed to do it in WordPress. =) (The other way would be using new WP_Query( [ ... ] ).)

  • I'd have used Post Object, but found it easier (lazier?) to import multiple entries into text fields @SallyCJ. Using the non-SQL solution is currently returning N/A for all results; perhaps this is because I didn't clarify properly? The post slug is a single number only i.e /club/100/ rather than club-100. But I'm unable to figure out if it's the $club_id query. – Pete Hayman Aug 1 '18 at 11:34
  • Sorry, I didn't really notice the "single number only" part.. so if the actual post slug are all numbers, try change the get_field( 'opposition', $post->ID ) to str_replace( [ '/club/', '/' ], '', get_field( 'opposition', $post->ID ) ) in the first code of the Original Answer part of my answer. – Sally CJ Aug 1 '18 at 12:33
  • 1
    It wouldn't surprise me if I did something wrong, to be honest. I've now taken one of your earlier suggestions and converted the opposition field to a Post Object – this is now doing what you said it would, thank you. You've been a massive help on this (and massively patient too!) – Pete Hayman Aug 11 '18 at 8:52
  • 1
    Do, absolutely @SallyCJ. Really do appreciate your time and patience with this, but you were right from the very off - post object is the way to go as it's much more convenient. Or so it seems. I'm sure the way I've structured the code is inefficient, but everything is good :) – Pete Hayman Aug 12 '18 at 17:22
  • 1
    I'm happy to upvote your rolled back answer - after all, you were right at the outset and it is much easier to convert the necessary meta fields to Post Object :) – Pete Hayman Aug 14 '18 at 8:05

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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