在一个查询中选择所有项目和相关项目

Question

I'm using latest codeigniter

I'm trying to get all products from the db and all related items for each product. Haven't coded for a while so I got stuck on output..

Here's my model:

   public function collections() {

        $this->db->select('*');
        $this->db->from('products');
        $this->db->join('product_images', 'product_images.from_set = products.img_set_ref');
        $this->db->order_by('products.id');

        $q = $this->db->get();         

        if ($q->num_rows() > 0) {       
            return $q->result();        
        }
        return false;               
    }

Controller:

    public function products() {   
    $this->load->model('Frontend'); 
    $page_data['results'] = $this->Frontend->collections();       
    $this->load->view('collections', $page_data);           
}

This is the output I'm trying to get (example of two products):

            <div class="pr"> //product 1
            <a href="img/pr_1.jpg" rel="gallery01"><img src="img/pr.jpg" alt="">
            </a>
            <a href="img/pr_2.jpg" rel="gallery01"><img src="img/pr.jpg" alt="">
            </a>
        </div>

        <div class="pr"> //product two
            <a href="img/pr_12.jpg" class="fancybox" rel="gallery02"><img src="img/pr.jpg" alt="">
            </a>
            <a href="img/pr_22.jpg" class="fancybox" rel="gallery02"><img src="img/pr.jpg" alt="">
            </a>
        </div>

I can't figure out how to properly echo products in foreach loop in the view so that they were in structure like above. How to achieve that?

Answer 1

If I would need a fast'n'dirty way, I'd go with building a temporary array of images per product

$groupedProducts = array();
foreach ($products as $product) {
     if (!array_key_exists($product['id'], $groupedProducts) {
         $groupedProducts[$product['id']] = $product;
         $groupedProducts[$product['id']]['images'] = array();
     }
     $groupedProducts[$product['id']]['images'][] = $product['product_images.url'];
}

Then iterate

foreach ($groupedProducts as $product) {
    // output some description, title, price, etc.
    foreach ($product['images'] as $imageUrl) {
        echo '<img src="'.$imageUrl.'">';
    }
}