CODEWITHSUNDEEP
phpsql
LiveEn
LiveEn

Reputation: 3253

Loop sql result to display rows without duplicating using php

I have 2 tables. product_data contains all the details about the product and product_images which has all the images for each product. I joined both the tables but the problem is that some products have more than 2 images. so the product rows duplicate 

product_name  | description | ucode |  star_rating | product_image

cheese            packed       321        3            1.jpg
cheese            packed       321        3            1.jpg
cheese            packed       321        3            3.jpg
meat              canned       F98        1            meat1.jpg
milk              canned       G43        5            milk1.jpg
milk              canned       G43        5            milk2.jpg
milk              canned       G43        5            milk3.jpg
milk              canned       G43        5            milk4.jpg

When I have a foreach loop

 foreach ($products as $product):
    //display the product details once and all the images for that product
    endforeach;

The output will be like,

product 1          1.jpg
                   2.jpg
                   3.jpg
product 2          meat1.jpg
product 3          milk1.jpg
                   milk2.jpg
                   milk3.jpg
                   milk4.jpg

I want to list all the products along with the images. How can I display the product details only 1 time and all the images for that product and move to the next product? There's is a maximum of 6 images for a product

The SQL query - 3 tables have been joined

SELECT *
FROM product_data p
LEFT JOIN product_img_map im ON im.product_id = p.product_id
LEFT JOIN product_images i ON i.photo_id = im.photo_id

Upvotes: 0

Views: 187

Answers (2)

m79lkm
m79lkm

Reputation: 3070

You can put it all in an array:

foreach ($products as $product) {
    ....
    $product_array[$product_name]['images'][] = $product_image;
    ....
}

then you will have more control over your output by looping through the product array you created.

Upvotes: 1

Mihai Iorga
Mihai Iorga

Reputation: 39704

You can use GROUP_CONCAT:

SELECT `product_data`.*,
GROUP_CONCAT(`product_data`.`product_image`) as Pictures
FROM `product_data`
GROUP BY `product_data`.`product_name`

You will get a new row Pictures containing all images separated by comma.

Upvotes: 0

Related Questions