PHP hierarchy / gallery system

Question

I'm in the process of writing my own simple gallery script in OOP (still learning) PHP.

I'm trying to pull the albums from the database in the most efficient way as the current gallery script (Gallery2) is extremely resource heavy, this will be replacing it. The current gallery has roughly 35,000 items and roughly 100 albums.

_{(source: iforce.co.nz)}

As you can see with the above image I require unlimited sub albums (in reality probably only 5-6 max).

Database layout is pretty simple as all it needs to do is display the images.

albums table album_id (int/auto_increment) album_parent (int) album_name (tingtext)

items table item_id (int/auto_increment) item_album (int) item_name (int)

Few queries I've came up with have just been based off using a WHERE statement and going off the parent album ID eg..

parent ID 0 = top level parent ID 3 would grab all albums that are parented to that ID.

The problem I have is that I would like to build the breadcrumbs and URLs using the albums (album_1/subalbum_1/etc) names and subalbums, using the current query I cannot pull them all at once.

Unless I'm overlooking something the URL would need to be like script.php?album_id=1&album_id=3&album_id=6 that would grab them as if 1 was top level, 3 was sub, and 6 was a sub of 3.

I have read a lot of articles regarding a hierarchy setup but most are directed towards tree hierarchy layout, I don't need to display all album's sub albums at once for every album more towards a single path hierarchy.

Thanks

edit I thought I should point out that the album names can be named anything, the dates were only used for naming not actual timestamps etc. Sorry

Answer 1

Retrieve path as ianhales suggested but store it into database table, and retrieve from there to display.

So store current node path into table, and only when moving, renaming nodes will need updating path.

This way will be more efficient reads.