从foreach循环中的多个表中拉出

Question

Please forgive the beginner level code presented here - I'm just learning PHP and MySQL, so it should all be very base level scripting.

My DB has two tables, teams & members. teams table has primary key teamID, which is a foreign key in the members table. While in a foreach loop on a page that lists each member, I'm trying to access a column on the teams table by referencing the teamID column. I've played around with it, but I either get one of two things: 1) blank returns, where it seems nothing matching is found; 2) an error that I'm trying to change an array to a string.

I would appreciate any insight - not for a correct answer, but more so I can understand where I'm not connecting the dots. Thanks in advance.

<?php foreach ($members as $member) : ?>
        <tr>
            <td><?php echo $member['memberName']; ?></td>
            <td><?php echo $member['memberDOB']; ?></td>
            <td><?php echo get_member_team(); ?></td>
        </tr>

And on my member_db.php page:

function get_member_team() {
    global $db;
    $query = 'SELECT teamName
              FROM teams
              WHERE teams.teamID = members.teamID';             
    $statement = $db->prepare($query);
    $statement->execute();
    $member_team = $statement->fetch();
    $statement->closeCursor();
    return $member_team;
}

Answer 1

When you're looping through members, you need to pass the current iteration's team ID into your function and use that in your get_member_team query by binding it to the statement.

HTML Changes:

<td><?php echo get_member_team( $member['teamID'] ); ?></td>

PHP Changes:

function get_member_team( $team_id ) {
  global $db;
  $query = 'SELECT teamName
            FROM teams
            WHERE teams.teamID = :team';             
  $statement = $db->prepare($query);
  $statement->bindParam(':team', $team_id, PDO::PARAM_INT);
  $statement->execute();
  $member_team = $statement->fetch();
  $statement->closeCursor();
  return $member_team[0];
}

However, it would probably be a better idea to JOIN the two tables in your initial query so you don't have to make another database call for every item in the loop.