CODEWITHSUNDEEP
phpmysqlcodeigniter
Ciprian
Ciprian

Reputation: 3226

Get data from two tables with join

I m trying to select a list of brands from one table and brand descriptions from another. One brand can have more than one description. What I want to have is something like this:

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

What I have now:

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

Model function:

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();

View: 

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

Upvotes: 1

Views: 64

Answers (3)

Chris Baker
Chris Baker

Reputation: 50592

Order your query by brand, that will group all the brand_desc rows together. So, your query looks like this:

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

Now, when you loop the items, you're going to have several rows repeating the brand name -- each brand description. Instead of thinking of this query as giving you a list of brands, think of it as giving you a list of all the brand descriptions. So when you output that, you'll have to define the grouping.

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

Upvotes: 4

VMai
VMai

Reputation: 10336

you never update your brand information in your foreach-loop. You should build an array of arrays:

$data['brand1'] => array of all descriptions of brand1
$data['brand2'] => array of all descriptions of brand2
[...]

Upvotes: 0

Jack M.
Jack M.

Reputation: 1170

Build your query like this :

$query = "SELECT DISTINCT a.id AS aid, a.brand, b.descriptionColumn FROM brands a
            LEFT JOIN brand_desc b ON a.id = b.brand_id";

Upvotes: -1

Related Questions