Iterate and group array of objects in PHP

Question

I have the following MySQL table:

+----+----------+--------+------+
| id | brand    | model  | year |
+----+----------+--------+------+
| 1  | audi     | tt     | 2014 |
+----+----------+--------+------+
| 2  | audi     | a4     | 2015 |
+----+----------+--------+------+
| 3  | mercedes | aclass | 2014 |
+----+----------+--------+------+
| 4  | mercedes | cclass | 2017 |
+----+----------+--------+------+

I am using MYSQL in PHP and writing a query to get this data:

$carsQuery = SELECT * FROM cars_table ORDER BY model ASC;

After the query is executed I am using PHP to try an achieve the following JSON Object output:

{
    "car_models": [
        {
            "id": "audi",
            "name": "Audi",
            "cars": [
                {
                    "id": "1",
                    "model": "tt",
                    "year": "2014",
                    "brand": "audi"
                    },
                {
                    "id": "2",
                    "model": "a4",
                    "year": "2015",
                    "brand": "audi"
                }
            ]
        },
        {
            "id": "mercedes",
            "name": "Mercedes",
            "cars": [
                {
                    "id": "3",
                    "model": "aclass",
                    "year": "2014",
                    "brand": "mercedes"
                    },
                    {
                    "id": "4",
                    "model": "cclass",
                    "year": "2017",
                    "brand": "mercedes"
                    }
            ]
        }
    ]
}

The following snippet is the PHP code I am using to achieve this:

foreach($carsQuery as $carQuery){ 
        $cars[$carQuery->brand][] = $carQuery;
    }

return array( 
        'car_models' => $carQuery
        );

The result is being grouped however I cannot get to the point of adding the other fields in order to achieve the JSON object I want.

Did you choose that JSON structure or is it an external constraint? If the former then you might reconsider the structure. If the latter then you should align the sorting of the query output with the data hierarchy in your JSON. — symcbean
– symcbean, Commented Mar 16, 2018 at 18:30

Don't Panic · Accepted Answer · 2018-03-16 18:34:52Z

2

Adding the other fields is mostly just a matter of defining them in the array you're building.

foreach ($carsQuery as $carQuery) {
    $cars[$carQuery->brand]['id'] = $carQuery->brand;
    $cars[$carQuery->brand]['name'] = ucfirst($carQuery->brand);
    $cars[$carQuery->brand]['cars'][] = $carQuery;
}

'id' and 'name' will be overwritten with the same value for multiple records of the same brand.

You may need to use some other approach than ucfirst to format the 'name' field, as some values may have different capitalization.

You should also use array_values to create your final array, in order to replace the string keys with numeric ones.

return array(
    'car_models' => array_values($cars)
);

That will ensure that car_models is an array of objects in your final JSON, rather than an object.

edited Mar 16, 2018 at 18:34

answered Mar 16, 2018 at 18:30

Don't Panic

41.9k11 gold badges70 silver badges87 bronze badges

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

4 Comments

Jojo Over a year ago

Many thanks for your reply, that worked perfectly fine. The only issue, the JSON output is not sorted how I want. How can I sort the objects with the brand name?

Don't Panic Over a year ago

Add it to the order by clause in your query. ...ORDER BY brand, model or actually, if you want it like your example, brand, id

Jojo Over a year ago

I already order by brand, however the model in the car s array are not alphabetical

Don't Panic Over a year ago

@Jojo I noticed that after I commented before. I edited the previous comment. With your example data, you should be able to get your example output with either ORDER BY brand, id or ORDER BY brand, year.

Collectives™ on Stack Overflow

Iterate and group array of objects in PHP

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related