Need some help with SQL

[Budove]

I'm trying to gather associative info from two tables that are held in a third table. Here is my table layout...

table:contact ----- rows: contact_id, firstname

table:groups ------rows: group_id, group_name

table:groupmember ------ rows: contact_id, group_id


The "groupmember" table contains associative id's relative to group membership.


Here is the code I have so far.

<td><b>Groups: </b><form name="Group Select" method="Post" action="prcs.viewgroups.php"><select name="viewgroup">
	<option value="select" selected>Group Membership</option>';
	
$result = mysql_query("SELECT group_id FROM groupmember INNER JOIN groups ON groupmember.group_id groups.group_id WHERE groupmember.contact_id = '$contactid'");

while($row = mysql_fetch_array($result))
  {
  echo "<option value=$row[group_id]";
  echo ">$row[group_name]</a></option>";
  }

echo '</select>
	        &nbsp;<input type="submit" name="Submit" value="Submit" />
	</td>

Any idea what I'm doing wrong?

[Rksk]

<td><b>Groups: </b><form name="Group Select" method="Post" action="prcs.viewgroups.php"><select name="viewgroup">
	<option value="select" selected>Group Membership</option>';
	
$result = mysql_query("SELECT group_id FROM groupmember INNER JOIN groups ON groupmember.group_id groups.group_id WHERE groupmember.contact_id = '$contactid'");

while($row = mysql_fetch_array($result))
  {
  echo "<option value=$row[group_id]";
  echo ">$row[group_name]</a></option>";
  }

echo '</select>
	        &nbsp;<input type="submit" name="Submit" value="Submit" />
	</td>

Try this,

<td><b>Groups: </b><form name="Group Select" method="Post" action="prcs.viewgroups.php"><select name="viewgroup">
	<option value="select" selected>Group Membership</option>';
	
$result = mysql_query("SELECT groups.group_id, groups.group_name FROM groups INNER JOIN groupmember ON groups.group_id = groupmember.group_id WHERE groupmember.contact_id = '$contactid'");

while($row = mysql_fetch_array($result))
  {
  echo "<option value=$row[0]";
  echo ">$row[1]</a></option>";
  }

echo '</select>
	        &nbsp;<input type="submit" name="Submit" value="Submit" />
	</td>

[Budove]

Thank you for clearing that up. I'm having trouble wrapping my head around INNER JOIN. After studying your code I thought I had it figured out, so I tried to write a query that would return the contact_id and first and last name.. but obviously I haven't quite grasped this concept yet. Here is what I tried to do...

<td><b>Groups: </b><form name="Group Select" method="Post" action="prcs.viewgroups.php"><select name="viewgroup">
	<option value="select" selected>Group Membership</option>';
	
$result = mysql_query("SELECT contact.contact_id, contact.contact_name FROM contact INNER JOIN groupmember ON contact.contact_id = groupmember.contact_id WHERE groupmember.group_id = '$groupid'");

while($row = mysql_fetch_array($result))
  {
  echo "<option value=$row[contact_id]";
  echo ">$row[first] $row[last]</a></option>";
  }

echo '</select>
	        &nbsp;<input type="submit" name="Submit" value="Submit" />
	</td>

[Rksk]

Thank you for clearing that up. I'm having trouble wrapping my head around INNER JOIN. After studying your code I thought I had it figured out, so I tried to write a query that would return the contact_id and first and last name.. but obviously I haven't quite grasped this concept yet. Here is what I tried to do...

<td><b>Groups: </b><form name="Group Select" method="Post" action="prcs.viewgroups.php"><select name="viewgroup">
	<option value="select" selected>Group Membership</option>';
	
$result = mysql_query("SELECT contact.contact_id, contact.contact_name FROM contact INNER JOIN groupmember ON contact.contact_id = groupmember.contact_id WHERE groupmember.group_id = '$groupid'");

while($row = mysql_fetch_array($result))
  {
  echo "<option value=$row[contact_id]";
  echo ">$row[first] $row[last]</a></option>";
  }

echo '</select>
	        &nbsp;<input type="submit" name="Submit" value="Submit" />
	</td>

see

SELECT contact.contact_id, contact.contact_name FROM contact

. It only select contact_id & contact_name. Just select first name & last name.

[Budove]

GAH! Stupid mistake. I thought I wan't understanding. Now I know I did understand it, just wasn't paying attention.

Thank you so much for clearing it up for me.

[Rksk]

you are welcome always...