显示所有照片库相册的专辑封面

Question

I have a photo gallery where I allow the user to create individual photo albums. I can display the name of each album like this.

$album = DB::getInstance()->query("SELECT `album_id`,`album_date`,`album_title` FROM `albums` ORDER BY `album_date` DESC");

What I'm trying to do is add a cover photo for each album, but can't seem to get my query correct. This is what I'm trying.

$album = DB::getInstance()->query("SELECT
albums.album_id,
albums.created,
albums.album_date,
albums.album_title,
gallery.file_name,
gallery.id,
gallery.album_id
FROM
albums
INNER JOIN gallery ON gallery.album_id = albums.album_id ORDER BY gallery.id DESC ;");

This is the result I get:

This is the result I'm trying to achieve:

I want each album name displayed with the last photo from each album as the cover photo.

If I add a LIMIT of 1 to my query, only one album is shown.

Here are my tables that are Joined together by a Foreign Key.

Albums Table:

Gallery Table:

Any solution to get this working would be much appreciated.

Answer 1

You can select the most recent image in each album using the following query:

SELECT album_id, MAX(id) id
FROM gallery
GROUP BY album_id

Alternatively, you can select a random image in each album like this:

SELECT g.album_id, (
    SELECT id FROM gallery g2
    WHERE g2.album_id = g.album_id
    ORDER BY RAND()
    LIMIT 1
) id
FROM gallery g
GROUP BY g.album_id

Either of the above queries could be plugged into a complete query:

SELECT a.album_id, a.created, a.album_date, a.album_title,
       g.file_name, g.id, g.album_id
FROM albums a
INNER JOIN (
    # above query goes here
) alb_img USING (album_id)
INNER JOIN gallery g ON g.id = alb_img.id
ORDER BY a.album_date DESC;

If you'd like to select empty albums as well as those containing one or more images change the INNER JOINs to LEFT JOINs.