How do I group from non-existent groups?

[louisp]

I currently have:

<p><b>Goalkeepers</b></p>
<? $result = mysql_query("SELECT * FROM players WHERE position='1' ORDER BY squadno,surname",$db);

if ($myrow = mysql_fetch_array($result)) {

  // display list if there are records to display

  do {

printf("<a href=\"profiles.php?playerid=%s\">%s %s</a> - %s<br>\n",

$myrow["playerid"], $myrow["firstname"], $myrow["surname"], $myrow["breifpro"]);
} while ($myrow = mysql_fetch_array($result));

}

?>
<p><b>Defenders</b></p>

<? $result = mysql_query("SELECT * FROM players WHERE position='2' ORDER BY squadno,surname",$db);

if ($myrow = mysql_fetch_array($result)) {

  // display list if there are records to display

  do {

printf("<a href=\"profiles.php?playerid=%s\">%s %s</a> - %s<br>\n", $myrow["playerid"], $myrow["firstname"], $myrow["surname"], $myrow["breifpro"]);
} while ($myrow = mysql_fetch_array($result));

}

?>
<p><b>Midfielders</b></p>

<? $result = mysql_query("SELECT * FROM players WHERE position='3' ORDER BY squadno,surname",$db);

if ($myrow = mysql_fetch_array($result)) {

  // display list if there are records to display

  do {

printf("<a href=\"profiles.php?playerid=%s\">%s %s</a> - %s<br>\n", $myrow["playerid"], $myrow["firstname"], $myrow["surname"], $myrow["breifpro"]);
}
while ($myrow = mysql_fetch_array($result));

}

?>
<p><b>Attackers</b></p>
<? $result = mysql_query("SELECT * FROM players WHERE position='4' ORDER BY squadno,surname",$db);

if ($myrow = mysql_fetch_array($result)) {

  // display list if there are records to display

  do {
    printf("<a href=\"profiles.php?playerid=%s\">%s %s</a> - %s<br>\n", $myrow["playerid"], $myrow["firstname"], $myrow["surname"], $myrow["breifpro"]);

  }

while ($myrow = mysql_fetch_array($result));

}
?>

and I'd like to put all of this into one query but seperating the players by their positions.. 1=goalkeeper,2=defender,3=midfielder & 4=attacker..

I've tried something involving:

if ($myrow[position] <> $position) {
but this just ruins positions in the player profiles, it gives them the wrong position.

[Anon]

This should be pretty close.

<?
$result = mysql_query("SELECT * FROM players where position in (1,2,3,4) ORDER BY position, squadno,surname",$db);

if ($myrow = mysql_fetch_array($result)) {

// display list if there are records to display

$currentpostion = 0;
$positions = array (1=>"Goalkeeper", 2=>"Defenders", 3=>"Midfielders", 4=>"Attackers");

do {

if ($currentposition != $myrow["position"]) {
$currentposition = $myrow["position"];
echo "<p>" . $positions[$currentposition] . "</p>";
}

printf("<a href=\"profiles.php?playerid=%s\">%s %s</a> - %s<br>\n",

$myrow["playerid"], $myrow["firstname"], $myrow["surname"], $myrow["breifpro"]);
} while ($myrow = mysql_fetch_array($result));
}
?>

[louisp]

Bloody perfect!!

Thanks a lot!