CODEWITHSUNDEEP
phpjquerymysqljson
sarah.ferguson
sarah.ferguson

Reputation: 3257

Jquery multi dimensional JSON from MySQL Query

I need to send a multidimensional array in JSON starting from a MySQL query .

The SQL Query is a LEFT JOIN such as this:

SELECT `TEAM`.`ID` AS TEAM_ID, `TEAM`.`NAME` AS TEAM_NAME, `TEAM_PLAYER`.`ID` AS TEAM_PLAYER_ID, `TEAM_PLAYER`.`NAME` AS TEAM_PLAYER_NAME FROM `TEAM_PLAYER`
LEFT JOIN `TEAM` ON `TEAM_PLAYER`.`TEAM_ID` = `TEAM`.`ID`

this query however returns a flat array with repeating columns such as

1 RED SOCKS 34 jOHN DOE

1 RED SOCKS 39 MICHAEL CAGE

2 VELVET 94 ARIA SAM

which is both redundant to send over the internet and cumbersome to treat.

I would like instead to get a JSON such as:

"0": {TEAM_ID : 1, TEAM_NAME : "RED SOCKS", "0": {TEAM_PLAYER_ID: 34, TEAM_PLAYER_NAME: JOHN DOE}, "1": {TEAM_PLAYER_ID: 39, TEAM_PLAYER_NAME: MICHAEL CAGE} },
"1": {TEAM_ID : 2, TEAM_NAME : "VELVET", "0": {TEAM_PLAYER_ID: 94, TEAM_PLAYER_NAME: ARIA SAM}

to return with a call to json_encode()

is there some proper way to do it?

Upvotes: 2

Views: 162

Answers (1)

Mouser
Mouser

Reputation: 13304

I would do it like this in PHP.

The basics: using an stdClass object to create fast easy to convert objects. use the {} notation to check for variable ids.

count(get_object_vars($master->{$id})): this is the most unusual part. It counts the amount of properties that are in the current selected property (id). Two properties are fixed: TEAM_ID and TEAM_NAME, so we can subtract those from the total, the others are team players. The integer returned will give us the current id for a new team player.

It's commented to explain, however the code is very self explanatory:

$array = array( array(1, "RED SOCKS", 34, "JOHN DOE"), array(1, "RED SOCKS", 39, "MICHAEL CAGE"), array(2, "VELVET", 94, "ARIA SAM") ); //demo
$master = new stdClass();

for ($i = 0; $i < count($array); $i++)
{
    $id = (intval($array[$i][0])-1); //subtract 1 to make it zero based.
    //check if the id is already present as object, if not create.
    if (!isset($master->{$id}))
    {
        //set id;
        $master->{$id} = new stdClass();
        $master->{$id}->TEAM_ID = $id+1;
        $master->{$id}->TEAM_NAME = $array[$i][1];
    }
    $currentKey = (count(get_object_vars($master->{$id}))) -2; //total key amount minus two fixed keys.
    $master->{$id}->{$currentKey} = new stdClass();
    $master->{$id}->{$currentKey}->TEAM_PLAYER_ID = $array[$i][2];
    $master->{$id}->{$currentKey}->TEAM_PLAYER_NAME = $array[$i][3];

}   

echo json_encode($master);

Result on my server:

{
 "0":{"TEAM_ID":1,"TEAM_NAME":"RED SOCKS",
      "0":{"TEAM_PLAYER_ID":34,"TEAM_PLAYER_NAME":"JOHN DOE"},
      "1":{"TEAM_PLAYER_ID":39,"TEAM_PLAYER_NAME":"MICHAEL CAGE"}
     },
 "1":{"TEAM_ID":2,"TEAM_NAME":"VELVET",
      "0":{"TEAM_PLAYER_ID":94,"TEAM_PLAYER_NAME":"ARIA SAM"}
     }
}

Upvotes: 1

Related Questions