查询以显示包含所有附加缩略图的帖子

问题描述：

Problem: I need to display a list of posts, each with their attached thumbnails. Thumbnails are in the filesystem, I just need the path that was stored in the mysql db. Si I have 2 db tables: one for posts and one for images_attachments.

I'm not sure what is the appropriate format I should retrieve from the db to achieve what I need. Right now I have this:

SELECT posts.id, posts.post_title, images_attachments.image_name
FROM posts 
INNER JOIN images_attachments ON posts.id = image_post_post_id

This produce rows with post_id, post_title and the corresponding joined images_attachments.image_name which is the path.

Is this how I should do it ? Problem is that I get as much rows per post as it has images. This will involve more logic on the template to show the post title, and under it all the thumbs.

Second problem is that it will ignore posts with no attached thumbnails.

Please advise if i'm in the wrong direction, and if it is the case, how to format the query.

(this is NOT wordpress, just raw mysql and php templates)

问题：我需要显示一个帖子列表，每个帖子都带有附加的缩略图。 Thumbnails在文件系统中，我只需要存储在mysql数据库中的路径。我有2个数据库表：一个用于posts code>，另一个用于 images_attachments code>。 p>

我不确定我应该从db中检索什么样的格式来实现我的需要。现在我有这个： p>


  SELECT posts  .id，posts.post_title，images_attachments.image_name 
FROM发布
INNER JOIN images_attachments ON posts.id = image_post_post_id 
  code>  pre> 
 
 这会产生 post_id行 代码>， post_title  code>和相应的联接 images_attachments.image_name  code>这是路径。 p> 
 
 
这是我应该怎么做的？ 问题是我每个帖子的行数与图像数量相同。 这将在模板上显示更多逻辑以显示帖子标题，并在其下面显示所有拇指。 p> 
 
 

第二个问题是它将忽略没有附加缩略图的帖子。 p> \  n 
 
请告知我是否方向错误，如果是这样的话，如何格式化查询。 p> 
 
 
（这不是wordpress，只是原始的mysql 和php模板） p> 
  div>

答

You have many-to-one (many attachments to one post), therefore you are probably better just doing a normal select, looping through the rows, and querying for attachments.

<?php

foreach ($posts as $post) {
    $attachments = // query other table
    foreach($attachments as $attachment) {
        // do stuff with attachment
    }
}

The way you are doing it, you would need to create dynamic additional columns based on the number of attachments, and that's a headache. This also ensures your posts with no attachments will also be fetched.

查询以显示包含所有附加缩略图的帖子

相关推荐