Page 1 of 1

Need Help using "order by"

Posted: Wed Dec 08, 2010 12:50 am
by webdzine
i have two tables
Table 1: players
Fields: id, teamid, firstname, lastname, position, height, weight, picture
Table 2: playerpositions
Fields: positionid, position

table 1 consists of many players where some players have the same "position"
table 2 consists of 4 unique positons
the field "position" in table 1 uses the values from field "positions" in table 2

my code looks like

Code: Select all

<table cellspacing="3" cellpadding="0" width="300">
     <?php      
$queryclient = "SELECT * FROM players where teamid=$clubid order by position";
$rscat = mysql_query($queryclient) or die('Error, query failed');
while($rowcat=mysql_fetch_object($rscat)){?>	
	<tr>
		<td width="45"><a href="playerprofile.php?pid=<? echo $row->id?>"><img src="<? echo $player_image_path.'/'.$rowcat->picture?>" height="48" width="44"></a></td>
		<td width="200" dir="ltr"><a href="playerprofile.php?pid=<? echo $rowcat->id?>"><? echo $rowcat->firstname.' '.$rowcat->lastname?></a><br><? echo $rowcat->position?></td>	
	</tr>
      <? } ?>  
</table>

but i want to order it by positionid instead of position
how do i create a query that will be able to order by a field in a different table

Re: Need Help using "order by"

Posted: Wed Dec 08, 2010 7:01 am
by Darhazer
Use a join

Code: Select all

$queryclient = "SELECT * FROM players INNER JOIN playerpositions USING(position) where teamid=$clubid order by positionid";

Re: Need Help using "order by"

Posted: Wed Dec 08, 2010 10:21 am
by webdzine
thank you very much.
works perfectly