SQL query result with left join and resulting array with PHP

This will convert your current array into the one you want:

$newArray = array();
foreach ($resultArray as $record) {
    $currentTeam = $record('TeamNo');
    if (!array_key_exists($currentTeam, $newArray)) {
        $newArray[$currentTeam] = array('TeamNo' => $currentTeam, 'TeamParticipants => array());
    }
    if (!empty($record['ParticipantFirstName']) {
        $newArray[$currentTeam]['TeamParticipants'][] = array('ParticipantFirstName' => $record['ParticipantFirstName'], 'ParticipantLastName' => $record['ParticipantLastName']);
    }
}

This is largely untested, but it should provide basically what you want. The only difference is that I use an associative array on the outside to easily look up which team numbers have already been created.

I'm not making any judgment call on why you want the array transformed this way. I'm assuming you know best why you want the array in this format. This should help you get it there.

Did you try mysql_fetch_assoc() or mysql_fetch_array()? what function are you using in fetching the outputs

 $query = "SELECT * FROM teams
 LEFT JOIN participants
 ON teams.teamNo = participants.teamNo
 ORDER BY teams.teamNo ASC"

 $result = mysql_query($query);

 while($row = mysql_fetch_array($result)){
     echo $row[0];
     echo $row[1];
     //and so on, this will display the data starting by the index 0 which is the first field
 }

Something like this should do:

$q=mysql_query("your query");
while($r=mysql_fetch_assoc($q)) {
  $a["Team ".$r["teamNo"]][]=array("fname"=>$r["firstName"], "sname"=>$r["surname"]);
}

$a["Team ".$r["teamNo"]] creates - or references the existing - value at associative index "Team N" of array $a.

$a["Team ".$r["teamNo"]][] says that the said value is an auto-incremented int-indexed array (therefore creating a new int key on each call).

We set the value matching this auto-incremented index to an array with 2 key/value pairs: "fname" referencing the current participant's first name, and "sname" referencing his/her surname. This gives the structure:

$a : array {
  "Team 1" : array {
    0 : array {
      "fname" > "Billy"
      "sname" > "The Kid"
    }
  }
  "Team 1" : array {
    1 : array {
      "fname" > "John"
      "sname" > "Dillinger"
    }
  }
  "Team 2" : array {
    0 : array {
      "fname" > "Melvin"
      "sname" > "Purvis"
    }
  }

  "Team 2" : array {
    1 : array {
      "fname" > "Eliot"
      "sname" > "Ness"
    }
  }
}

Using PHP, you access it like:

$a["Team 1"][0]["fname"] //Billy
$a["Team 1"][1]["fname"] //John
$a["Team 1"][0]["sname"] //The Kid
$a["Team 2"][1]["sname"] //Ness

So with a loop for display purposes for instance:

foreach($a as $team=>$participants) {
  echo "<h1>$team</h1>";
  foreach($participants as $identity) { //no need for keys here, as they're semantically irrelevant
    echo $identity["fname"]." ".strtoupper($identity["sname"])."<br>";
  }
}

This will display

<h1>Team 1</h1>
Billy THE KID<br>
John DILLINGER<br>
<h1>Team 2</h1>
Melvin PURVIS<br>
Eliot NESS<br>

Hope this is clear enough for you to better understand arrays.