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

Question

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)

Answer 1

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.