Professional Web Applications Themes

Using self-joins to make a navigation heirarchy... - MySQL

I'm not sure if this is the appropriate place to ask this. If it isn't could you suggest another group? I've been looking for ways to make a hierarchical navigation system for a web site. Basically a content management type of thing, with unlimited numbers of sub-levels. Lets say I have a table with 3 cols; id, name and parent Lets fill it with some data 1 cars NULL 2 bikes NULL 3 trucks NULL 4 Ford 1 5 GM 1 6 Renault 1 7 Focus 4 8 Fiesta 4 9 Turbo 7 10 GT 7 I know the code ...

  1. #1

    Default Using self-joins to make a navigation heirarchy...

    I'm not sure if this is the appropriate place to ask this. If it isn't
    could you suggest another group?

    I've been looking for ways to make a hierarchical navigation system for
    a web site. Basically a content management type of thing, with unlimited
    numbers of sub-levels.

    Lets say I have a table with 3 cols;

    id, name and parent

    Lets fill it with some data

    1 cars NULL
    2 bikes NULL
    3 trucks NULL
    4 Ford 1
    5 GM 1
    6 Renault 1
    7 Focus 4
    8 Fiesta 4
    9 Turbo 7
    10 GT 7

    I know the code to create a self-join to show that the Turbo is a Focus.
    What I want to be able to do is create additional rows to say its a
    Ford, and its a car.

    My code (hopefully adapted correctly for the example above)

    SELECT a.name AS model, b.name AS type FROM my_table AS a, my_table AS
    b WHERE a._id = b.parent

    Should give me the car and the model. How can I adapt this to shown
    columns for the manufacture and the type of vechicle? And how would you
    make it flexible so you could keep adding "sub-levels"

    Or is this just completely the wrong way to go abut this?
    Bonge Guest

  2. #2

    Default Re: Using self-joins to make a navigation heirarchy...

    Bonge Boo wrote: 

    You're describing what Mark Hillyer calls the Adjacency model in his
    article at:
    http://dev.mysql.com/tech-resources/articles/hierarchical-data.html

    He also discusses the Nested set model that I plan to use in an upcoming
    project.

    There is also a webinar on this on the MySQL dev site.

    Chad


    --
    Chad Hanna
    Systems Developer FamilyHistoryOnline www.familyhistoryonline.net
    FreeBSD Apache MySQL Perl mod_perl PHP
    Chad Guest

  3. #3

    Default Re: Using self-joins to make a navigation heirarchy...


    Bonge Boo wrote: 

    I think this table should just be a list of IDs and parents. 'JOIN' the
    separate 'make' and 'model' tables onto the query.

    This is an 'adjacency list model' and so requires recursion to obtain
    the children of a given parent. This can be done using a php script,
    for instance, like this (adapted from Gijs Van Tulder's article at
    http://www.sitepoint.com/article/hierarchical-data-database):

    <?php
    // $parent is the parent of the children we want to see
    // $level is increased when we go deeper into the tree,
    // and is used to display a nice indented tree
    function display_children($parent, $level) {
    // retrieve all children of $parent
    $result = mysql_query('SELECT id FROM my_table '.
    'WHERE parent="'.$parent.'";');

    // display each child
    while ($row = mysql_fetch_array($result)) {
    echo "<br>";
    // indent and display the title of this child
    echo str_repeat("&nbsp;&nbsp;",$level).$row['title']."\n";

    // call this function again to display this
    // child's children
    display_children($row['title'], $level+1);
    }
    }

    //Usage example

    include('path/to/my/connection/script');

    display_children(NULL,0);
    ?>

    In answer to your last question, this isn't completely wrong, but there
    are other ways to go about it - see Mike Hillyer's article at
    http://dev.mysql.com/tech-resources/articles/hierarchical-data.html

    strawberry Guest

  4. #4

    Default Re: Using self-joins to make a navigation heirarchy...

    snip
     

    Many thanks to all...

    Bonge Guest

  5. #5

    Default Re: Using self-joins to make a navigation heirarchy...


    Chad Hanna wrote: 

    Hi chad, have you got a url for this?

    Captain Guest

  6. #6

    Default Re: Using self-joins to make a navigation heirarchy...

    Captain Paralytic wrote: 
    >
    > Hi chad, have you got a url for this?
    >[/ref]

    It would appear that the article on hierarchy replaced the webinar. Sorry.

    --
    Chad Hanna
    Systems Developer FamilyHistoryOnline www.familyhistoryonline.net
    FreeBSD Apache MySQL Perl mod_perl PHP
    Chad Guest

  7. #7

    Default Re: Using self-joins to make a navigation heirarchy...


    Chad Hanna wrote: 
    > >
    > > Hi chad, have you got a url for this?
    > >[/ref]
    >
    > It would appear that the article on hierarchy replaced the webinar. Sorry.
    >
    > --
    > Chad Hanna
    > Systems Developer FamilyHistoryOnline www.familyhistoryonline.net
    > FreeBSD Apache MySQL Perl mod_perl PHP[/ref]

    It's still out there somewhere though. I saw it just the other day.

    strawberry Guest

Similar Threads

  1. Spry Navigation Bars - Make them look GOOD
    By suzzie12 in forum Macromedia Exchange Dreamweaver Extensions
    Replies: 3
    Last Post: February 10th, 07:12 AM
  2. flat heirarchy permissions
    By fluffy1 in forum Macromedia Contribute Connection Administrtion
    Replies: 1
    Last Post: July 30th, 07:58 AM
  3. permissions in flat heirarchy
    By fluffy1 in forum Macromedia Contribute General Discussion
    Replies: 0
    Last Post: July 28th, 05:08 PM
  4. how to make this navigation bar
    By Dzjoepke in forum Macromedia Flash Sitedesign
    Replies: 2
    Last Post: December 11th, 07:57 AM
  5. Best way to make navigation bar on each page?
    By Juan in forum Macromedia Dreamweaver
    Replies: 0
    Last Post: July 11th, 02:41 AM

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
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139