ORDER BY p.display_order and i.img show thumbnail with specific image order

Question

I want to get product thumbnails on a product category page, each product 1st thumbnail image needs to be shown. right now the last image is showing. here is the screen shot for my db structure

EDIT:

right now the table format as screenshot, there are 4 images with order 1,2,3,4 i want to get the image with img_order 1.

when i am running sql query

 SELECT i.img_order,p.*, i.product_id, i.dirName, i.img FROM products p inner JOIN product_images i ON i.product_id = p.id WHERE p.status = 1 and i.product_id=329 AND i.type = 'Static' GROUP BY i.product_id ORDER BY i.img_order asc

it's giving wrong result see screenshot

but i want to get the image with img_order 1.

screen shot is not clear enough, column name is not displayed. — Mobasher Fasihy, May 12 '16 at 06:28
http://stackoverflow.com/questions/14375099/select-query-return-1-row-from-each-group this may help you. — DD77, May 12 '16 at 06:41
We can't see the columns. Provide proper DDLs and a desired result instead — Strawberry, May 12 '16 at 06:56
Or see something like http://stackoverflow.com/questions/17882073/mysql-select-max-date-inside-a-join-statement/17882188#17882188 — Strawberry, May 12 '16 at 07:00

score 1 · Answer 1 · answered May 14 '16 at 05:28

1

Try Something like this:

SELECT pi.img_order,p.*, pi.product_id, pi.dirName, pi.img 
FROM product as p
LEFT JOIN product_image as pi ON pi.product_id = p.id
JOIN
 ( SELECT id,product_id, MIN(img_order) minVal
   FROM product_image GROUP BY product_id
 ) i
ON pi.img_order = i.minVal AND i.id = pi.id;

fiddle is here

answered May 14 '16 at 05:28

Mobasher Fasihy

1,021
2
9
17

@Umer bin Siddique check now! – Mobasher Fasihy May 14 '16 at 05:29

score 0 · Accepted Answer · edited Oct 05 '22 at 00:11

0

I found the solution with the following sql query:

SELECT i.img_order,p.*, i.product_id, i.dirName, i.img FROM products p inner JOIN product_images i ON i.product_id = p.id WHERE p.status = 1 and i.product_id=329 AND i.type = 'Static' GROUP BY i.product_id ORDER BY i.img_order asc

edited Oct 05 '22 at 00:11

miken32

42,008
16
111
154

answered May 13 '16 at 10:57

Umer bin Siddique

460
1
4
13

score -1 · Answer 3 · answered May 12 '16 at 06:28

-1

Have you tried something like this.

Check all the rows

 $query = $this->db->query("YOUR QUERY");

 foreach ($query->result_array() as $row)
 {
    echo $row['title'];
    echo $row['name'];
    echo $row['body'];
  }

answered May 12 '16 at 06:28

Waqas_aamer

220
1
3
18

You can store the values in array in the foreach loop if you want to return it. – Waqas_aamer May 12 '16 at 06:31
i have tried this but some images are showing there 1st and some showing last – Umer bin Siddique May 12 '16 at 06:48
can you a little explain – Waqas_aamer May 12 '16 at 07:25

ORDER BY p.display_order and i.img show thumbnail with specific image order

3 Answers3