Pulling from multiple tables in a foreach loop

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;
}

FYI, no need to prepare a query without placeholders. Assuming this is PDO, just $statement = $db->query($query); works fine. — miken32
– miken32, Commented Feb 22, 2016 at 23:49

jeffjenx · Accepted Answer · 2016-02-23 00:57:53Z

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.

edited Feb 23, 2016 at 0:57

answered Feb 22, 2016 at 23:44

jeffjenx

17.6k6 gold badges63 silver badges104 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Shaw Over a year ago

Thanks for the quick reply, and it makes me feel a little better than I was at least headed in the right direction - as I had tried almost this exact code. When the page loads, I'm getting an error: code Notice: Array to string conversion in list_members.php on line 32 Array code

jeffjenx Over a year ago

I don't know what line 32 is, but I'm guessing it should be return $member_team[0]; in the get_member_team function. The query probably returns an array of arrays even though we are only selecting a single column of a single record.

Shaw Over a year ago

That did it! Thanks for breaking it down for me, really appreciate it!

Collectives™ on Stack Overflow

Pulling from multiple tables in a foreach loop

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related