Results 1 to 8 of 8
  1. #1
    Join Date
    Mar 2008
    Posts
    11

    Unanswered: some advice please

    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
    -------------------------------------------------------------------------------------------------------------------------

  2. #2
    Join Date
    Mar 2008
    Posts
    11
    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.

  3. #3
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    there was a post here recently referring to hierarchies / tree structures which may be of interest.
    http://www.dbforums.com/showthread.php?t=1628032
    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.

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by healdem
    ... 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!!!

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Quote Originally Posted by r937
    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?

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  7. #7
    Join Date
    Mar 2008
    Posts
    11
    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.

  8. #8
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    use the standard sql EXCEPT operator

    oh, wait, mysql hasn't implemented it

    in that case, use a NOT EXISTS subquery

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •