PHP层次结构/图库系统
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
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.
If I understand your question, you want the path from a leaf (e.g. "Day 2") to the root of the tree (Album Data).
If that's right, the answer to this question should do it: mysql + php retrieve leaf children with path
Consider using a MPTT table for albums, then you can name them whatever you want and have unlimited/unrestricted nesting. You can then then add pictures to an album by id. You can administer such tables using drag and drop interfaces. They are geared for fast reading, slower updating.
As for data,
albums table: album_id (mediumint/unsigned auto_increment), lft (mediumint/unsigned), rgt (mediumint/unsigned) album_name (tingtext?)
items table: item_id (mediumint/unsigned auto_increment) item_album_id (mediumint/unsigned) item_name (varchar)
Mediumint because you may go beyond 65 thousand items/albums but seem unlikely to go above 16 million.
EDIT:
If you use good tools, you can reduce the learning curve. See http://mjsarfatti.com/sandbox/nestedSortable/ and https://github.com/mjsarfatti/nestedSortable The values you need to store in the database are provided for you by the toArray() method. You can use standard jQuery UI sortable() events like update to change hidden inputs.
Selecting trees, or parts thereof, is basically ordering by the left value. For subtrees, you also limit items to those that have a left that is between the parent's left and right values. You can also limit by (relative) depth like depth <= parent_depth + number_of_levels_down.
To display the flat list you get from the db as a hierarchy, you use a stack to note the depth of the previous item. As you iterate the items, you use it to create opening html tags when it goes deeper and closing tags when the depth decreases.