[SOLVED] Sort Groups

[Kingy]

I have two tables in my mysql database. One is called `team` and the other is called `group`.

 

They are set out like:

 

Team

 

teamID | teamName

-------------------

    1    |    Team1

    2    |    Team2

 

 

Group

 

group | teamID

---------------

  A    |    1

  B    |    2

 

 

Now what i want to do is orgainise it on a web page.

 

eg:

Group A:

Team 1

Team 4

Team 7

 

Group B:

Team 2

Team 3

Team 5

 

etc

 

I was thinking that I need to use a JOIN in the mysql query but after much trying i have gotten absolutley nowhere. Can anyone point me in the direction of how I can achieve this? Are the tables set out correctly?

 

Thanks

[phpdragon]

here is how I do it now based on help from this forum just change your queries and variables to suit, and of course the formatting 

 

<?php
		    $q = mysql_query ("SELECT collection, collectionID FROM collections ORDER BY collectionID DESC;" );
			if ( mysql_num_rows ( $q ) > 0 )
			{
				while ( $r = mysql_fetch_assoc ( $q ) )
				{
				echo "<tr><td class='collmenu'><a href='#' class='anchorclass " . $r['collection'] . "' rel='" . $r['collectionID'] . "' rev='lr'>" . $r['collection'] . "</a></td></tr><div id='" . $r['collectionID'] . "' class='anylinkcsscols'>";
				$s = mysql_query ("SELECT catalogID, name FROM catalog WHERE collectionID = '" . $r['collectionID'] . "' ORDER BY catalogID DESC;" );
				while ( $t = mysql_fetch_assoc ( $s ) )
					{
					echo "<div class='column'><ul><li><a href='products.php?catalogID=" . $t['catalogID'] . "&pagecount=1&collectionID=" . $r['collectionID'] . "'>" . $t['name'] . "</a></li></ul></div>";
					}
				}
			echo "</div>";
			}
			?>

[npsari]

I think you will have to use LEFT JOIN in order to join the 2 tables

 

So, your query will be something like that

 

$q = "SELECT t.teamID, t.teamName, u.group, u.teamID
FROM Team t
LEFT JOIN Group u
ON u.teamID = t.teamID
GROUP BY t.teamID
ORDER BY t.teamID DESC LIMIT 100;";

[Kingy]

ah LEFT JOIN, thanks very much npsari, works like a charm

 

I was also having problems with the word 'group' inside the query. Had to use `group`.