How to group array data returned by left join query in php?

Question

I have following 2 tables with following values:

---

tbl_brand

id      name

1       Apple

2       Samsung

---

tbl_products

id      brand_id      p_name

1             1              Mobile

2             1              Earpods

3             2              Mobile

---

Here when I use left join query i.e.

SELECT 'b'.'id' as 'brand_id', 'b'.'name' as 'brand_name', 'p'.'p_name' as 'product_name' FROM 'tbl_brand' 'b' LEFT JOIN 'tbl_products' 'p' ON 'p'.'brand_id' = 'b'.'id'

and print the result, I get the following array:

Array
    (
        [0] => stdClass Object
            (
                [brand_id] => 1
                [brand_name] => Apple
                [product_name] => Mobile
            )
        [1] => stdClass Object
            (
                [brand_id] => 1
                [brand_name] => Apple
                [product_name] => Earpods
            )
        [2] => stdClass Object
            (
                [brand_id] => 2
                [brand_name] => Samsung
                [product_name] => Mobile
            )

Everything is working fine. But the result I'm looking for is something like this:

Array
    (
        [0] => stdClass Object
            (
                [brand_id] => 1
                [brand_name] => Apple
                [product_name] => stdClass Object
                               (
                                   [0] => Mobile
                                   [1] => Earpods
                               )
            )
        [1] => stdClass Object
            (
                [brand_id] => 2
                [brand_name] => Samsung
                [product_name] => Mobile
            )

I want to group the data according to the brand_id key. How can I do that ?

Answer 1

mysql returns rows, so if you want a multidimensional array you have to construct it yourself. A loop along these lines will do it:

$array=array();
foreach ($result as $row) {
    $array[$row->brandid]['brandid'] = $row->brandid;
    $array[$row->brandid]['brand_name'] = $row->brand_name;
    $array[$row->brandid]['products'][] = $row->product;
    }

Answer 2

While you can generate the desired array in a single loop, I would rather execute two queries. First fetch all brands into an array and add an empty products array to every brand. Then fetch all products and assign them to the related brand.

Since I don't know what DB library you are using, here some kind of pseudo code:

$data = [];

$brandResult = $db->query("SELECT id, name FROM tbl_brand");
while ($row = $brandResult->fetchObject()) {
    $row->product_names = [];
    $data[$row->id] = $row;
}

$productResult = $db->query("SELECT id, brand_id, p_name FROM tbl_products");
while ($row = $productResult->fetchObject()) {
    $data[$row->brand_id][$row->id] = $row->p_name;
}