codeigniter ORDER BY p.display_order和i.img显示具有特定图像顺序的缩略图[重复]

4ktjp1zp 于 2022-12-06 发布在其他

关注(0)|答案(3)|浏览(91)

This question already has answers here:

MYSQL Select MAX Date inside a join statement (2 answers)
SELECT query return 1 row from each group (3 answers)
Closed 2 months ago.
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.

codeigniter

来源：https://stackoverflow.com/questions/37178738/order-by-p-display-order-and-i-img-show-thumbnail-with-specific-image-order

3条答案

按热度按时间

soat7uwm1#

尝试类似这样的操作：

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;

小提琴来了

赞(0）回复(0）举报 2022-12-06

gstyhher2#

我用下面的sql查询找到了解决方案：

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

赞(0）回复(0）举报 2022-12-06

0wi1tuuw3#

你有没有试过这样的东西。

选中所有行

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

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

赞(0）回复(0）举报 2022-12-06

我来回答

codeigniter ORDER BY p.display_order和i.img显示具有特定图像顺序的缩略图[重复]

3条答案

相关问题

热门标签

最新问答