0

I would like to convert my PHP script to JSON to return a specific structure with parent and child. I searched some similar questions here, but unfortuntly i didn't find a correct way to solve my doubt:

  1. How to create json array php with childs
  2. php json parse access child values
  3. Php echo json array with children
  4. How to select JSON (dynamic) Parent and Child in PHP?

I have a database table that call tbl_countries that's similar to the structure below:

id | countryID | country | city
-----------------------------------------
 1 |     1     | Greece  | Athens
 2 |     1     | Greece  | Thessalonica
 3 |     2     | Italy   | Rome
 4 |     2     | Italy   | Milan

What i would like to do is convert my php script to return a json structure as below:


    [{
      "id": 1,
      "text": "Greece",
      "children": [{
        "id": "Athens",
        "text": "Athens"
      }, {
        "id": "Thessalonica",
        "text": "Thessalonica"
      }]
    }, {
      "id": 2,
      "text": "Italy",
      "children": [{
        "id": "Milan",
        "text": "Milan"
      }, {
        "id": "Rome",
        "text": "Rome"
      }]
    }]

Below is the php script that i'm trying to convert like json structure above. This script, when executed, return information correctly, but not like the structure that i would like to has:


include 'config.php';

$stmt = $mysqli -> prepare('SELECT countryID, country, city FROM tbl_countries'); 
 
$stmt -> execute(); 
$stmt -> store_result(); 
$stmt -> bind_result($countryID, $country, $city);

$json = array();

while ($stmt -> fetch()) {

$json[] = array("id" => $countryID, "text" => $country, "children" => [array("id" => $city, "text" => $city)]);

}

header('Content-Type: application/json');
print_r(json_encode($json, JSON_UNESCAPED_UNICODE));

In this case how can i improve my php script to return a similar json structure above?

Improve this question
1
  • 4
    Your table structure seems weird, but I’ll ignore that. I personally like to make simple objects to represent my data, it is easier for me to reason about. Some people say it is overkill though. A quick demo might be something like this: 3v4l.org/IPQUX Commented Jun 7, 2024 at 4:03

1 Answer 1

Reset to default
1

First, i would rename the database table tbl_countries into tbl_cities because that's what they are, and at the json output result of children[]['id'] should be the field id of the table above and not the city name. But this is what I would do.

So, giving your code, this is how I would improve it to give your expected result:

include 'config.php';

$stmt = $mysqli -> prepare('SELECT countryID, country, city FROM tbl_countries'); 
 
$stmt -> execute(); 
$stmt -> store_result(); 
$stmt -> bind_result($countryID, $country, $city);

$json = array();
$countryIdsArr = []; // this would help keeping track of processed countries
while ($stmt -> fetch()) {

    $countryIdx = array_search($countryID, $countryIdsArr);
    if ($countryIdx !== false) { //country row exists
        $json[$countryIdx]['children'][] = ['id' => $city, 'text' => $city]; // insert row into existing children array
    } else {
        $countryIdsArr[] = $countryID; // add the country id
        $json[] = ['id' => $countryID, 'text' => $country, 'children' => [['id' => $city, 'text' => $city]]]; // add the row
    }
}

header('Content-Type: application/json');
print_r(json_encode($json, JSON_UNESCAPED_UNICODE));
Improve this answer
Sign up to request clarification or add additional context in comments.

1 Comment

Thanks for the help! I will make some adjustments to my script based on this solution.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.