How to select first # rows from album and album details

I'm currently developing a photoalbum page for a website I'm working on, and my idea was to create an overview of all photoalbums, showing the 5 most recently added pictures. The photoalbum uses two MySQL tables, one containing the album details (eg: album name, album id) and one containing the pictures (I store them into the database as blobs) along with the corresponding album id.

However, I sofar haven't been able to figure out a query which allows me to fetch all of the album details and the id's for the 5 most recent pictures corresponding to the album. I hope you can help me out a bit.

The tables look like this:

Table: photoalbums

id | album_name | album_created
-------------------------------------
1  | testalbum  | 2010-11-07 19:33:20
2  | some more  | 2010-11-15 18:48:29

Table: pictures

id | file   | thumbnail | name  | album_id
------------------------------------------
1  | binary | binary    | test1 | 1
2  | binary | binary    | test2 | 1
3  | binary | binary    | test3 | 2
4  | binary | binary    | test4 | 2
5  | binary | binary    | test5 | 1

It looks 开发者_JAVA技巧to me like I'm going to need a subquery, or performing a separate query per album. I would rather avoid using multiple queries..

Can anybody help me finding the right way to do this? Thanks in advance.

You may want to consider creating a separate, de-normalized, 'history' table which is inserted to at the same time any new photo is inserted, containing the album id, the date the photo was inserted, and any columns you may want for displaying results in this history table. You can then select off this table based on the photo_creation_date (I don't think album creation date will help you here.)

This has the added benefit of separating any queries you run based on the photo creation date from other insert/update/read operations being performed against the photo and albums that do not have anything to do with the photo creation date. I.E. saving you an extra index on the photoalbum and pictures tables.

This does cost you in extra storage and insert operations, but it results in faster read speeds. You also will have to be careful in your maintenance of the history table. Deletes/Updates/Inserts of the photo and photoalbum tables will need to update the history table as well.

Try

SELECT * FROM pictures LEFT JOIN photoalbums ON (photoalbums.id=pictures.id) ORDER BY album_created DESC
 LIMIT 5

If it's information for just one particular album, and if you add a date modified column in the pictures table, you could do something like this:

SELECT a.*, p.*
FROM photoalbums AS a
LEFT JOIN pictures AS p ON a.id = p.album_id
WHERE a.id = 1  -- (insert number here)
ORDER BY p.modified_date
LIMIT 5

For a list of the 5 most recent picture modifications for all albums, then yes there are ways of crafting a query to return that information. Let me know and I'll do a query like that for you, as well.

While I think you might be able to do what you're asking using cursors (q.v. http://dev.mysql.com/doc/refman/5.0/en/cursors.html ), or temporary (or even permanent, as Macy suggested) tables, you have to ask yourself if you really have a good reason to go to those lengths, when four or five lines of PHP will do what you want, at the cost of multiple smaller, simpler queries.

Thou shalt remember KISS and keep it holy in thy heart.

This query will get you all the album details and all the pictures sorted by ID descending in a comma-separated field. I can only assume the highest picture.id is the most recent, since you don't have a date_inserted column for the pictures.

SELECT a.*, GROUP_CONCAT(p.id ORDER BY p.id DESC) as picID FROM photoalbums AS a INNER JOIN pictures AS p ON p.album_id=a.id GROUP BY p.album_id

As I said, this will get all of the pictures in decreasing ID order. With php it should be a simple matter of exploding on the comma and slicing off the first 5 IDs.

I must thank you for this fun little project, but I've got to move on and unfortunately didn't get it exactly where you want it just using an sql query.

To do this in a single pass you need to relate in some way with the parent field as the query is running. To do this you need to use a variable. Here, I have used the variable x to hold the current count as the query runs. As each record is selected, it decides if it is currently dealing with a record that belongs to the current parent. If it does it simply adds 0.5 to the running total. (I originally tried 1, but it seems that 1 is added twice).

Here is the final query. Let me know if it helps. :-)

SELECT album_id, id, @x := if(@album_id = album_id, @x + 0.5, 1) cnt, @album_id := album_id FROM pictures GROUP BY album_id, id, cnt HAVING cnt <= 5;