SQL query result with left join and result array with PHP
I have the following request:
SELECT * FROM teams
LEFT JOIN participants
ON teams.teamNo = participants.teamNo
ORDER BY teams.teamNo ASC
The request obviously gets all the teams in the team table and where there is a matching member in the team table that is returned. Not every team can nominate members, but every team must be displayed.
I want to decompose the data in one page (since team 2 has no current members, but still displays:
Team 1:
- Participant 1
- Participant 2
- Participant 3
Team 2:
Team 3:
- Participant 1
- Participant 2
I have the following array returning from a SQL query:
Array
(
[0] => Array
(
[TeamNo] => 1
[ParticipantFirstNames] => Katie
[ParticipantSurname] => Bloggs
)
[1] => Array
(
[TeamNo] => 1
[ParticipantFirstNames] => Jenny
[ParticipantSurname] => Ruffles
)
[2] => Array
(
[TeamNo] => 1
[ParticipantFirstNames] => Hannah
[ParticipantSurname] => Cox
)
[3] => Array
(
[TeamNo] => 2
[ParticipantFirstNames] =>
[ParticipantSurname] =>
)
[4] => Array
(
[TeamNo] => 3
[ParticipantFirstNames] => Alex
[ParticipantSurname] => Glover
)
[5] => Array
(
[TeamNo] => 3
[ParticipantFirstNames] => Karl
[ParticipantSurname] => Lawrence
)
I believe I need to convert it to an array that looks a bit like the following, but I'm not sure how to do it in PHP:
array(
array( 'TeamNo' => '1',
'TeamParticipants' => array(
array( 'ParticipantFirstName' => 'Harry',
'ParticipantSurname' => 'Bloggs'),
array( 'ParticipantFirstName' => 'James',
'ParticipantSurname' => 'Car'))
)
array( 'TeamNo' => '2',
'TeamParticipants' => array() )
array( 'TeamNo' => '3',
'TeamParticipants' => array(
array( 'ParticipantFirstName' => 'Harry',
'ParticipantSurname' => 'Bloggs'),
array( 'ParticipantFirstName' => 'James',
'ParticipantSurname' => 'Car')
)
)
)
I just can't get my head around the arrays, can someone help, or do I need another query in the first place? I am using PHP.
This will convert your current array to 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 pretty much untested, but it should provide basically what you want. The only difference is that I use an associative array outside to easily find which command numbers have already been created.
I won't judge why you want the array to convert this way. I assume you know why you want an array in this format. This should help you get it there.
source to share
Have you tried mysql_fetch_assoc () or mysql_fetch_array ()? what function are you using when fetching 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
}
source to share
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 an existing value at an associative "Team N"
array index $a
.
$a["Team ".$r["teamNo"]][]
says the specified value is an auto-incremented int-indexed array (so a new int key is created for each call).
We set the value corresponding to this auto-incrementing index into an array with two key / value pairs: "fname"
referencing the name of the current member and "sname"
referencing his / her last name. 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 this:
$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 to display, for example:
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>";
}
}
Will display
<h1>Team 1</h1>
Billy THE KID<br>
John DILLINGER<br>
<h1>Team 2</h1>
Melvin PURVIS<br>
Eliot NESS<br>
Hopefully this is clear enough for you to understand arrays better.
source to share