Displaying cross-referenced tables? (Joins Question)

[Jackanape]

I'm running into a wall again, and I've been reading up on joins the last couple of days, but I'm just a hair from getting it, and my output just isn't working.  Basically, I have multiple tables, and I've written my query to pull that data I need from each, according to the id's I've assigned for cross-referencing purposes, here's a breakdown ( I have edited superfluous columns from the table listing below):

 

Tables:

[pre]-users-

Field Type

user_id  mediumint(8 )

username  varchar(25)

 

-picks- -

Field Type

user_id  mediumint(8 )

user_pick  tinyint(2)

selection_id  mediumint(8 )

tiebreaker  tinyint(3)

 

 

-selections-

Field Type

selection_id  mediumint(8 )

abbr  varchar(6) [/pre]

 

Where user-pick represents picks 1 through 10, to display in the table below, and abbr represents the name of the pick for the table.

What I want to do is print out a table that displays all users' picks, thusly:

 

[pre]2007 Pool Selections

Name 1st  TB 2nd 3rd 4th 5th 6th 7th 8th 9th 10th  [/pre]

 

I have cobbled together this code:

 

//Start printing table
print '<table border="1" width="100%" summary="2007 Pool">
<tr><td height="24" colspan="13"><h3>2007 Pool Selections</h3></td></tr>
<tr><td>Name</td><td>1st</td><td>TB</td><td>2nd</td><td>3rd</td><td>4th</td><td>5th</td><td>6th</td><td>7th</td><td>8th</td><td>9th</td><td>10th</td><td> </td></tr>';			   
		   
//Get Data

$sql = 'SELECT users.user_id, picks.user_pick, picks.selection_id, picks.tiebreaker, users.username
      	FROM picks, users
        WHERE picks.user_id = users.user_id
	GROUP BY users.user_id
	ORDER BY users.username
        ASC';	

if ( !($query = mysql_query($sql)) )
{	
print '<p>Could not conduct query because: <b>' . mysql_error() . '</b><br>The query was ' . $sql . '</p>';		
}	

while($row = mysql_fetch_assoc($query))
{
echo '<tr><td>' . $row['username'] . '</td><td>' . $row['selection_id'] . '</td><td>' . $row['tiebreaker'] . '</td>';   
$pick = array ($user_pick => $pick_id);
foreach ($pick as $user_pick => $pick_id)
{
    echo '<td>' . $pick_id . '</td>';
}	
echo '</tr>';
}
print '</table>';

 

My resulting output displays each user's name, their first pick, and their tiebreaker, but the rest of the array doesn't want to follow, with the rest of the columns empty.  I sohuld note that I haven't yet joined the selection.abbr with the picks.selection_id yet, because I just want to get this working first.

 

I've tried moving the tiebreaker to the end of my columns, so I can display the picks, 1-10 as a single array, possibly, but that produces no results, either...

 

I'm sure everyone here gets tired of hearing, "I'm a newbie", but, well...I am!  I'm well versed with phpBB, and have heavily modified code there, but writing from scratch is a whole new animal...

 

 

[Jackanape]

I'm getting closer.  I've changed my loops, inserting a loop withing the While loop, that should print each user's picks, but only reprints the first user's picks for each user...

 

Here's the updated query, which I've broken into two queries:

 

$sql = 'SELECT users.user_id, picks.user_pick, picks.selection_id, picks.tiebreaker, users.username
      	FROM picks, users
        WHERE picks.user_id = users.user_id	
	AND picks.user_pick = 1
	GROUP BY users.user_id
	ORDER BY users.username
        ASC';	

if ( !($query = mysql_query($sql)) )
{	
print '<p>Could not conduct query because: <b>' . mysql_error() . '</b><br>The query was ' . $sql . '</p>';		
}	

while($row = mysql_fetch_assoc($query))
{
echo '<tr><td>' . $row['username'] . '</td><td>' . $row['selection_id'] . '</td><td>' . $row['tiebreaker'] . '</td>';   
for($p = 2; $p < 11; $p++) 
{
$sql = 'SELECT users.user_id, picks.user_pick, picks.selection_id
      	FROM picks, users
        WHERE picks.user_id = users.user_id
	AND picks.user_pick = ' . $p ;	

if ( !($quer = mysql_query($sql)) )
	{	
	print '<p>Could not conduct query because: <b>' . mysql_error() . '</b><br>The query was ' . $sql . '</p>';		
	}
$r = mysql_fetch_assoc($quer);	
$pick = $r['selection_id'];
    echo '<td>' . $pick . '</td>';
}	
echo '</tr>';
}

 

Any reason anyone can see why my for loop isn't selecting each user's picks appropriately?