php - 通过连接从两个表中获取数据

Question

我正在尝试从一张表中选择品牌列表，并从另一张表中选择品牌描述。一个品牌可以有不止一种描述。我想要的是这样的:

Brand1
-brand1 description 1
-brand1 description 2
...etc

我现在拥有的:

Brand1
-brand1 description1
-brand1 description2
Brand1
-brand1 description1
-brand1 description2

模型函数:

function get_brand_desc() {
    $query = "SELECT a.id AS aid, a.brand, b.* FROM brands a
                LEFT JOIN brand_desc b ON a.id = b.brand_id";
    $q = $this->db->query($query);

    if($q->num_rows() > 0) 
    {
        foreach($q->result() as $descs)
        {
            $data[] = $descs;
        }
        return $data;
    }else{
        return false;
    }   
}

Controller :

    $admin_data['descs'] = $this->admin_model->get_brand_desc();

查看:

<?php
        echo '<ul>';
            foreach($descs as $desc) {
                echo '<li>';
                echo '<p>'.$desc->brand.'</p>';
                echo '<p>'.$desc->description.'</p>';
                echo '</li>';
            }
        echo '</ul>';
    ?>

Answer 1

按品牌排序您的查询，这会将所有 brand_desc 行组合在一起。因此，您的查询如下所示:

SELECT
    a.id AS aid,
    a.brand,
    b.* 
FROM 
    brands a
LEFT JOIN 
    brand_desc b ON 
        a.id = b.brand_id
ORDER BY 
    a.brand

现在，当您循环显示项目时，您将有几行重复品牌名称 -- 每个品牌描述。与其将此查询视为为您提供品牌列表，不如将其视为为您提供所有品牌描述的列表。因此，当您输出它时，您必须定义分组。

    echo '<ul>';
        $currentBrand = false;
        foreach($descs as $desc) {
            if ($currentBrand != $desc->brand) {
                if ($currentBrand !== false)
                    echo '</li>'; // if there was a brand LI open, close it
                echo '<li>'; // open a new LI for this new brand
                echo '<p>'.$desc->brand.'</p>';
                $currentBrand  = $desc->brand;
            }
            echo '<p>'.$desc->description.'</p>';
        }
    echo '</li>'; // closing out the brand that is left hanging open at the end
    echo '</ul>';