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;
1 Answer
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="https://wordpress.stackexchange.com/questions/309811/<?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="https://wordpress.stackexchange.com/questions/309811/<?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( [ ... ] )
.)