here is what I am working on, its a photo gallery and this would be an example structre:



[root] (folder id:1)
.......|
.......---[locations] (folder id:2)
.....................|
.....................---[North america] (folder id:3)
...................................|
...................................---[United States] (folder id:4)
.................................................. .|
.................................................. .---[New York] (folder id:5)
.................................................. ...............|
.................................................. ...............---[New York City] (slide id:6)
.................................................. ...................................|
.................................................. ...................................---• [img_1.jpg] (image id:7)
.................................................. ...................................|
.................................................. ...................................---• [img_2.jpg] (image id:8)
.................................................. ...................................|
.................................................. ...................................---• [img_3.jpg] (image id:9)



categories table list folders, slides and images each with a unique id and has a parent id. Folders can hold other folders or slides. Slides can only hold images.



categories_table
-------------------------------------------------------------------------------------------------------------------------
unique_id | parent_id | file_type | title
-------------------------------------------------------------------------------------------------------------------------
1 | 0 | folder | root folder
-------------------------------------------------------------------------------------------------------------------------
2 | 1 | folder | locations
-------------------------------------------------------------------------------------------------------------------------
3 | 2 | folder | North america
-------------------------------------------------------------------------------------------------------------------------
4 | 3 | folder | United States
-------------------------------------------------------------------------------------------------------------------------
5 | 4 | folder | New York
-------------------------------------------------------------------------------------------------------------------------
6 | 5 | slide | New York City
-------------------------------------------------------------------------------------------------------------------------
7 | 6 | image | img_1.jpg
-------------------------------------------------------------------------------------------------------------------------
8 | 6 | image | img_2.jpg
-------------------------------------------------------------------------------------------------------------------------
9 | 6 | image | img_3.jpg
-------------------------------------------------------------------------------------------------------------------------

flat parent ids table takes each item in categories table and list ever possible parent id up the tree. With this table it is easy to to list all tags or perform searches within any parent id since a query can be done to get all ids with a parent id in the flat table. file_depth is used to get a count of how deep the item is.


flat_parent_ids
-------------------------------------------------------------------------------------------------------------------------
unique_id | parent_id | file_type | file_depth | file_depth rev
-------------------------------------------------------------------------------------------------------------------------
2 | 1 | folder | 1 | 1

-------------------------------------------------------------------------------------------------------------------------
3 | 1 | folder | 1 | 2
-------------------------------------------------------------------------------------------------------------------------
3 | 2 | folder | 2 | 1

-------------------------------------------------------------------------------------------------------------------------
4 | 1 | folder | 1 | 3
-------------------------------------------------------------------------------------------------------------------------
4 | 2 | folder | 2 | 2
-------------------------------------------------------------------------------------------------------------------------
4 | 3 | folder | 3 | 1

-------------------------------------------------------------------------------------------------------------------------
5 | 1 | folder | 1 | 4
-------------------------------------------------------------------------------------------------------------------------
5 | 2 | folder | 2 | 3
-------------------------------------------------------------------------------------------------------------------------
5 | 3 | folder | 3 | 2
-------------------------------------------------------------------------------------------------------------------------
5 | 4 | folder | 4 | 1

-------------------------------------------------------------------------------------------------------------------------
6 | 1 | slide | 1 | 5
-------------------------------------------------------------------------------------------------------------------------
6 | 2 | slide | 2 | 4
-------------------------------------------------------------------------------------------------------------------------
6 | 3 | slide | 3 | 3
-------------------------------------------------------------------------------------------------------------------------
6 | 4 | slide | 4 | 2
-------------------------------------------------------------------------------------------------------------------------
6 | 5 | slide | 5 | 1

-------------------------------------------------------------------------------------------------------------------------
7 | 1 | image | 1 | 6
-------------------------------------------------------------------------------------------------------------------------
7 | 2 | image | 2 | 5
-------------------------------------------------------------------------------------------------------------------------
7 | 3 | image | 3 | 4
-------------------------------------------------------------------------------------------------------------------------
7 | 4 | image | 4 | 3
-------------------------------------------------------------------------------------------------------------------------
7 | 5 | image | 5 | 2
-------------------------------------------------------------------------------------------------------------------------
7 | 6 | image | 6 | 1

-------------------------------------------------------------------------------------------------------------------------
8 | 1 | image | 1 | 6
-------------------------------------------------------------------------------------------------------------------------
8 | 2 | image | 2 | 5
-------------------------------------------------------------------------------------------------------------------------
8 | 3 | image | 3 | 4
-------------------------------------------------------------------------------------------------------------------------
8 | 4 | image | 4 | 3
-------------------------------------------------------------------------------------------------------------------------
8 | 5 | image | 5 | 2
-------------------------------------------------------------------------------------------------------------------------
8 | 6 | image | 6 | 1

-------------------------------------------------------------------------------------------------------------------------
9 | 1 | image | 1 | 6
-------------------------------------------------------------------------------------------------------------------------
9 | 2 | image | 2 | 5
-------------------------------------------------------------------------------------------------------------------------
9 | 3 | image | 3 | 4
-------------------------------------------------------------------------------------------------------------------------
9 | 4 | image | 4 | 3
-------------------------------------------------------------------------------------------------------------------------
9 | 5 | image | 5 | 2
-------------------------------------------------------------------------------------------------------------------------
9 | 6 | image | 6 | 1



this schema can be used to get a total count in each folder e.g. search for total image count in folder United States.

SELECT COUNT(*) FROM flat_parent_ids WHERE parent_id='4' AND file_type = 'image'



Or search within a category, first do a search and create an array with all results that have the tag e.g. "city" (using free_tag_class). Then in php do a for each loop see if the results is in the array of tag results and set it true (to be displayed) if it is.

$sql = "SELECT unique_id FROM flat_parent_ids WHERE parent_id='4';

$result = mysql_query($sql);
while($s = mysql_fetch_array($result)){
$display_tags[$s['unique_id']] = true;

}



QUESTION: is this a viable solution to search within category because it seems to work? I am doing alot with getting sql results and storing them into a PHP array then comparing another set of sql results (I am also doing this to exclude results e.g. NOT brooklyn). Is this correct? if it does work, the problem I run into is if I move something, say for example move United States to -> root -> scenes. The flat parent ids entire matrix would have to be redone.

there was a post here recently referring to hierarchies / tree structures which may be of interest.
Database tree structure functionality
there was also a reference to something on the sitepoint site using PHP & hierarchies which provides another mechanism
http://www.sitepoint.com/article/hie...ata-database/2

the latter mechanism requires more maintenance, and is mor eporne to breaking down (you must make certain that you use the specified , whereas the first reference is harder to break, require no maintenance, but only supports the number of level you design in.

and if you are designing something for the woild wide webaroonie, you shouldn't be going down eleven levels in da foist place!!!

why?
could it be that most spotty teenage kids, who seem to proliferate to t'net, have the attention span of a gnat?

the three-click rule isn't aimed solely at mollifying the teenagers in your target audience

check that wikipedia article -- even the british gummint says the three-click rule is best practice

Thanks for the replies, it looks like the horizontal method is the best. It looks like it will require a very complicated method of editing the db. Thanks again. One last question-

when searching for "pets AND animals" exclude "dogs"

I am getting all results for pets, then getting all results for animals and comparing two arrays in php, then getting all results for pets and comparing that array again in PHP. Is this correct or should this be done with mysql? I am using a search method that has a keyword/tag assigned with an id and then each post has a list of Ids associated with it.

use the standard sql EXCEPT operator

oh, wait, mysql hasn't implemented it

in that case, use a NOT EXISTS subquery