pulling data out of many to many relationship

Question

I've been grappling with this one for a couple of days now, but I've reached the limits of my admittedly limited mysql knowledge. I have two tables, groups and parties, using a lookup table groupparty with two columns groupid and partyid. Given a particular party, I simply want to pull out the groups associated with that party. Here is the bare bones of the script I've been struggling with:

<?php



include $_SERVER['DOCUMENT_ROOT'] . '/includes/connect.inc.php';

 $partyid = '71';
 $sql = "SELECT groupid FROM groupparty WHERE groupparty.partyid='$partyid'";
 $result = mysqli_query($link, $sql);
 while ($row = mysqli_fetch_array($result))
 {
  $groupids[] = array('groupid' => $row['groupid']);
 }

 $sql = "SELECT groupname FROM groups WHERE id='$groupids'";
 $result = mysqli_query($link, $sql);
 while ($row = mysqli_fetch_array($result))
 {
  $groups[] = array('groupname' => $row['groupname']);
 }

 include 'show.html.php';
 exit();

?>

And the html call in show.html.php:

Group(s):
 <?php foreach ($groups as $group): ?>
 <?php htmlout($group['groupname']); ?>
 <?php endforeach; ?>

Which of course gives me the following error:

 Group(s): 
 Warning: Invalid argument supplied for foreach() in show.html.php on line 2

I know the issue is the attempt to SELECT groupname FROM groups WHERE id='$groupids'. But how does one handle this common situation?

Thanks.

Answer 1

Use a join to do the whole thing in just one query:

SELECT groups.groupname
FROM groupparty
JOIN groups
ON groups.id = groupparty.groupid
WHERE groupparty.partyid='$partyid'

Or if you want to continue with your way of doing it use the SQL expression IN (..., ..., ...) to match any one of a number of IDs. I wouldn't recommend this though.