Mapping SQL query results to JSON in PHP

Question

I'm running into some issues getting the correct JSON output from my SQL query. Essentially what I'm struggling with is getting an array of options objects as opposed to singular option objects.

$query = 'SELECT matchup.matchupID, matchup_option.player_name, matchup_option.player_id FROM matchup 
        INNER JOIN matchup_option
        ON matchup_option.matchupID= matchup.matchupID;';

$attachments = $db->query($query);
$data = array();
while ($attachment = $db->fetch_array($attachments)){
    $data[] = array (
        'id' => $attachment['matchupID'],
        'options' => array(
            array (
                "name" => $attachment['player_name'],
                "playerid" => $attachment['player_id']
            )
        )
    );
    //VAR_DUMP($attachment);
}
$data = array("matchup"=>$data);
print json_encode($data);

Gives me this output:

{
"matchup":[
  {
     "id":"111222",
     "options":[
        {
           "name":"111",
           "playerid":"111"
        }
     ]
  },
  {
     "id":"111222",
     "options":[
        {
           "name":"222",
           "playerid":"222"
        }
     ]
  }
]
}

And here's what I'm trying to get to:

{
"matchup":[
  {
     "id":"111222",
     "options":[
        {
           "name":"111",
           "playerid":"111"
        },
        {
           "name":"222",
           "playerid":"222"
        }
     ]
  }
]
}

I'd like to follow best practices as well as structure this appropriately, if there's a better way to go about this, please let me know!

Will the id be same for all your rows ?

Rajdeep Paul
– Rajdeep Paul

2017-07-09 16:44:05 +00:00
Commented Jul 9, 2017 at 16:44 — Rajdeep Paul
– Rajdeep Paul, Commented Jul 9, 2017 at 16:44

u_mulder · Accepted Answer · 2017-07-09 16:44:08Z

1

You need to store $attachment['matchupID'] as an array key of $data:

$data = array();
while ($attachment = $db->fetch_array($attachments)){
    if (!isset($data[$attachment['matchupID']])) {
        $data[$attachment['matchupID']] = array (
            'id' => $attachment['matchupID'],
            'options' => array()
        );
    }
    $data[$attachment['matchupID']]['options'][] = array (
        "name" => $attachment['player_name'],
        "playerid" => $attachment['player_id']
    );
}

// use `array_values` to reindex `$data`
$data = array("matchup" => array_values($data));
print json_encode($data);

answered Jul 9, 2017 at 16:44

u_mulder

54.8k5 gold badges54 silver badges72 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

user3344880 Over a year ago

The solution is always so damn simple! Thank you my friend.

Collectives™ on Stack Overflow

Mapping SQL query results to JSON in PHP

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related