Jquery multi dimensional JSON from MySQL Query

Question

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?

Not without some code in between. You will need to loop the results in PHP and return the std-object with json_encode. — Mouser
– Mouser, Commented Mar 11, 2015 at 17:01
I devised a PHP solution for this, however the most optimized way would be using a stored procedure. — Mouser
– Mouser, Commented Mar 11, 2015 at 17:52

Mouser · Accepted Answer · 2015-03-11 17:50:33Z

1

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"}
     }
}

edited Mar 11, 2015 at 17:50

answered Mar 11, 2015 at 17:42

Mouser

13.3k3 gold badges30 silver badges54 bronze badges

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

2 Comments

sarah.ferguson Over a year ago

Thank you, but that's a very long solution, and notice you started from an array of array, I should also implement the conversion from SQL result to array of array as you pointed. Thought there had to be something quicker I was missing.

Mouser Over a year ago

The quicker way would be to do this inside the sql query.

Collectives™ on Stack Overflow

Jquery multi dimensional JSON from MySQL Query

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related