php - How to create hierarchical json using data from a psql database? -

i using php connect psql database , have initial code connect database, access table , set column array etc. i, however, have been struggling data desired format code running on. input in json hierarchical data form, following.

function getdata() {     return {  "name": "data",  "children": [   {    "name": "america",    "children": [     {      "name": "ma",      "children": [       {"name": "westborough", "order": 30},       {"name": "southborough", "order":20}      ]     },     {      "name": "nh",      "children": [       {"name": "nashua", "order": 12}      ]     },     {      "name": "ca",      "children": [       {"name": "sanfransico", "order": 17}      ]     }    ] } ] }; 

this code have using php:

<?php     // attempt connection  $dbh = pg_connect("host=localhost dbname=sample user=postgres");  if (!$dbh) {      die("error in connection: " . pg_last_error());  }          // execute query  $sql = "select * dataset";  $result = pg_query($dbh, $sql);  if (!$result) {      die("error in sql query: " . pg_last_error());  }         //sets column @ 1 array  $arr = pg_fetch_all_columns($result, 1);    // free memory  pg_free_result($result);          // close connection  pg_close($dbh);  ?> 

this format of database table

thanks in advance :)

i code dome you.you need check yourselt

$dbh = pg_connect("host=localhost dbname=sample user=postgres");  $ret = array(); $ret['name'] = 'data'; $ret['children'] = get_chilren($dbh,0);  return  json_encode($ret);   while ($line = pg_fetch_array($result)) {  }   function get_chilren($dbh,$parentid){   var $cret = array();   $sql = "select * database parentid=".$parentid   $cresult = pg_query($dbh, $sql);   if (!$cresult) {     echo "an error occured.\n";     exit;   }   while($chil = pg_fetch_array($cresult)){ // fetch each row     $cret['name'] = $chil['name'];     $cc = get_chilren($dbh,$chil['id']);     if(is_array($cc))      {//when hava child       $cret['children'] = $cc;     }else{ //not hava       $cret['order'] = $chil['order'];     }   }   return $cret; }