Loop sql result to display rows without duplicating using php

Question

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

Can you show us the SQL query?

Hydra IO
– Hydra IO

2013-03-22 17:01:09 +00:00
Commented Mar 22, 2013 at 17:01 — Hydra IO
– Hydra IO, Commented Mar 22, 2013 at 17:01
@HydraIO i have added the sql code..

LiveEn
– LiveEn

2013-03-22 17:23:58 +00:00
Commented Mar 22, 2013 at 17:23 — LiveEn
– LiveEn, Commented Mar 22, 2013 at 17:23

m79lkm · Accepted Answer · 2013-03-22 17:08:50Z

1

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.

answered Mar 22, 2013 at 17:08

m79lkm

3,0801 gold badge24 silver badges22 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Mihai Iorga · Accepted Answer · 2013-03-22 17:07:30Z

0

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.

answered Mar 22, 2013 at 17:07

Mihai Iorga

39.8k17 gold badges109 silver badges109 bronze badges

1 Comment

LiveEn Over a year ago

actually there 3 tables joined basically 1 table is used to map the product id with image id. i have added the sql code. Is it possible to use the concat with my sql code?

Collectives™ on Stack Overflow

Loop sql result to display rows without duplicating using php

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related