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

[ridz]

I am using php to connect to my psql database and have some initial code to connect to a database, access a table and set a column to an array etc. I, however, have been struggling to get my data into a desired format that my code is already running on. My input is in Json hierarchical data form, as 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 is the code I currently have using php:
 

<?php

// attempt a connection
$dbh = pg_connect("host=localhost dbname=sample user=postgres");
if (!$dbh) {
die("Error in connection: " . pg_last_error());
}

// execute query
$sql = "SELECT * FROM dataset";
$result = pg_query($dbh, $sql);
if (!$result) {
die("Error in SQL query: " . pg_last_error());
}

//Sets the column at 1 to an array
$arr = pg_fetch_all_columns($result, 1);


// free memory
pg_free_result($result);

// close connection
pg_close($dbh);

?>

This is the format of the database table



Thanks in advance

Edited April 17, 2013 by ridz