SitePoint Sponsor

User Tag List

Page 1 of 2 12 LastLast
Results 1 to 25 of 40

Hybrid View

  1. #1
    SitePoint Enthusiast
    Join Date
    May 2010
    Location
    UK
    Posts
    62
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Display a hierarchical menu using php

    Hi guys and gals,

    I'm wondering if you might be able to give me some help. I'm looking to generate a drop-down menu, with multiple parent, child and (grand)child items.

    Currently, I have a DB table setup with the pages in it, and the following fields which are relevant:
    • id
    • parent_id
    • name
    • template_id
    • content
    • etc, etc, etc


    In the simple little CMS that I've coded, the admin can create pages and on the form, one of the options - presented as a drop down of all the existing page names that are in the db - is to select "parent page".

    So, the "about" page will have a parent_id of "1" as the "home" page's ID is "1". Hope that makes sense - sure it does!

    Anyway...

    I gather this method of storing the database data is called the 'adjacency model'. I know there are other models - such as the modified pre-order tree traversal, etc, but I'm happy with the method of storage that I've chosen, as the DB will never be *that* full of pages - it's never going to be a huge site.

    The problem I've got is how to extract the data in my controller script for the frontend, and present it as in a list (probably UL?) that I can then use Javascript to convert into a neat drop-down menu.

    I've looked a lot of places for help but I can't seem to find the right thing.
    Any help and advice you could offer is really very much appreciated

    Cheers!

    Al

  2. #2
    Keeper of the SFL StarLion's Avatar
    Join Date
    Feb 2006
    Location
    Atlanta, GA, USA
    Posts
    3,748
    Mentioned
    71 Post(s)
    Tagged
    0 Thread(s)
    First thing i'm going to do is bounce you over to the mySQL forums to help get the ball rolling - getting your query straight is the first step!

    (plus r937 always corrects me when i try to answer these sorts of things ;P )

    Edit:

    thread bounced over - Mittineague

  3. #3
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,248
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    have you written your 3-level query yet, or do you want me to point you to the Categories and Subcategories article again? because the query you need is in there, you just need to adapt the table/column names to your setup

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  4. #4
    SitePoint Enthusiast
    Join Date
    May 2010
    Location
    UK
    Posts
    62
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Aaarrrggghhh.

    Thanks for the replies guys, really, but I simply **don't** understand what I'm meant to be doing!.
    I've read the article four times now. I understand that I need to make a query, but it's the OUTPUT into PHP that I don't understand how to do. I posted this query in the PHP forums for a reason, having originally been told to do that on the thread I started in the mySQL forum, and now I've had this new thread moved into the mySQL forum too.

    Frustratedly,

    Alex

  5. #5
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,248
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    so could you please show the query that's working properly?

    that's the only way that the php guys will be able to write your php code for you -- they have to see the column names that you used, which determine the names they will test for in php
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  6. #6
    SitePoint Wizard bronze trophy
    Join Date
    Jul 2006
    Location
    Augusta, Georgia, United States
    Posts
    4,147
    Mentioned
    16 Post(s)
    Tagged
    3 Thread(s)
    PHP Code:
    <?php
    // mock result set
    $rows = array(
        array(
            
    'id'=>1
            
    ,'parent_id'=>null
            
    ,'name'=>'Home'
        
    )
        ,array(
            
    'id'=>2
            
    ,'parent_id'=>null
            
    ,'name'=>'About'
        
    )
        ,array(
            
    'id'=>3
            
    ,'parent_id'=>2
            
    ,'name'=>'Me'
        
    )
        ,array(
            
    'id'=>4
            
    ,'parent_id'=>2
            
    ,'name'=>'Company'
        
    )
        ,array(
            
    'id'=>5
            
    ,'parent_id'=>null
            
    ,'name'=>'Portfolio'
        
    )
        ,array(
            
    'id'=>6
            
    ,'parent_id'=>5
            
    ,'name'=>'Web'
        
    )
        ,array(
            
    'id'=>7
            
    ,'parent_id'=>5
            
    ,'name'=>'Writing'
        
    )
        ,array(
            
    'id'=>8
            
    ,'parent_id'=>6
            
    ,'name'=>'Design'
        
    )
        ,array(
            
    'id'=>9
            
    ,'parent_id'=>6
            
    ,'name'=>'Programming'
        
    )
    );

    /*
    UNTESTED

    $rows = array();
    $sql = 'SELECT * FROM {table}'; -- replace * with proper columns and {table} w/ table name
    $result = mysql_query($sql);
    while($row = mysql_fetch_assoc($result) ) {
        $rows[] = $row;
    }
    */

    // covert raw result set to tree
    $menu convertAdjacencyListToTree(null,$rows,'id','parent_id','links');
    // echo '<pre>',print_r($menu),'</pre>';

    // display menu
    echo themeMenu($menu,1);

    /*
    * ------------------------------------------------------------------------------------
    * Utility functions
    * ------------------------------------------------------------------------------------
    */

    /*
    * Convert adjacency list to hierarchical tree
    *
    * @param value of root level parent most likely null or 0
    * @param array result
    * @param str name of primary key column
    * @param str name of parent_id column - most likely parent_id
    * @param str name of index that children will reside ie. children, etc
    * @return array tree
    */
    function convertAdjacencyListToTree($intParentId,&$arrRows,$strIdField,$strParentsIdField,$strNameResolution) {
                
        
    $arrChildren = array();
                
        for(
    $i=0;$i<count($arrRows);$i++) {
            if(
    $intParentId === $arrRows[$i][$strParentsIdField]) {
                
    $arrChildren array_merge($arrChildren,array_splice($arrRows,$i--,1));
            }
        }
                    
        
    $intChildren count($arrChildren);
        if(
    $intChildren != 0) {
            for(
    $i=0;$i<$intChildren;$i++) {
                
    $arrChildren[$i][$strNameResolution] = convertAdjacencyListToTree($arrChildren[$i][$strIdField],$arrRows,$strIdField,$strParentsIdField,$strNameResolution);
            }        
        }
                    
        return 
    $arrChildren;
            
    }

    /*
    * Theme menu
    *
    * @param array menu
    * @param runner (depth)
    * @return str themed menu
    */
    function themeMenu($menu,$runner) {

        
    $out '';
            
        if(empty(
    $menu)) {
            return 
    $out;
        }
            
        
    $out.='<ul>';
        foreach(
    $menu as $link) {
            
    $out.= sprintf(
                
    '<li class="depth-%u">%s%s</li>'
                
    ,$runner
                
    ,$link['name']
                ,
    themeMenu($link['links'],($runner+1))
            );
        }
        
        
    $out.='</ul>';
        return 
    $out;
        
    }

    ?>
    Replace mock result set with the real result set and there you go. Also, Im not sure what the root level parent is in your actual case. In the code I provided its assumed to be null, so that may need to be changed if its 0 or some other value.

    PHP Code:
    // covert raw result set to tree
    $menu convertAdjacencyListToTree(null,$rows,'id','parent_id','links'); 
    The first argument is the value for the root level menu items. So if the actual value is 0 rather than null it would need to be changed to the below instead.

    PHP Code:
    // covert raw result set to tree
    $menu convertAdjacencyListToTree(0,$rows,'id','parent_id','links'); 
    The only code I hate more than my own is everyone else's.

  7. #7
    SitePoint Enthusiast
    Join Date
    May 2010
    Location
    UK
    Posts
    62
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    @oddz - thanks so much for this. I'm going to play with this today, and let you know how it goes.

    Yes, the value for the parent_id column on root_level parent items is indeed 'null'.

    A couple of queries before I implement:

    1) The 'mock results set' (where you tell me to replace that with the 'real' results set), what exact results should I put in there? Am I just listing the parent pages there?
    The CMS is dynamic and although the six parent pages/categories will always be the same, sub-pages will be added and deleted.

    2) Do I need to modify anything in the 'utility functions' section, prior to implementation?

    Thanks for all your help - so very much appreciated. Am learning!

    Al

  8. #8
    SitePoint Enthusiast
    Join Date
    May 2010
    Location
    UK
    Posts
    62
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    @oddz - Further to earlier update.
    Your example works perfectly, using your test results set, however when I comment that out and work with your "untested" section, I'm hitting some problems.

    So far I've got:
    Code PHP:
    include $_SERVER['DOCUMENT_ROOT'] . '/admin/assets/lib/config.inc.php';  
     
    $rows = array();
    $sql = 'SELECT id, parent_id, name FROM pages';
    $result = mysqli_query($link, $sql);
    	if (!$result)
    	{
    		$error = 'Error fetching page structure, for nav menu generation.';
    		include 'error.html.php';
    		exit();
    	}
     
    while($row = mysqli_fetch_assoc($result) ) {
        $rows[] = $row;
    }

    but this is producing a blank page. Note how I've changed your "mysql" to "mysqli" (as the "mysql" without the "i" was causing an error).

    Can you help me clarify why these results aren't coming from the database and into an array, correctly?

    Many thanks in advance

    Al

  9. #9
    Keeper of the SFL StarLion's Avatar
    Join Date
    Feb 2006
    Location
    Atlanta, GA, USA
    Posts
    3,748
    Mentioned
    71 Post(s)
    Tagged
    0 Thread(s)
    Put echo "This is working"; somewhere in your page and execute it. If you still get a blank screen, you've got a syntax error somewhere. (White Page of Syntax Error. WPSE, pronounced Whoops)

  10. #10
    SitePoint Enthusiast
    Join Date
    May 2010
    Location
    UK
    Posts
    62
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hey StarLion.
    I've done that and it works fine.
    Here is the full code for the page:

    Code PHP:
    <?php
    // mock result set
    /*$rows = array(
        array(
            'id'=>1
            ,'parent_id'=>NULL
            ,'name'=>'Home'
        )
        ,array(
            'id'=>2
            ,'parent_id'=>NULL
            ,'name'=>'About'
        )
        ,array(
            'id'=>3
            ,'parent_id'=>2
            ,'name'=>'Me'
        )
        ,array(
            'id'=>4
            ,'parent_id'=>2
            ,'name'=>'Company'
        )
        ,array(
            'id'=>5
            ,'parent_id'=>NULL
            ,'name'=>'Portfolio'
        )
        ,array(
            'id'=>6
            ,'parent_id'=>5
            ,'name'=>'Web'
        )
        ,array(
            'id'=>7
            ,'parent_id'=>5
            ,'name'=>'Writing'
        )
        ,array(
            'id'=>8
            ,'parent_id'=>6
            ,'name'=>'Design'
        )
        ,array(
            'id'=>9
            ,'parent_id'=>6
            ,'name'=>'Programming'
        )
    ); */
     
     
    //UNTESTED
    include $_SERVER['DOCUMENT_ROOT'] . '/admin/assets/lib/config.inc.php';  
     
    $rows = array();
    $sql = 'SELECT id, parent_id, name FROM pages';
    $result = mysqli_query($link, $sql);
        if (!$result)
        {
            $error = 'Error fetching page structure, for nav menu generation.';
            include 'error.html.php';
            exit();
        }
     
    while($row = mysqli_fetch_assoc($result) ) {
        $rows[] = $row;
    }
     
     
    // covert raw result set to tree
    $menu = convertAdjacencyListToTree(null,$rows,'id','parent_id','links');
    // echo '<pre>',print_r($menu),'</pre>';
     
    // display menu
    echo themeMenu($menu,1);
     
    /*
    * ------------------------------------------------------------------------------------
    * Utility functions
    * ------------------------------------------------------------------------------------
    */
     
    /*
    * Convert adjacency list to hierarchical tree
    *
    * @param value of root level parent most likely null or 0
    * @param array result
    * @param str name of primary key column
    * @param str name of parent_id column - most likely parent_id
    * @param str name of index that children will reside ie. children, etc
    * @return array tree
    */
    function convertAdjacencyListToTree($intParentId,&$arrRows,$strIdField,$strParentsIdField,$strNameResolution) {
     
        $arrChildren = array();
     
        for($i=0;$i<count($arrRows);$i++) {
            if($intParentId === $arrRows[$i][$strParentsIdField]) {
                $arrChildren = array_merge($arrChildren,array_splice($arrRows,$i--,1));
            }
        }
     
        $intChildren = count($arrChildren);
        if($intChildren != 0) {
            for($i=0;$i<$intChildren;$i++) {
                $arrChildren[$i][$strNameResolution] = convertAdjacencyListToTree($arrChildren[$i][$strIdField],$arrRows,$strIdField,$strParentsIdField,$strNameResolution);
            }        
        }
     
        return $arrChildren;
     
    }
     
    /*
    * Theme menu
    *
    * @param array menu
    * @param runner (depth)
    * @return str themed menu
    */
    function themeMenu($menu,$runner) {
     
        $out = '';
     
        if(empty($menu)) {
            return $out;
        }
     
        $out.='<ul>';
        foreach($menu as $link) {
            $out.= sprintf(
                '<li class="depth-%u">%s%s</li>'
                ,$runner
                ,$link['name']
                ,themeMenu($link['links'],($runner+1))
            );
        }
     
        $out.='</ul>';
        return $out;
     
    }
     
    echo 'this is working';
    ?>

    Am now totally flummoxed why the array isn't being generated from the query.

  11. #11
    Twitter: @AnthonySterling silver trophy AnthonySterling's Avatar
    Join Date
    Apr 2008
    Location
    North-East, UK.
    Posts
    6,111
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)
    This thread probably deserves to be in the PHP forum now, I'll move it back.
    @AnthonySterling: I'm a PHP developer, a consultant for oopnorth.com and the organiser of @phpne, a PHP User Group covering the North-East of England.

  12. #12
    SitePoint Enthusiast
    Join Date
    May 2010
    Location
    UK
    Posts
    62
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by AnthonySterling View Post
    This thread probably deserves to be in the PHP forum now, I'll move it back.
    Thanks, Anthony. Still flummoxed! Been checking all the above - looks okay to my novice eye

    Any help appreciated - feels close now!

    Al

  13. #13
    SitePoint Wizard bronze trophy
    Join Date
    Jul 2006
    Location
    Augusta, Georgia, United States
    Posts
    4,147
    Mentioned
    16 Post(s)
    Tagged
    3 Thread(s)
    What is the output of the below code.

    PHP Code:
    //UNTESTED
    include $_SERVER['DOCUMENT_ROOT'] . '/admin/assets/lib/config.inc.php'
       
    $rows = array();
    $sql 'SELECT id, parent_id, name FROM pages';
    $result mysqli_query($link$sql);
        if (!
    $result)
        {
            
    $error 'Error fetching page structure, for nav menu generation.';
            include 
    'error.html.php';
            exit();
        }
     
    while(
    $row mysqli_fetch_assoc($result) ) {
        
    $rows[] = $row;


    echo 
    '<pre>',print_r($rows),'</pre>'
    The only code I hate more than my own is everyone else's.

  14. #14
    SitePoint Enthusiast
    Join Date
    May 2010
    Location
    UK
    Posts
    62
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Aah :-)
    It's output the following (there are 43 records, so I'll snip it)

    HTML Code:
    Array
    (
        [0] => Array
            (
                [id] => 1
                [parent_id] => NULL
                [name] => home
            )
    
        [1] => Array
            (
                [id] => 2
                [parent_id] => NULL
                [name] => rides_activities
            )
    
        [2] => Array
            (
                [id] => 3
                [parent_id] => NULL
                [name] => events
            )
    
        [3] => Array
            (
                [id] => 4
                [parent_id] => NULL
                [name] => opening_times
            )
    
        [4] => Array
            (
                [id] => 5
                [parent_id] => NULL
                [name] => prices
            )
    
    <SNIP>
    
        [43] => Array
            (
                [id] => 62
                [parent_id] => 1
                [name] => video
            )
    
    )
    1
    Thanks :-)
    Now, I'm assuming that's one step of a couple, to get this thing to work?

    In advance *salute*!

    Al

  15. #15
    SitePoint Enthusiast
    Join Date
    May 2010
    Location
    UK
    Posts
    62
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    ..So just to confirm that my current code is
    Code PHP:
    include $_SERVER['DOCUMENT_ROOT'] . '/admin/assets/lib/config.inc.php'; 
     
    $rows = array();
    $sql = 'SELECT id, parent_id, name FROM pages';
    $result = mysqli_query($link, $sql);
        if (!$result)
        {
            $error = 'Error fetching page structure, for nav menu generation.';
            include 'error.html.php';
            exit();
        }
     
    while($row = mysqli_fetch_assoc($result) ) {
        $rows[] = $row;
    } 
     
    echo '<pre>',print_r($rows),'</pre>';  
     
    // covert raw result set to tree
    $menu = convertAdjacencyListToTree(null,$rows,'id','parent_id','links');
    // echo '<pre>',print_r($menu),'</pre>';
     
    // display menu
    echo themeMenu($menu,1);
     
    /*
    * ------------------------------------------------------------------------------------
    * Utility functions
    * ------------------------------------------------------------------------------------
    */
     
    /*
    * Convert adjacency list to hierarchical tree
    *
    * @param value of root level parent most likely null or 0
    * @param array result
    * @param str name of primary key column
    * @param str name of parent_id column - most likely parent_id
    * @param str name of index that children will reside ie. children, etc
    * @return array tree
    */
    function convertAdjacencyListToTree($intParentId,&$arrRows,$strIdField,$strParentsIdField,$strNameResolution) {
     
        $arrChildren = array();
     
        for($i=0;$i<count($arrRows);$i++) {
            if($intParentId === $arrRows[$i][$strParentsIdField]) {
                $arrChildren = array_merge($arrChildren,array_splice($arrRows,$i--,1));
            }
        }
     
        $intChildren = count($arrChildren);
        if($intChildren != 0) {
            for($i=0;$i<$intChildren;$i++) {
                $arrChildren[$i][$strNameResolution] = convertAdjacencyListToTree($arrChildren[$i][$strIdField],$arrRows,$strIdField,$strParentsIdField,$strNameResolution);
            }        
        }
     
        return $arrChildren;
     
    }
     
    /*
    * Theme menu
    *
    * @param array menu
    * @param runner (depth)
    * @return str themed menu
    */
    function themeMenu($menu,$runner) {
     
        $out = '';
     
        if(empty($menu)) {
            return $out;
        }
     
        $out.='<ul>';
        foreach($menu as $link) {
            $out.= sprintf(
                '<li class="depth-%u">%s%s</li>'
                ,$runner
                ,$link['name']
                ,themeMenu($link['links'],($runner+1))
            );
        }
     
        $out.='</ul>';
        return $out;
     
    }

  16. #16
    SitePoint Enthusiast
    Join Date
    May 2010
    Location
    UK
    Posts
    62
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Any ideas Oddz?

  17. #17
    SitePoint Wizard bronze trophy
    Join Date
    Jul 2006
    Location
    Augusta, Georgia, United States
    Posts
    4,147
    Mentioned
    16 Post(s)
    Tagged
    3 Thread(s)
    So what about this.

    PHP Code:
    include $_SERVER['DOCUMENT_ROOT'] . '/admin/assets/lib/config.inc.php';
       
    $rows = array();
    $sql 'SELECT id, parent_id, name FROM pages';
    $result mysqli_query($link$sql);
        if (!
    $result)
        {
            
    $error 'Error fetching page structure, for nav menu generation.';
            include 
    'error.html.php';
            exit();
        }
     
    while(
    $row mysqli_fetch_assoc($result) ) {
        
    $rows[] = $row;
    }
     
    // echo '<pre>',print_r($rows),'</pre>'; 
     
    // covert raw result set to tree
    $menu convertAdjacencyListToTree(null,$rows,'id','parent_id','links');
    echo 
    '<pre>',print_r($menu),'</pre>'
    The only code I hate more than my own is everyone else's.

  18. #18
    SitePoint Enthusiast
    Join Date
    May 2010
    Location
    UK
    Posts
    62
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by oddz View Post
    So what about this.

    PHP Code:
    include $_SERVER['DOCUMENT_ROOT'] . '/admin/assets/lib/config.inc.php';
       
    $rows = array();
    $sql 'SELECT id, parent_id, name FROM pages';
    $result mysqli_query($link$sql);
        if (!
    $result)
        {
            
    $error 'Error fetching page structure, for nav menu generation.';
            include 
    'error.html.php';
            exit();
        }
     
    while(
    $row mysqli_fetch_assoc($result) ) {
        
    $rows[] = $row;
    }
     
    // echo '<pre>',print_r($rows),'</pre>'; 
     
    // covert raw result set to tree
    $menu convertAdjacencyListToTree(null,$rows,'id','parent_id','links');
    echo 
    '<pre>',print_r($menu),'</pre>'
    Thanks, it just outputs this (in the browser)

    HTML Code:
    Array
    (
    )
    1
    and to recap, the page code is:
    Code PHP:
    <?php
    // mock result set
    /*$rows = array(
        array(
            'id'=>1
            ,'parent_id'=>NULL
            ,'name'=>'Home'
        )
        ,array(
            'id'=>2
            ,'parent_id'=>NULL
            ,'name'=>'About'
        )
        ,array(
            'id'=>3
            ,'parent_id'=>2
            ,'name'=>'Me'
        )
        ,array(
            'id'=>4
            ,'parent_id'=>2
            ,'name'=>'Company'
        )
        ,array(
            'id'=>5
            ,'parent_id'=>NULL
            ,'name'=>'Portfolio'
        )
        ,array(
            'id'=>6
            ,'parent_id'=>5
            ,'name'=>'Web'
        )
        ,array(
            'id'=>7
            ,'parent_id'=>5
            ,'name'=>'Writing'
        )
        ,array(
            'id'=>8
            ,'parent_id'=>6
            ,'name'=>'Design'
        )
        ,array(
            'id'=>9
            ,'parent_id'=>6
            ,'name'=>'Programming'
        )
    ); */
     
    include $_SERVER['DOCUMENT_ROOT'] . '/admin/assets/lib/config.inc.php';
     
    $rows = array();
    $sql = 'SELECT id, parent_id, name FROM pages';
    $result = mysqli_query($link, $sql);
        if (!$result)
        {
            $error = 'Error fetching page structure, for nav menu generation.';
            include 'error.html.php';
            exit();
        }
     
    while($row = mysqli_fetch_assoc($result) ) {
        $rows[] = $row;
    }
     
    // echo '<pre>',print_r($rows),'</pre>'; 
     
    // covert raw result set to tree
    $menu = convertAdjacencyListToTree(null,$rows,'id','parent_id','links');
    echo '<pre>',print_r($menu),'</pre>';  
     
    // display menu
    echo themeMenu($menu,1);
     
    /*
    * ------------------------------------------------------------------------------------
    * Utility functions
    * ------------------------------------------------------------------------------------
    */
     
    /*
    * Convert adjacency list to hierarchical tree
    *
    * @param value of root level parent most likely null or 0
    * @param array result
    * @param str name of primary key column
    * @param str name of parent_id column - most likely parent_id
    * @param str name of index that children will reside ie. children, etc
    * @return array tree
    */
    function convertAdjacencyListToTree($intParentId,&$arrRows,$strIdField,$strParentsIdField,$strNameResolution) {
     
        $arrChildren = array();
     
        for($i=0;$i<count($arrRows);$i++) {
            if($intParentId === $arrRows[$i][$strParentsIdField]) {
                $arrChildren = array_merge($arrChildren,array_splice($arrRows,$i--,1));
            }
        }
     
        $intChildren = count($arrChildren);
        if($intChildren != 0) {
            for($i=0;$i<$intChildren;$i++) {
                $arrChildren[$i][$strNameResolution] = convertAdjacencyListToTree($arrChildren[$i][$strIdField],$arrRows,$strIdField,$strParentsIdField,$strNameResolution);
            }        
        }
     
        return $arrChildren;
     
    }
     
    /*
    * Theme menu
    *
    * @param array menu
    * @param runner (depth)
    * @return str themed menu
    */
    function themeMenu($menu,$runner) {
     
        $out = '';
     
        if(empty($menu)) {
            return $out;
        }
     
        $out.='<ul>';
        foreach($menu as $link) {
            $out.= sprintf(
                '<li class="depth-%u">%s%s</li>'
                ,$runner
                ,$link['name']
                ,themeMenu($link['links'],($runner+1))
            );
        }
     
        $out.='</ul>';
        return $out;
     
    }
     
    ?>

  19. #19
    SitePoint Wizard bronze trophy
    Join Date
    Jul 2006
    Location
    Augusta, Georgia, United States
    Posts
    4,147
    Mentioned
    16 Post(s)
    Tagged
    3 Thread(s)
    alright, lets go back a step and check out the data types.

    PHP Code:
    include $_SERVER['DOCUMENT_ROOT'] . '/admin/assets/lib/config.inc.php'
       
    $rows = array();
    $sql 'SELECT id, parent_id, name FROM pages';
    $result mysqli_query($link$sql);
        if (!
    $result)
        {
            
    $error 'Error fetching page structure, for nav menu generation.';
            include 
    'error.html.php';
            exit();
        }
     
    while(
    $row mysqli_fetch_assoc($result) ) {
        
    $rows[] = $row;
    }

    echo 
    '<pre>',var_dump($rows),'</pre>'
    The only code I hate more than my own is everyone else's.

  20. #20
    SitePoint Enthusiast
    Join Date
    May 2010
    Location
    UK
    Posts
    62
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by oddz View Post
    alright, lets go back a step and check out the data types.

    PHP Code:
    include $_SERVER['DOCUMENT_ROOT'] . '/admin/assets/lib/config.inc.php'
       
    $rows = array();
    $sql 'SELECT id, parent_id, name FROM pages';
    $result mysqli_query($link$sql);
        if (!
    $result)
        {
            
    $error 'Error fetching page structure, for nav menu generation.';
            include 
    'error.html.php';
            exit();
        }
     
    while(
    $row mysqli_fetch_assoc($result) ) {
        
    $rows[] = $row;
    }

    echo 
    '<pre>',var_dump($rows),'</pre>'
    That outputs:

    HTML Code:
    array(26) {
      [0]=>
      array(3) {
        ["id"]=>
        string(1) "1"
        ["parent_id"]=>
        string(0) ""
        ["name"]=>
        string(4) "home"
      }
      [1]=>
      array(3) {
        ["id"]=>
        string(1) "2"
        ["parent_id"]=>
        string(4) "NULL"
        ["name"]=>
        string(16) "rides_activities"
      }
      [2]=>
      array(3) {
        ["id"]=>
        string(1) "3"
        ["parent_id"]=>
        string(4) "NULL"
        ["name"]=>
        string(6) "events"
      }
      [3]=>
      array(3) {
        ["id"]=>
        string(1) "4"
        ["parent_id"]=>
        string(4) "NULL"
        ["name"]=>
        string(13) "opening_times"
      }
      [4]=>
      array(3) {
        ["id"]=>
        string(1) "5"
        ["parent_id"]=>
        string(4) "NULL"
        ["name"]=>
        string(6) "prices"
      }
      [5]=>
      array(3) {
        ["id"]=>
        string(1) "6"
        ["parent_id"]=>
        string(4) "NULL"
        ["name"]=>
        string(9) "more_info"
      }
    <SNIP>
      [24]=>
      array(3) {
        ["id"]=>
        string(2) "64"
        ["parent_id"]=>
        string(1) "1"
        ["name"]=>
        string(10) "Contact_Us"
      }
      [25]=>
      array(3) {
        ["id"]=>
        string(2) "65"
        ["parent_id"]=>
        string(0) ""
        ["name"]=>
        string(4) "news"
      }
    }
    and the php code is:
    Code PHP:
    <?php
     
    include $_SERVER['DOCUMENT_ROOT'] . '/admin/assets/lib/config.inc.php'; 
     
    $rows = array();
    $sql = 'SELECT id, parent_id, name FROM pages';
    $result = mysqli_query($link, $sql);
        if (!$result)
        {
            $error = 'Error fetching page structure, for nav menu generation.';
            include 'error.html.php';
            exit();
        }
     
    while($row = mysqli_fetch_assoc($result) ) {
        $rows[] = $row;
    }
     
    echo '<pre>',var_dump($rows),'</pre>';  
     
    // covert raw result set to tree
    $menu = convertAdjacencyListToTree(null,$rows,'id','parent_id','links');
    // echo '<pre>',print_r($menu),'</pre>';
     
    // display menu
    echo themeMenu($menu,1);
     
    /*
    * ------------------------------------------------------------------------------------
    * Utility functions
    * ------------------------------------------------------------------------------------
    */
     
    /*
    * Convert adjacency list to hierarchical tree
    *
    * @param value of root level parent most likely null or 0
    * @param array result
    * @param str name of primary key column
    * @param str name of parent_id column - most likely parent_id
    * @param str name of index that children will reside ie. children, etc
    * @return array tree
    */
    function convertAdjacencyListToTree($intParentId,&$arrRows,$strIdField,$strParentsIdField,$strNameResolution) {
     
        $arrChildren = array();
     
        for($i=0;$i<count($arrRows);$i++) {
            if($intParentId === $arrRows[$i][$strParentsIdField]) {
                $arrChildren = array_merge($arrChildren,array_splice($arrRows,$i--,1));
            }
        }
     
        $intChildren = count($arrChildren);
        if($intChildren != 0) {
            for($i=0;$i<$intChildren;$i++) {
                $arrChildren[$i][$strNameResolution] = convertAdjacencyListToTree($arrChildren[$i][$strIdField],$arrRows,$strIdField,$strParentsIdField,$strNameResolution);
            }        
        }
     
        return $arrChildren;
     
    }
     
    /*
    * Theme menu
    *
    * @param array menu
    * @param runner (depth)
    * @return str themed menu
    */
    function themeMenu($menu,$runner) {
     
        $out = '';
     
        if(empty($menu)) {
            return $out;
        }
     
        $out.='<ul>';
        foreach($menu as $link) {
            $out.= sprintf(
                '<li class="depth-%u">%s%s</li>'
                ,$runner
                ,$link['name']
                ,themeMenu($link['links'],($runner+1))
            );
        }
     
        $out.='</ul>';
        return $out;
     
    }
     
    ?>

  21. #21
    SitePoint Wizard bronze trophy
    Join Date
    Jul 2006
    Location
    Augusta, Georgia, United States
    Posts
    4,147
    Mentioned
    16 Post(s)
    Tagged
    3 Thread(s)
    Try this out and post the results.

    PHP Code:
    <?php
     
    include $_SERVER['DOCUMENT_ROOT'] . '/admin/assets/lib/config.inc.php';
       
    $rows = array();
    $sql 'SELECT id, parent_id, name FROM pages';
    $result mysqli_query($link$sql);
        if (!
    $result)
        {
            
    $error 'Error fetching page structure, for nav menu generation.';
            include 
    'error.html.php';
            exit();
        }
     
    while(
    $row mysqli_fetch_assoc($result) ) {

        if( 
    strcasecmp($row['parent_id'],'null') === || empty($row['parent_id']) ) {
             
    $row['parent_id'] = null;
        }

        
    $rows[] = $row;
    }
     
    // echo '<pre>',var_dump($rows),'</pre>'; 
     
    // covert raw result set to tree
    $menu convertAdjacencyListToTree(null,$rows,'id','parent_id','links');
    echo 
    '<pre>',print_r($menu),'</pre>';
    The only code I hate more than my own is everyone else's.

  22. #22
    SitePoint Enthusiast
    Join Date
    May 2010
    Location
    UK
    Posts
    62
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Aaah :-)
    Looking like it's taking shape :-)

    I've now got a raw array output at the top, and a formatted version at the bottom. :-)

    Do I take it that if I comment-out the line that outputs the raw array, so the code looks like this (pasted below), that'll be all the mods I need to make?

    PHP Code:
    include $_SERVER['DOCUMENT_ROOT'] . '/admin/assets/lib/config.inc.php';
       
    $rows = array();
    $sql 'SELECT id, parent_id, name FROM pages';
    $result mysqli_query($link$sql);
        if (!
    $result)
        {
            
    $error 'Error fetching page structure, for nav menu generation.';
            include 
    'error.html.php';
            exit();
        }
     
    while(
    $row mysqli_fetch_assoc($result) ) {

        if( 
    strcasecmp($row['parent_id'],'null') === || empty($row['parent_id']) ) {
             
    $row['parent_id'] = null;
        }

        
    $rows[] = $row;
    }
     
    // echo '<pre>',var_dump($rows),'</pre>'; 
     
    // covert raw result set to tree
    $menu convertAdjacencyListToTree(null,$rows,'id','parent_id','links');
    //echo '<pre>',print_r($menu),'</pre>';
     
    // display menu
    echo themeMenu($menu,1); 

  23. #23
    SitePoint Wizard bronze trophy
    Join Date
    Jul 2006
    Location
    Augusta, Georgia, United States
    Posts
    4,147
    Mentioned
    16 Post(s)
    Tagged
    3 Thread(s)
    Does this give you the expected result?

    PHP Code:
    include $_SERVER['DOCUMENT_ROOT'] . '/admin/assets/lib/config.inc.php';
       
    $rows = array();
    $sql 'SELECT id, parent_id, name FROM pages';
    $result mysqli_query($link$sql);
        if (!
    $result)
        {
            
    $error 'Error fetching page structure, for nav menu generation.';
            include 
    'error.html.php';
            exit();
        }
     
    while(
    $row mysqli_fetch_assoc($result) ) {

        if( 
    strcasecmp($row['parent_id'],'null') === || empty($row['parent_id']) ) {
             
    $row['parent_id'] = null;
        }

        
    $rows[] = $row;
    }
     
    // echo '<pre>',print_r($rows),'</pre>'; 
     
    // covert raw result set to tree
    $menu convertAdjacencyListToTree(null,$rows,'id','parent_id','links');
    // echo '<pre>',print_r($menu),'</pre>';
     
    // display menu
    echo themeMenu($menu,1);
     
    /*
    * ------------------------------------------------------------------------------------
    * Utility functions
    * ------------------------------------------------------------------------------------
    */
     
    /*
    * Convert adjacency list to hierarchical tree
    *
    * @param value of root level parent most likely null or 0
    * @param array result
    * @param str name of primary key column
    * @param str name of parent_id column - most likely parent_id
    * @param str name of index that children will reside ie. children, etc
    * @return array tree
    */
    function convertAdjacencyListToTree($intParentId,&$arrRows,$strIdField,$strParentsIdField,$strNameResolution) {
               
        
    $arrChildren = array();
               
        for(
    $i=0;$i<count($arrRows);$i++) {
            if(
    $intParentId === $arrRows[$i][$strParentsIdField]) {
                
    $arrChildren array_merge($arrChildren,array_splice($arrRows,$i--,1));
            }
        }
                   
        
    $intChildren count($arrChildren);
        if(
    $intChildren != 0) {
            for(
    $i=0;$i<$intChildren;$i++) {
                
    $arrChildren[$i][$strNameResolution] = convertAdjacencyListToTree($arrChildren[$i][$strIdField],$arrRows,$strIdField,$strParentsIdField,$strNameResolution);
            }       
        }
                   
        return 
    $arrChildren;
           
    }
     
    /*
    * Theme menu
    *
    * @param array menu
    * @param runner (depth)
    * @return str themed menu
    */
    function themeMenu($menu,$runner) {
     
        
    $out '';
           
        if(empty(
    $menu)) {
            return 
    $out;
        }
           
        
    $out.='<ul>';
        foreach(
    $menu as $link) {
            
    $out.= sprintf(
                
    '<li class="depth-%u">%s%s</li>'
                
    ,$runner
                
    ,$link['name']
                ,
    themeMenu($link['links'],($runner+1))
            );
        }
       
        
    $out.='</ul>';
        return 
    $out;
       

    The only code I hate more than my own is everyone else's.

  24. #24
    SitePoint Wizard bronze trophy
    Join Date
    Jul 2006
    Location
    Augusta, Georgia, United States
    Posts
    4,147
    Mentioned
    16 Post(s)
    Tagged
    3 Thread(s)
    If that does yield the expected result you should really consider not using an empty string or null for the top level - either or. The hack I threw in there to convert an empty string to null really shouldn't be necessary. Also, I think it may be a MySQLi thing but on my machine with mysql and pdo null columns are properly converted to a true null, rather than null inside a string, which will obviously fail with a strict comparison. Not sure why I am getting a true null value and your getting string unless your storing null in a string actually, which I doubt.
    The only code I hate more than my own is everyone else's.

  25. #25
    SitePoint Enthusiast
    Join Date
    May 2010
    Location
    UK
    Posts
    62
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by oddz View Post
    If that does yield the expected result you should really consider not using an empty string or null for the top level - either or. The hack I threw in there to convert an empty string to null really shouldn't be necessary. Also, I think it may be a MySQLi thing but on my machine with mysql and pdo null columns are properly converted to a true null, rather than null inside a string, which will obviously fail with a strict comparison. Not sure why I am getting a true null value and your getting string unless your storing null in a string actually, which I doubt.
    Hey Oddz.

    Thanks for this. I've taken a look at the DB, and "NULL" is stored in capitals in the top-level pages' parent_id fields.

    Does that help?

    Al


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
  •