从PHP中的SQL结果构造一个复杂的JSON对象

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

Answer 1

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