CODEWITHSUNDEEP
phpjqueryarraysjsonmysqli
Reflow
Reflow

Reputation: 85

One mysqli table into JSON format with PHP

I am trying to get the desired return I want with a specific mysqli table, which has the following structure:

id | name  | parentid
--------------------
1  | Boss    | 0
2  | Bob     | 1
3  | Chef1   | 1
4  | Chef2   | 1
5  | Lara    | 3
6  | Kim     | 4
7  | Nick    | 1
63 | Oldboss | 20

I need to get for each parent name a new:

[
  {  
    "name": "Boss",
    "attributes": {
      "data-id": "1"
    },
    "children": [
      {      
        "name": "Bob",
        "attributes": {
          "data-id": "2"
        }
      },
      {
        "name": "Chef1",
        "attributes": {
          "data-id": "3"
        }
      },
      {
        "name": "Chef2",
        "attributes": {
          "data-id": "4"
        }
      },
      {
        "name": "Nick",
        "attributes": {
          "data-id": "7"
        }
      }
    ]
  },
  {  
    "name": "Chef1",
    "attributes": {
      "data-id": "3"
    },
    "children": [
      {      
        "name": "Lara",
        "attributes": {
          "data-id": "5"
        }
      }
    ]
  },
  {  
    "name": "Chef2",
    "attributes": {
      "data-id": "4"
    },
    "children": [
      {      
        "name": "Kim",
        "attributes": {
          "data-id": "6"
        }
      }
    ]
  }
]

That's what I need to get, for each Name which has childs need to get at the top, but also displayed under his parent. This will create the same result when using an echo into a while format. But I just can't get the array / JSON to build up how I want it to result. This is what I have currently:

$returnarray = array();

$sql = "SELECT DISTINCT
  t_names.name AS Name2,
  t_names.id AS ID2
FROM t_names
  INNER JOIN t_names t_names_1
    ON t_names.id = t_names_1.parentid
WHERE t_names.parentid <> 63
AND t_names.id <> 63";
$result = mysqli_query($conn, $sql);
while($row = mysqli_fetch_array($result)){


$subarray = array(
        "data-id"=> $row['ID2']
    );  

$headarray = array(
    "name"=> $row2['Name2'],
    "attributes"=> $subarray,
    "children"=>$rowarray2
);

$sql2 = "SELECT DISTINCT id, name, parentid FROM t_names WHERE parentid = '{$row[ID2]}' ";
$result2 = mysqli_query($conn, $sql2);
while($row2 = mysqli_fetch_assoc($result2)){

$subarray2 = array(
        "data-id"=> $row2['id']
    ); 

$rowarray = array(
        "name"=> $row2['Name'],
        "attributes"=> $subarray2
    );  

$rowarray2[] = $rowarray;

}

$returnarray[] = $headarray;

}

echo "<pre>";

echo json_encode($returnarray, JSON_PRETTY_PRINT);

echo "</pre>";

Above result does create a json format that results in the code:

- Boss
Bob
Chef1
Chef2
Nick

-Chef1
Bob
Chef1
Chef2
Nick
Lara

-Chef2
Bob
Chef1
Chef2
Nick
Lara
Kim

So it creates the groups, but it keeps using the data from previous groups and adds the correct names after them.

What am I dong wrong with my arrays?

Upvotes: 2

Views: 131

Answers (1)

Jeff Puckett
Jeff Puckett

Reputation: 40861

I would just join the table once on that unary relation, iterate through the rows grouping under the same parent indices, and then collapse top-tier.

function transformEmployee($user)
{
    $output['name'] = $user['employee'];
    $output['attributes']['data-id'] = $user['emp_id'];
    return $output;
}

$mysqli = new mysqli('server', 'username', 'password', 'database');

$sql = '
    SELECT bosses.id AS boss_id, bosses.name AS boss,
        employees.id AS emp_id, employees.name AS employee
    FROM names bosses
    JOIN names employees
      ON bosses.id = employees.parent_id
';

$names = $mysqli->query($sql)->fetch_all(MYSQLI_ASSOC);

foreach ($names as $name) {
    $result[$name['boss_id']]['name'] = $name['boss'];
    $result[$name['boss_id']]['attributes']['data-id'] = $name['boss_id'];
    $result[$name['boss_id']]['children'] []= transformEmployee($name);
}

echo json_encode(array_values($result), JSON_PRETTY_PRINT);

Upvotes: 1

Related Questions