WordPress custom query on two tables to get hierarcal data

1 Views

Working with WordPress and using its functions to query the database on two custom tables. I am trying to output data as a “tree/grouped” format. Example: 

Team Name One
- Player Name One
- Player Name Two
- Player Name Three
- Etc...

Team Name Two
- Player Name one
- Player Name Two
- Player Name Three
- Etc...

I am not sure of what would be needed to get the results to display this way. Here is one query I have that joins the tables…

Read More
global $wpdb;
$user_id = '1';
$teamlist = $wpdb->get_results( $wpdb->prepare( 
"
SELECT
fv_teams.team_id AS team_teamid,
fv_players.player_id,
fv_teams.team_name AS teamname,
fv_players.player_fname AS fname,
fv_players.player_lname AS lname,
fv_players.player_team AS team,
fv_players.player_position AS position
FROM
fv_teams
INNER JOIN fv_players ON fv_teams.team_id = fv_players.team_id
WHERE
fv_teams.user_id = %d
",
$user_id
), OBJECT );

I can certainly get results doing the following… 

foreach ( $teamlist as $teamrow ) 
{
echo 'Team: ' .  $teamrow->teamname . '<br />';
echo 'Player ID: ' .  $teamrow->fname . '<br />';
}

But the team name will be duplicated in the results as 

Team Name One
- Player One
Team Name One
- Player Two
Team Name One
- Player Three
- Etc...

I am fairly certain I need to add a nested loop in there, but I am uncertain as to the coding required.

Any thoughts or direction would be very much helpful to a guy who just lost a good portion of his hair on this. ; )

Post Views: 1

Related posts

Leave a Reply

2 comments

  1. Not sure on the etiquette for answering your own question but I was able to get what I needed based on nesting the second query within a loop. 

    $teamlist = $wpdb->get_results( $wpdb->prepare( 
"SELECT *
 FROM fv_teams
 WHERE user_id = '1'
",
$user_id
), OBJECT );


foreach ( $teamlist as $teamrow ) 
{
$get_team_id = $teamrow->team_id;
echo  '<hr>';
echo '<h2>' . $teamrow->team_name . '</h2>';
$playerlist = $wpdb->get_results( $wpdb->prepare( 
    "SELECT *
     FROM fv_players 
     WHERE team_id = %d
    ",
    $get_team_id
), OBJECT );


    echo '<ul>';
    foreach ( $playerlist as $playerrow ) 
    {
        echo '<li>Player Name: ' .  $playerrow->player_fname . '</li>';
    }
    echo '</ul>';   
}

    It needs cleaning up but does work for me.