Consolidate JSON array in PHP from MySQL Query Results

user_16913076 0
Viewed 34

The existing code looks like this:

function getList(){
 
   $sql = 'SELECT DISTINCT `person`.`person_name` as name, `skill`.`skill_name` as 
   skill,`skill_linker`.`skill_score`;
       
   $result = $GLOBALS['conn']->query($sql);

if ($result->num_rows > 0) {
    $emptyArray = array();
 
  while($row = $result->fetch_assoc()) {
    $emptyArray[] = $row;
  }
  echo json_encode($emptyArray);
 } 
}

JSON Output from above function:

[{
"name":"Bob Sun",
"skill":"Dancing",
"skill_score":"3"
},

{
"name":"Bob Sun",
"skill":"Surfing",
"skill_score":"2"
},

{
"name":"Bob Sun",
"skill":"Swimming",
"skill_score":"5"
},

{
"name":"Joe Steel",
"skill":"Eating",
"skill_score":"2"
},
{
"name":"Joe Steel",
"skill":"Cooking",
"skill_score":"3"
}]

Instead, I would like the JSON output to look something like this:

[
      {
        "name": "Bob Sun",
        "skills" : [
            "Dancing": 3,
            "Surfing": 2,
            "Swimming": 5
        ]
      },
    
      {
        "name": "Joe Steel",
        "skills" : [
            "Eating": 2,
            "Cooking": 3
        ]
      }
]

Can this be done? How can I have it display skills:skill_score for each person if the data coming from the SQL result set is separate? How can I change the code to restructure the JSON data to have semi-categories?

mysql php json
2 Answers

Just transform the array in your while cycle.

  while($row = $result->fetch_assoc()) {
    $emptyArray[$row['name']]['name'] = $row['name'];
    $emptyArray[$row['name']]['skills'][$row['skill']] = $row['skill_score'];
  }

Welcome to StackOverflow.

user_18637750

insted of applying json_encode on the final array ,

  • apply it on row
  • concatinate all rows
  • inset final concatinated string into array

this may solve the issue

user_110167480
Related