如何在php中对左联接查询返回的数组数据进行分组? [英] How to group array data returned by left join query in php?

问题描述

我有以下两个具有以下值的表:

I have following 2 tables with following values:

tbl_brand

id       名称

id      name

1            Apple

1       Apple

2       <b;三星

2       Samsung

tbl_products

id      brand_id        p_name >

id      brand_id      p_name

1          1    nbsp; b&bsp; b&bsp; n ;  移动

1             1              Mobile

2          1    nbsp; b&bsp; b&bsp; b&bsp; ;   Earpods

2             1              Earpods

3           2    nbsp; b&bsp; b&bsp; ;  移动

3             2              Mobile

在这里，当我使用左联接查询i.e.

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
            )

我想根据brand_id键对数据进行分组.我该怎么办?

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

推荐答案

虽然您可以在单个循环中生成所需的数组，但我宁愿执行两个查询.首先，将所有品牌放入一个阵列，然后向每个品牌添加一个空的产品阵列.然后获取所有产品并将其分配给相关品牌.

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;
}

这篇关于如何在php中对左联接查询返回的数组数据进行分组?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！