MySQL - select only one (random) image of many

Question

I got stucked with one mysql query and i hope you guys can crack it... Ok, so my query selects all products witch are featured and active and displays them on front page... But problems appears when product has more then one image. This products are duplicated then on front page.

As you can see in results, last two products are the same, but duplicated because he has two pictures assigned on it. I whould like to know, what is the right way to retrive only one random picture of this product

Tnx! ;)

Here is my MySql query:

SELECT prod.id_product, des.name, des.description,
                      price.price_net, price.discount_price, 
                      prod.url, prod.active, prodpic.id_product, prodpic.id_image,
                      img.img_path, thumb.thumb_path
                FROM products AS prod
                JOIN descriptions AS des
                ON prod.id_description = des.id_description
                JOIN prices AS price
                ON prod.id_price = price.id_price
                JOIN products_pictures AS prodpic
                ON prod.id_product = prodpic.id_product
                JOIN product_images AS img
                ON prodpic.id_image = img.id_image
                JOIN product_thumb AS thumb
                ON img.id_product_thumb = thumb.id_product_thumb
               WHERE prod.featured = 1
               AND prod.active = 1

And the result is:

      0 => 
    object(stdClass)[21]
      public 'id_product' => string '1285' (length=4)
      public 'name' => string '1 item' (length=23)
      public 'description' => string 'Firs item DESC.' (length=109)
      public 'price_net' => string '32.786890' (length=9)
      public 'discount_price' => null
      public 'url' => string 'tunika-crazy-dots-white' (length=23)
      public 'active' => string '1' (length=1)
      public 'id_image' => string '1285' (length=4)
      public 'img_path' => string 'images/webshop/products/Tunika_Crazy_Dot_527fb1f252ccd.jpg' (length=58)
      public 'thumb_path' => string 'images/webshop/products/resized/Tunika_Crazy_Dot_527fb1f252ccd.jpg' (length=66)
  1 => 
    object(stdClass)[22]
      public 'id_product' => string '1300' (length=4)
      public 'name' => string 'Trol face 1' (length=11)
      public 'description' => string 'Troll face 1... Troll face 1... Troll face 1... Troll face 1... Troll face 1... Troll face 1... Troll face 1... ' (length=112)
      public 'price_net' => string '81.967213' (length=9)
      public 'discount_price' => string '50' (length=2)
      public 'url' => string 'Trol-face-1' (length=11)
      public 'active' => string '1' (length=1)
      public 'id_image' => string '1344' (length=4)
      public 'img_path' => string 'images/webshop/products/Trollface1.jpg' (length=38)
      public 'thumb_path' => string 'images/webshop/products/resized/Trollface1_thumb.jpg' (length=52)
  2 => 
    object(stdClass)[23]
      public 'id_product' => string '1300' (length=4)
      public 'name' => string 'Trol face 1' (length=11)
      public 'description' => string 'Troll face 1... Troll face 1... Troll face 1... Troll face 1... Troll face 1... Troll face 1... Troll face 1... ' (length=112)
      public 'price_net' => string '81.967213' (length=9)
      public 'discount_price' => string '50' (length=2)
      public 'url' => string 'Trol-face-1' (length=11)
      public 'active' => string '1' (length=1)
      public 'id_image' => string '1341' (length=4)
      public 'img_path' => string 'images/webshop/products/Trollface.jpg' (length=37)
      public 'thumb_path' => string 'images/webshop/products/resized/Trollface_thumb.jpg' (length=51)

score 3 · Accepted Answer · answered Dec 14 '13 at 13:39

3

You can try

ORDER BY RAND()

and

GROUP BY id_image

answered Dec 14 '13 at 13:39

Supun Silva

580
5
21

Don't know if you have understand question right way... I want mysql to chose beatween $product[1] OR $product[2] and outputs only one, since this is the same product with two images... – Valor_ Dec 14 '13 at 13:47

score 1 · Answer 2 · answered Dec 15 '13 at 14:35

As Supun Silva mentiond answer is pretty easy :)

the query is:

SELECT prod.id_product, des.name, des.description,
                      price.price_net, price.discount_price, 
                      prod.url, prod.active, prodpic.id_product, prodpic.id_image,
                      img.img_path, thumb.thumb_path
                FROM products AS prod
                JOIN descriptions AS des
                ON prod.id_description = des.id_description
                JOIN prices AS price
                ON prod.id_price = price.id_price
                JOIN products_pictures AS prodpic
                ON prod.id_product = prodpic.id_product
                JOIN product_images AS img
                ON prodpic.id_image = img.id_image
                JOIN product_thumb AS thumb
                ON img.id_product_thumb = thumb.id_product_thumb
               WHERE prod.featured = 1
               AND prod.active = 1
               GROUP BY prod.id_product

MySQL - select only one (random) image of many

2 Answers2