How to select the first # lines from album and album entries

Question

I am currently developing a photo album page for the website I'm working on, and my idea was to create an overview of all photo albums, showing the 5 most recently added images. Two MySQL tables are used in the photo album, one of which contains album data (for example, album name, album identifier) ​​and one that contains images (I store them in the database as drops) along with the corresponding album identifier.

However, I sofar could not find a query that allows me to get all the album data and the identifier for the last 5 pictures corresponding to the album. Hope you can help me a little.

The tables look like this:

Table: photo albums

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

Table: Photos

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

, . .

- ? .

Answer 1

, , - "", ​​ , ​​ , , , ​​, , . photo_creation_date ( , .)

, , //, , . .. .

, . . // .

Answer 2

Try

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

Answer 3

, pictures, - :

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

5 , , . , .

Answer 4

, , , (qv http://dev.mysql.com/doc/refman/5.0/en/cursors.html), ( ), , , PHP , , .

.

Answer 5

, , , . , . , ​​ date_inserted .

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

, , . php 5 .

, , , , , , SQL-.

Answer 6

, - . . x . , , , . 0,5 . ( 1, , 1 ).

. , .: -)

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