construct a complex JSON object from SQL result in PHP

Question

I have a db with many tables. One table represents products, one other table represents categories. One product can belong to many categories. So when I request my db to display the products with their categories I of course retrieve many rows as many categories each product has. For instance the result would be

product_name|category      |city
Test1       |cinema        |paris
Test1       |entertainment |paris
Test1       |Other         |paris
Test2       |Food          |new york
Test2       |Restaurant    |new york
Test2       |Night         |new york

What I am trying to do is to create a JSON object using a PHP script for each product name which looks like this :

    [
        {
            "product_name": "Test1",
            "categorie": [
                "cinema",
                "entertainment",
                "Other"
            ],
            "city": "paris"
        },
        {
            "product_name": "Test2",
            "categorie": [
                "Food",
                "Restaurant",
                "Night"
            ],
            "city": "new york"
        }
    ]

When I tried to use json_encode but unsuccessfully I got duplicate rows. thanks for your help

First group by rows. It's easy to do: use foreach and just check if a current product_name equals to the previous one. If it is - put its categorie to the project one. Otherwise - create a new entry — zerkms
– zerkms, Commented Oct 18, 2014 at 23:59

ngleich · Accepted Answer · 2014-10-19 00:37:55Z

2

You will need to reorder your data before the json_encode

If $rows is the response of your db, you have to go throw

$data = [];
foreach($rows as $row) {
    $name = $row['product_name'];
    if(!isset($data[$name])) {
        $data[$name] = [
            'product_name' => $name,
            'city'=>$row['city'],
            'categories'=>[]
        ];
    }
    $data[$name]['categories'][] = $row['category'];
}
json_encode(array_values($data)); //this is what you want

Thats it! Maybe you need while ($row=mysqli_fetch_row($result)) instead of foreach, depending on your sql query builder you use. But the idea is the same

edited Oct 19, 2014 at 0:37

answered Oct 19, 2014 at 0:06

ngleich

2861 silver badge5 bronze badges

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

4 Comments

skunk a Over a year ago

I use while($row = $sth->fetch(PDO::FETCH_ASSOC)). However I got an error on the line $data[$name]['categories'] = $row['category']

ngleich Over a year ago

Yes, there was a bug. It must have the [] for adding the categories. I also added array_values to the encode, this way it transformit as an array and not as object

skunk a Over a year ago

thanks, can you explain please what the last data does? My understanding is : 1- you create a array, 2 for each row you set the var name with the name of the product and check if this value exists in the array; if not exist you create a new array with the values; if the value exists you create a new array (now data has 2 dimensions) with the value categories. what i don'y understand is how you append the category value?

ngleich Over a year ago

If you have an array variable $var = []; You can append elements to it, by doing $var[] = 'new value'; For example if you have: $var = [];$var[] = 1;$var[] = 2;$var[] 3; At this point $var is an array with [1,2,3] values. php.net/manual/en/function.array-push.php

Collectives™ on Stack Overflow

construct a complex JSON object from SQL result in PHP

1 Answer 1

4 Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Related