SitePoint Sponsor

User Tag List

Results 1 to 6 of 6
  1. #1
    SitePoint Guru phantom007's Avatar
    Join Date
    May 2008
    Posts
    752
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)

    Question mySQL: Designing a TreeView Structure.

    Hello Everyone..

    I am looking for designing a database structure for a treeview plugin.

    The nodes should be populated from the database. The root node can have unlimited level of child nodes and each child node can further have unlimited level of sub-child nodes.

    Can someone please suggest me the database structure and possibly the query to fetch each nodes/child nodes.

    This is a structure I have thought of so far but I am not sure if this is the proper way i should be desining the table.

    Code:
    CREATE TABLE `masters` (
      `master_id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
      `master_title` VARCHAR(255) DEFAULT NULL,
      PRIMARY KEY (`master_id`)
    ) ENGINE=MYISAM AUTO_INCREMENT=208 DEFAULT CHARSET=latin1
    
    
    CREATE TABLE `mapping` (
      `mapping_id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
      `master_id_1` INT(10) DEFAULT NULL,
      `master_id_2` INT(10) DEFAULT NULL,
      `master_level_number` INT(10) DEFAULT NULL,
      `master_level_sequence` INT(10) DEFAULT NULL,
      PRIMARY KEY (`mapping_id`),
      KEY `id_index` (`master_id_1`)
    ) ENGINE=MYISAM AUTO_INCREMENT=151 DEFAULT CHARSET=latin1



    Kindly Help.

    Thanks

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,328
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    mandatory reading: http://www.sitepoint.com/hierarchical-data-database/

    the adjacency model is the easiest to understand and manipulate, but to retrieve unlimited sublevels with a single query is impossible without recursion (note: see http://sqllessons.com/categories.html if your interface will be showing only a limited number of sublevels in a single display)

    you're probably going to want to use the nested set model
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Guru phantom007's Avatar
    Join Date
    May 2008
    Posts
    752
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)
    Hi

    So for example if I want upto 6 sub-levels, will it still be hard to build a single query for it?


    Thanks

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,328
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    6 levels? pièce de gateaux

    see the sqllessons article, it has a sample query
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Guru phantom007's Avatar
    Join Date
    May 2008
    Posts
    752
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)
    Hello

    I have gone thru that article that you mentioned. I ahve managed to build a 6 level treeview.

    So next step is how do I iterate through the list of records and build a tree view using PHP.

    Perhaps this threads needs to move to the PHP section now or can someone help me with the concept here itself?


    Thanks for your help so far.

  6. #6
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,328
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    well, i can't help you with the php, but it should be really trivial once you've got your query working

    can i see your query?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


Bookmarks

Posting Permissions

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