One to many relationship in two JSON arrays, lookup results in PHP
I have two separate JSON arrays. Array 1 is called matches, Array 2 is called participants. Participants can be in many matches. I need some direction in how to look up the player id from matches within the matches array and return the corresponding participants name from the participants array.
Matches contains: player1_id and player2_d for each match. Participants contains id for each player. player1_id and player2_id will equal an id within the participants array, unless player1_id or player2_id is null.
Sample Match array:
[
{
"match": {
"attachment_count": null,
"created_at": "2015-01-19T16:57:17-05:00",
"group_id": null,
"has_attachment": false,
"id": 23575258,
"identifier": "A",
"location": null,
"loser_id": null,
"player1_id": 16543993,
"player1_is_prereq_match_loser": false,
"player1_prereq_match_id": null,
"player1_votes": null,
"player2_id": 16543997,
"player2_is_prereq_match_loser": false,
"player2_prereq_match_id": null,
"player2_votes": null,
"round": 1,
"scheduled_time": null,
"started_at": "2015-01-19T16:57:17-05:00",
"state": "open",
"tournament_id": 1086875,
"underway_at": null,
"updated_at": "2015-01-19T16:57:17-05:00",
"winner_id": null,
"prerequisite_match_ids_csv": "",
"scores_csv": ""
}
},
{
"match": {
"attachment_count": null,
"created_at": "2015-01-19T16:57:17-05:00",
"group_id": null,
"has_attachment": false,
"id": 23575259,
"identifier": "B",
"location": null,
"loser_id": null,
"player1_id": 16543994,
"player1_is_prereq_match_loser": false,
"player1_prereq_match_id": null,
"player1_votes": null,
"player2_id": 16543996,
"player2_is_prereq_match_loser": false,
"player2_prereq_match_id": null,
"player2_votes": null,
"round": 1,
"scheduled_time": null,
"started_at": "2015-01-19T16:57:17-05:00",
"state": "open",
"tournament_id": 1086875,
"underway_at": null,
"updated_at": "2015-01-19T16:57:17-05:00",
"winner_id": null,
"prerequisite_match_ids_csv": "",
"scores_csv": ""
}
},
{
"match": {
"attachment_count": null,
"created_at": "2015-01-19T16:57:17-05:00",
"group_id": null,
"has_attachment": false,
"id": 23575260,
"identifier": "C",
"location": null,
"loser_id": null,
"player1_id": null,
"player1_is_prereq_match_loser": false,
"player1_prereq_match_id": 23575258,
"player1_votes": null,
"player2_id": null,
"player2_is_prereq_match_loser": false,
"player2_prereq_match_id": 23575259,
"player2_votes": null,
"round": 2,
"scheduled_time": null,
"started_at": null,
"state": "pending",
"tournament_id": 1086875,
"underway_at": null,
"updated_at": "2015-01-19T16:57:17-05:00",
"winner_id": null,
"prerequisite_match_ids_csv": "23575258,23575259",
"scores_csv": ""
}
}
]
My sample participants array:
[
{
"participant": {
"active": true,
"checked_in_at": null,
"created_at": "2015-01-19T16:54:40-05:00",
"final_rank": null,
"group_id": null,
"icon": null,
"id": 16543993,
"invitation_id": null,
"invite_email": null,
"misc": null,
"name": "Participant #1",
"on_waiting_list": false,
"seed": 1,
"tournament_id": 1086875,
"updated_at": "2015-01-19T16:54:40-05:00",
"challonge_username": null,
"challonge_email_address_verified": null,
"removable": true,
"participatable_or_invitation_attached": false,
"confirm_remove": true,
"invitation_pending": false,
"display_name_with_invitation_email_address": "Participant #1",
"email_hash": null,
"username": null,
"attached_participatable_portrait_url": null,
"can_check_in": false,
"checked_in": false,
"reactivatable": false
}
},
{
"participant": {
"active": true,
"checked_in_at": null,
"created_at": "2015-01-19T16:54:43-05:00",
"final_rank": null,
"group_id": null,
"icon": null,
"id": 16543994,
"invitation_id": null,
"invite_email": null,
"misc": null,
"name": "Participant #2",
"on_waiting_list": false,
"seed": 2,
"tournament_id": 1086875,
"updated_at": "2015-01-19T16:54:43-05:00",
"challonge_username": null,
"challonge_email_address_verified": null,
"removable": true,
"participatable_or_invitation_attached": false,
"confirm_remove": true,
"invitation_pending": false,
"display_name_with_invitation_email_address": "Participant #2",
"email_hash": null,
"username": null,
"attached_participatable_portrait_url": null,
"can_check_in": false,
"checked_in": false,
"reactivatable": false
}
},
{
"participant": {
"active": true,
"checked_in_at": null,
"created_at": "2015-01-19T16:57:10-05:00",
"final_rank": null,
"group_id": null,
"icon": null,
"id": 16543996,
"invitation_id": null,
"invite_email": null,
"misc": null,
"name": "Participant #3",
"on_waiting_list": false,
"seed": 3,
"tournament_id": 1086875,
"updated_at": "2015-01-19T16:57:10-05:00",
"challonge_username": null,
"challonge_email_address_verified": null,
"removable": true,
"participatable_or_invitation_attached": false,
"confirm_remove": true,
"invitation_pending": false,
"display_name_with_invitation_email_address": "Participant #3",
"email_hash": null,
"username": null,
"attached_participatable_portrait_url": null,
"can_check_in": false,
"checked_in": false,
"reactivatable": false
}
},
{
"participant": {
"active": true,
"checked_in_at": null,
"created_at": "2015-01-19T16:57:12-05:00",
"final_rank": null,
"group_id": null,
"icon": null,
"id": 16543997,
"invitation_id": null,
"invite_email": null,
"misc": null,
"name": "Participant #4",
"on_waiting_list": false,
"seed": 4,
"tournament_id": 1086875,
"updated_at": "2015-01-19T16:57:12-05:00",
"challonge_username": null,
"challonge_email_address_verified": null,
"removable": true,
"participatable_or_invitation_attached": false,
"confirm_remove": true,
"invitation_pending": false,
"display_name_with_invitation_email_address": "Participant #4",
"email_hash": null,
"username": null,
"attached_participatable_portrait_url": null,
"can_check_in": false,
"checked_in": false,
"reactivatable": false
}
}
]
My desired output would be:
- Participant #4 vs Participant #2
- Participant #1 vs Participant #3
and so on for every match in the match array.
and so on.
To my knowledge, merging both arrays won't work because of the one to many relationship, but I could just be uneducated on merging. Any guidance on where to start would be appreiciated. I've looked at filtering too, but that doesn't seem to be my answer either.
Answer
Solution:
This first indexes the participants by the id, it stores the whole array in case you need any other details later. The end result is an array called $participants indexed by the id.
Then it loops through the matches and extracts the two id's, if they both have a value, then it outputs the name from the stored array, otherwise uses Unknown for the name.
$participant = json_decode(file_get_contents("t.json"), true);
// Extract subarrays
$participants = array_column($participant, "participant");
// index by id
$participants = array_column($participants, null, "id");
$matches = json_decode(file_get_contents("a.json"), true);
foreach ( $matches as $match ) {
// Extract player ID's
$id1 = $match['match']['player1_id'];
$id2 = $match['match']['player2_id'];
if ( $id1 && $id2 ) {
echo $participants[$id1]["name"] . " vs " . $participants[$id2]["name"].PHP_EOL;
}
else {
echo "Unknown vs Unknown".PHP_EOL;
}
}