Joining Several FKs to Single Row

[smerny]

I have two tables, one of them is "weeks" and one of them is "pengs".

 

Weeks has ID, p1, p2 ... p9, p10 (ID=PK, all others are FK to "pengs" table)

Pengs has ID, points, [other stuff]

 

I'm wanting results like this:

 

Week 1 - 4(1), 5(2), 9(2), 10(2), 11(1), 13(2), 20(1), 24(2), 33(1), 35(1)

Week 2 - etc...

 

so I'd need to loop

 

"Week ".$row['week']." - ".$row['p1']."(".$row['pts1']."), ".$row['p2']."(".$row['pts2']."), "... etc

?

I'm not sure how to go about this

[fenway]

What?

[awjudd]

It sounds like you are wanting to make a pivot table.  There are a few topics online about it if this is the case (http://en.wikibooks.org/wiki/MySQL/Pivot_table).

 

~juddster

[smerny]

fen i reread my first post and i am unsure of what you are whatting about.

 

anyway, this isn't pretty or efficient but i suppose this isnt something that will be loaded often. here is what i ended up doing that works like i wanted:

 

echo "<table><tr><td>Week</td><td colspan='5'>1 pointers</td><td colspan='5'>2 pointers</td></tr>";
$search = "SELECT * FROM weeks";
$result = mysql_query($search) or die("SQL Error: " . mysql_error());
while($week = mysql_fetch_assoc($result)){
	echo "<tr><td>".$week['week']."</td>";

	$stack = array();

	$printRow = "<td>".$week['p1'];
	$search = "SELECT points FROM pengarchive WHERE ID='".$week['p1']."'";
	$res = mysql_query($search) or die("SQL Error: " . mysql_error());
	$peng = mysql_fetch_assoc($res);
	$printRow .= "</td>";

	if($peng['points'] == 2)
		array_push($stack, $printRow);
	else
		echo $printRow;

	$printRow = "<td>".$week['p2'];
	$search = "SELECT points FROM pengarchive WHERE ID='".$week['p2']."'";
	$res = mysql_query($search) or die("SQL Error: " . mysql_error());
	$peng = mysql_fetch_assoc($res);
	$printRow .= "</td>";

	if($peng['points'] == 2)
		array_push($stack, $printRow);
	else
		echo $printRow;

	$printRow = "<td>".$week['p3'];
	$search = "SELECT points FROM pengarchive WHERE ID='".$week['p3']."'";
	$res = mysql_query($search) or die("SQL Error: " . mysql_error());
	$peng = mysql_fetch_assoc($res);
	$printRow .= "</td>";

	if($peng['points'] == 2)
		array_push($stack, $printRow);
	else
		echo $printRow;		

	$printRow = "<td>".$week['p4'];
	$search = "SELECT points FROM pengarchive WHERE ID='".$week['p4']."'";
	$res = mysql_query($search) or die("SQL Error: " . mysql_error());
	$peng = mysql_fetch_assoc($res);
	$printRow .= "</td>";

	if($peng['points'] == 2)
		array_push($stack, $printRow);
	else
		echo $printRow;		

	$printRow = "<td>".$week['p5'];
	$search = "SELECT points FROM pengarchive WHERE ID='".$week['p5']."'";
	$res = mysql_query($search) or die("SQL Error: " . mysql_error());
	$peng = mysql_fetch_assoc($res);
	$printRow .= "</td>";

	if($peng['points'] == 2)
		array_push($stack, $printRow);
	else
		echo $printRow;		

	$printRow = "<td>".$week['p6'];
	$search = "SELECT points FROM pengarchive WHERE ID='".$week['p6']."'";
	$res = mysql_query($search) or die("SQL Error: " . mysql_error());
	$peng = mysql_fetch_assoc($res);
	$printRow .= "</td>";

	if($peng['points'] == 2)
		array_push($stack, $printRow);
	else
		echo $printRow;		

	$printRow = "<td>".$week['p7'];
	$search = "SELECT points FROM pengarchive WHERE ID='".$week['p7']."'";
	$res = mysql_query($search) or die("SQL Error: " . mysql_error());
	$peng = mysql_fetch_assoc($res);
	$printRow .= "</td>";

	if($peng['points'] == 2)
		array_push($stack, $printRow);
	else
		echo $printRow;		

	$printRow = "<td>".$week['p8'];
	$search = "SELECT points FROM pengarchive WHERE ID='".$week['p8']."'";
	$res = mysql_query($search) or die("SQL Error: " . mysql_error());
	$peng = mysql_fetch_assoc($res);
	$printRow .= "</td>";

	if($peng['points'] == 2)
		array_push($stack, $printRow);
	else
		echo $printRow;		

	$printRow = "<td>".$week['p9'];
	$search = "SELECT points FROM pengarchive WHERE ID='".$week['p9']."'";
	$res = mysql_query($search) or die("SQL Error: " . mysql_error());
	$peng = mysql_fetch_assoc($res);
	$printRow .= "</td>";

	if($peng['points'] == 2)
		array_push($stack, $printRow);
	else
		echo $printRow;		

	$printRow = "<td>".$week['p10'];
	$search = "SELECT points FROM pengarchive WHERE ID='".$week['p10']."'";
	$res = mysql_query($search) or die("SQL Error: " . mysql_error());
	$peng = mysql_fetch_assoc($res);
	$printRow .= "</td>";

	if($peng['points'] == 2)
		array_push($stack, $printRow);
	else
		echo $printRow;	

	foreach($stack as $stackRow)
		echo $stackRow;

	echo "</tr>";
}
echo "</table>";

[fenway]

I assumed that's what you started with, and were looking for a better solution -- juddster's advice was appropriate.

[smerny]

i'll check into pivot tables, thanks

[mikosiko]

you will be much better if you redesign your "weeks" table ... actually it has the infamous "spreadsheet style"... a simple re-design with 3 tables will give you more flexibility and should simplify your queries (using just a JOIN), after that, the generation of  a "pivot table" style report is just a matter of display.

 

suggestion:

Weeks

week-ID

[other week related stuff].. take out of here all the p1..p2...etc.

 

Pengs

peng-ID

points

etc..etc

 

WEEK-PENGS  this table hold the relation between weeks and pegs, and will not be limited to just 10 pengs as with your current design

week-id

peng-id

 

with this design all the code (10 or so SQL's)  that you previously posted will be reduced to just one query:

SELECT weeks.week-id,
       week-pengs.peng-id,
       pengs.points
  FROM weeks
       JOIN week-pengs ON weeks.week-ID = week-pengs.week-ID
       JOIN pengs ON week-pengs.peng-ID = pengs.peng-ID