PHP JSON Group by the same value

Question

I'm thinking how to group my array by objects with the same value.

I have this result from MySQL query:

| Id |    Name     |     Date     |  Value  |
|----|-------------|--------------|---------|
| 1  |    Jeff     |  2014-12-01  |    5    |          
| 1  |    Jeff     |  2015-12-01  |    8    | 
| 2  |    David    |  2014-12-01  |    7    |      
| 2  |    David    |  2015-12-01  |    6    |

If I use echo json_encode():

$json = array();

    while($row = mysqli_fetch_assoc($result)) {             
        $bus = [
        'id' => $row["id"],
        'name' => $row["name"],
        'date' => $row["date"],
        'value' => $row["value"]
        ];

        array_push($json, $bus);    
        }

echo json_encode($json);

the result is:

[

    {
        "Id": "1",
        "Name": "Jeff",
        "Date": "2014-12-01",
        "Value": "5"
    },
    {
        "Id": "1",
        "Name": "Jeff",
        "Date": "2015-12-01",
        "Value": "8"
    },
    {
        "Id": "2",
        "Name": "David",
        "Date": "2014-12-01",
        "Value": "7"
    },
    {
        "Id": "2",
        "Name": "David",
        "Date": "2015-12-01",
        "Value": "6"
    }


]

but, Is it possible to get the json array like this with php ?

[

    {
        "Id": "1",
        "Name": "Jeff",
        "Values":[
               {
                 "2014-12-01": "5",
                 "2015-12-01": "8"
               }
                  ]
    },
    {
        "Id": "2",
        "Name": "David",
        "Values":[
               {
                 "2014-12-01": "7",
                 "2015-12-01": "6"
               }
                  ]
    }

]

You could probably parse the returned data in PHP. I dunno enough of that language to answer for sure. — anon
– anon, Commented Jun 13, 2015 at 6:38
Shouldn't you just change your request with something like group by Name ? — Alexis Paques
– Alexis Paques, Commented Jun 13, 2015 at 20:48

Liuqing Hu · Accepted Answer · 2015-06-13 08:31:28Z

1

I don't know if it's posiable to get data what you want just use sql.

Usually,I will do this job use php

<?php
$json = array();

foreach ($array as $idx => $data) {
    //Id is not exists in json,create new data 
    if( !array_key_exists($data['Id'], $json) ){//Id is unique in db,use Id for index ,you can find it easily
        $json[$data['Id']]=array(
            'Id'  =>$data['Id'],
            'Name'=>$data['Name'],
            'Value'=>array(
                    $data['Date']=>$data['Value']
                )
        );
        continue;
    }

    //Id is exists in json, append value
    $json[$data['Id']]['Value'][$data['Date']] =$data['Value'];
}
$json = json_encode($json);

Wish I could help you

answered Jun 13, 2015 at 8:31

Liuqing Hu

1,9803 gold badges11 silver badges15 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Biswas Khayargoli Over a year ago

This creates key of 'Id' on $json ... how can we do it without the key there?

Liuqing Hu Over a year ago

@BiswasKhayargoli if your data has no primary key, you need build your hash method to generate a unique key,eg concat id and name's value as the hash key, then format result.

Saurabh Over a year ago

Worked fro me.. My data was city & pincode in array... Thnx

Faiz Rasool · Accepted Answer · 2015-06-13 07:10:14Z

0

Try this

<?php

$json = array();

while ($row = mysqli_fetch_assoc($result)) {

    if (is_array($json)) {
        foreach ($json as $key => $val) {
            if ($val['Id'] == $row["id"]) {
                $json[$key]['date'][] = $row["date"];
                continue;
            }
        }
    }
    $json[] = array(
        'id' => $row["id"],
        'name' => $row["name"],
        'date' => array($row["date"]),
        'value' => $row["value"]);
}

echo json_encode($json);

answered Jun 13, 2015 at 7:10

Faiz Rasool

1,3791 gold badge12 silver badges20 bronze badges

4 Comments

Faiz Rasool Over a year ago

It is lame if you flag down and does not give the reason

RepeaterCreeper Over a year ago

Here I'll help you out. Even I don't know why it's flagged down.

Biswas Khayargoli Over a year ago

why is there if (is_array) ?? this will result true everytime isn't it?

Faiz Rasool Over a year ago

@BiswasKhayargoli yeah in this example yes but it is possible previously it is initialised as null or something also in the loop you reassigned the json to some thing else. i just do it as a good practice.

Collectives™ on Stack Overflow

PHP JSON Group by the same value

2 Answers 2

3 Comments

4 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

4 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related