CODEWITHSUNDEEP
phparraysjsoncustomization
mrabhiram
mrabhiram

Reputation: 63

How to make custom json object with the result of a group by clause, php

I am trying to get a json object from php script. So far i have done this.

<?php

$connection=pg_connect("host=localhost port=5432 dbname=postgres user=postgres password=root")  or die("Can't connect to database".pg_last_error());

$result = pg_query('SELECT * FROM playground'); 
$rows = array();
while($r = pg_fetch_assoc($result)) {
 $rows[] = $r;
}
print json_encode(array_values(pg_fetch_all($result)));

?>

This returns the following object

[{"name":"slide","color":"blue","location":"south"},{"name":"slide","color":"green","location":"north"},{"name":"dont","color":"red","location":"west"}]

However, im trying to store them in more like....

[ 
{"name":"slide","values": [ "color" : ["blue","green"], "location": ["south", "north"] ]},
{"name":"dont","values" : ["color" : ["red"],"location" : ["west"] } 
]

Basically, i want to group the common group by parent field values as key, and their values as the values of the json object.

But i am not able to. Would appreciate if anybody could help. Thanks.

Upvotes: 1

Views: 247

Answers (1)

davidkonrad
davidkonrad

Reputation: 85518

You could just loop through $result and create the desired array structure yourself :

while($r = pg_fetch_assoc($result)) {
    if (!array_key_exists($r['name'], $rows)) {
        $rows[$r['name']] = array('values' => array( 'color' => array(), 'location' => array() ));
    }
    if (!in_array($r['color'], $rows[$r['name']]['values']['color'])) {
        $rows[$r['name']]['values']['color'][] = $r['color'];
    }
    if (!in_array($r['location'], $rows[$r['name']]['values']['location'])) {
        $rows[$r['name']]['values']['location'][] = $r['location'];
    }
}

echo json_encode($rows);

This will produce JSON on the form 

{
    "slide": {
        "values": {
            "color": [
                "blue",
                "green"
            ],
            "location": [
                "south",
                "north"
            ]
        }
    },
    "dont": {
        "values": {
            "color": [
                "red"
            ],
            "location": [
                "west"
            ]
        }
    }
}

Upvotes: 1

Related Questions