SitePoint Sponsor

User Tag List

Page 1 of 2 12 LastLast
Results 1 to 25 of 49
  1. #1
    SitePoint Wizard
    Join Date
    Mar 2008
    Location
    United Kingdom
    Posts
    1,285
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    While loop query MySQL DB issue...

    Hi all,

    Was wondering if I could get a quick help with this.

    I have a question about retrieving results from a MySQL database using the while loop. It has to display in a certain style(see HTML below).

    I have 2 tables in my database:

    bweb_cat:
    id | name
    0 | home
    1 | contact


    bweb_pages
    id | title | file_name | subcat | cat*

    0 | home page | index | 0 | 0
    1 | get in touch | get-in-touch | 0 | 1
    2 | guestbook | guestbook | 0 | 1
    3 | feedback | feedback | 0 | 1
    4 | further feedback | further-feedback | 3 | 1

    * refers to id of bweb_cat table

    So 'further feedback'(id=4) is a subpage of 'feedback'(id=3) and in the 'contact' category.


    And my PHP code I'm using to echo out the results:

    PHP Code:
    class NavigationList
    {

         private 
    $navlist=array();
         public function 
    generateList()
         {
              
    $sql="SELECT bweb_pages.*, bweb_cat.name AS 'cat_name' FROM bweb_pages INNER JOIN bweb_cat ON bweb_cat.id = bweb_pages.cat ORDER BY bweb_cat.id ASC, bweb_pages.id ASC";
                        
    $result=mysql_query($sql);
              if(!
    $result){echo "Query failed in ".__METHOD__." on line ".__LINE__;exit;}
              if(
    mysql_num_rows($result)==0){echo "No categories found.";exit;}
              
    $navlist=array();
              
    $pointer=-1;
              
    $lastCat=-1;
              
    $lastCatName="";
              while(
    $row=mysql_fetch_assoc($result))
              {
                   if(
    $lastCat!=$row['cat']||$lastCatName!=$row['cat_name'])
                   {
                       
    $lastCat=$row['cat'];$lastCatName=$row['cat_name'];$pointer++;
                       
    $navlist[$pointer]=array('category'=>array('id'=>$lastCat,'name'=>$lastCatName),'pages'=>array());
                   }
              
    $navlist[$pointer]['pages'][]=$row;
              }
          
    $this->navlist=$navlist;
         }

         public function 
    getHTML()
         {
             
    $els=array();
              foreach(
    $this->navlist as $element)
              {
                    
    $cat=$element['category'];
                    
    $prefix="<li id=\"{$cat['name']}\">\n <a href=\"/newsite/{$cat['name']}\">{$cat['name']}</a>\n
                                    <a class=\"tab\" href=\"/newsite/
    {$cat['name']}\"></a>

                                    <div class=\"callout\" id=\"
    {$cat['name']}tab\">
                                    <div class=\"bg\"></div>
                                    <div class=\"aCol\"><ul>\n"
    ;
                    
    $sufix=" </ul>\n</div>\n</div>\n</li>";
                       if(
    sizeof($element['pages'])==0)
                       {
                            
    $els[]="$prefix  <li>No pages</li>\n$sufix";
                            continue;
                       }
                         
    $tmp="";
                         foreach(
    $element['pages'] as $page)
                         {
                           
    $tmp.="  <li id=\"{$page['file_name']}\"><a href=\"/newsite/{$page['file_name']}\">{$page['title']}</a></li>\n";
                         }
                      
    $els[]=$prefix.$tmp.$sufix;
              }
            return 
    implode("\n\n",$els);
         }


    }

    $navlist=new NavigationList();
    $navlist->generateList();
    echo 
    $navlist->getHTML(); 
    The above works perfectly, however it doesn't display the sub-pages(e.g. further feedback).


    I would like to display the subpages too, like so:

    HTML Code:
    <li id="contact">
     <ul>
      <li id="get-in-touch">get in touch</li>
      <li id="guestbook">guestbook</li>
      <li id="feedback">feedback
     <ul>
      <li id="further-feedback">further feedback</li>
     </ul>
    </li>
     </ul>
    </li>
    Should this be an easy fix to my class?

    My head is scrambled and can't seem to think of a way around it.



    Many thanks for any helpers or advice.

  2. #2
    SitePoint Evangelist bradical1379's Avatar
    Join Date
    Feb 2007
    Posts
    443
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Try putting your variables in quotations:

    Code:
    $pointer = "-1";
    $lastCat = "-1";

  3. #3
    SitePoint Wizard
    Join Date
    Mar 2008
    Location
    United Kingdom
    Posts
    1,285
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks for this.
    Like I say, the above works in displaying categories and pages. But I'd like it to display categories, pages, then sub-pages too.

    The above works, but under each page, I need it to display sub-pages(if any) in the HTML style I've posted.

  4. #4
    SitePoint Evangelist bradical1379's Avatar
    Join Date
    Feb 2007
    Posts
    443
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Oh, I misunderstood.

    So you are looking to have getHTML function edited in order to display your lists correctly?

  5. #5
    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)
    With things like this I tend to split it up into easier to manages sections. Create an array with the structure you'd like to have returned from the Model, something like...

    PHP Code:
    $aMenu = array(
        
    'Home'            =>    '/home/',
        
    'About Us'        =>    '/about-us/',
        
    'Categories'    =>    array(
            
    'Cake'        =>    '/categories/cakes/',
            
    'Cheese'    =>    '/categories/cheese/',
            
    'Biscuits'    =>    array(
                
    'Digestives'    => '/categories/biscuits/digestives/',
                
    'Rich Tea'        => '/categories/biscuits/rich-tea/',
                
    'Hob Nobs'        => '/categories/biscuits/hob-nobs/'
            
    )
        )
    ); 
    Then create a process to create a list from the data...

    PHP Code:
    function buildMenu($aMenu)
    {
        
    $sHMTL .= '<ul>';
        foreach(
    $aMenu as $mKey => $mValue)
        {
            if(
    is_array($mValue))
            {
                
    $sHMTL .= sprintf(
                    
    '<li>%s %s</li>',
                    
    $mKey,
                    
    buildMenu($mValue)
                );
            }
            else
            {
                
    $sHMTL .= sprintf(
                    
    '<li><a href="%s">%s</a></li>',
                    
    $mValue,
                    
    $mKey
                
    );
            }
        }
        
    $sHMTL .= '</ul>';
        return 
    $sHMTL;

    Now, you can concentrate on just building the dataset...

    Your go!
    @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.

  6. #6
    SitePoint Wizard
    Join Date
    Mar 2008
    Location
    United Kingdom
    Posts
    1,285
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    @bradical yes, getHTML function seems to just need a few tweaks.

    @Anthony

    Hehe, yikes

    So what I should now be doing is finding a way to generate the $aMenu array from the bweb_pages table data?
    Then the buildMenu function will do the rest?

  7. #7
    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)
    Yup.
    @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.

  8. #8
    SitePoint Wizard
    Join Date
    Mar 2008
    Location
    United Kingdom
    Posts
    1,285
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Yikes, I'm even more scared. Can you give me a starter for ten on this one?
    Really appreciate the help. Sorry to be such a pain

  9. #9
    SitePoint Wizard bronze trophy
    Join Date
    Jul 2006
    Location
    Augusta, Georgia, United States
    Posts
    4,151
    Mentioned
    16 Post(s)
    Tagged
    3 Thread(s)
    Let me ask this question. Is there any reason why you couldn't split this structure up into three tables rather then two? Is the maximum depth 3 or are you planning on a infinite hierarchy?

    - cats
    --- id

    - pages
    --- id
    --- cat_id


    - subpages
    --- id
    --- page_id

  10. #10
    SitePoint Wizard
    Join Date
    Mar 2008
    Location
    United Kingdom
    Posts
    1,285
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks for the reply oddz.
    I'm pretty 100&#37; it is a maximum depth of 3.

    Should I be splitting it up into 3 tables, how would this work?

  11. #11
    SitePoint Wizard bronze trophy
    Join Date
    Jul 2006
    Location
    Augusta, Georgia, United States
    Posts
    4,151
    Mentioned
    16 Post(s)
    Tagged
    3 Thread(s)
    Well splitting up into three would remove some of the ambiguity and make the result easier to process.

  12. #12
    SitePoint Wizard
    Join Date
    Mar 2008
    Location
    United Kingdom
    Posts
    1,285
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Aaaah OK.

    Can you show me an example of this? I was just a bit worried it would complicate matters have 3 different 'pages' tables.

  13. #13
    SitePoint Wizard bronze trophy
    Join Date
    Jul 2006
    Location
    Augusta, Georgia, United States
    Posts
    4,151
    Mentioned
    16 Post(s)
    Tagged
    3 Thread(s)
    Actually, the below may just do the trick as far as the query goes assuming you only need three level. Now you would just need to process the result into a multidimensional array based on the category, page and subpage.

    Code SQL:
    SELECT
         t1.id AS cat_id
         ,t1.name AS cat_name
         ,t2.id AS page_id
         ,t2.title AS page_title
         ,t2.file_name AS page_file_name
         ,t3.id AS subpage_id
         ,t3.title AS subpage_title
         ,t3.file_name AS subpage_file_name
      FROM
         bweb_cat AS t1
      LEFT
      JOIN
         bweb_pages AS t2
        ON
         t1.id = t2.cat
      LEFT
      JOIN
         bweb_pages AS t3
        ON
         t2.subcat = t3.id

  14. #14
    SitePoint Wizard bronze trophy
    Join Date
    Jul 2006
    Location
    Augusta, Georgia, United States
    Posts
    4,151
    Mentioned
    16 Post(s)
    Tagged
    3 Thread(s)
    This should begin to create the tier for you. Its not tested, so there may be some errors, but the logic to create the 3 level hierarchy from the result set is there. If I get some time I'll go ahead and clean it up for you. In this circumstance objects are so much nicer to deal with because they pass by reference naturally which takes the messy array manipulation out of the equation.

    PHP Code:
    class Category {

        protected 
    $id;
        protected 
    $pages;
        
        public function 
    __construct($id) {
        
            
    $this->id $id;
            
    $this->pages[] = array();
        
        }
        
        public function 
    getId() {
        
            return 
    $this->id;
        
        }
        
        public function 
    getPages() {
        
            return 
    $this->pages;
        
        }
        
        public function 
    addPage(Page $page) {
        
            
    $this->pages[] = $page;
        
        }
        
        public function 
    getPage($id) {
        
            foreach(
    $this->pages as $key=>$page) {
            
                if(
    $id==$page->getId()) return $page;
            
            }
            
            return 
    false;
        
        }

    }

    class 
    Page {
        
        protected 
    $id;
        protected 
    $pages;
        
        public function 
    __construct($id) {
        
            
    $this->id $id;
            
    $this->pages = array();
        
        }
        
        public function 
    getId() {
        
            return 
    $this->id;
        
        }
        
        public function 
    getPages() {
        
            return 
    $this->pages;
        
        }
        
        public function 
    addPage(Page $page) {
        
            
    $this->pages[] = $page;
        
        }    
        
        public function 
    getPage($id) {
        
            foreach(
    $this->pages as $key=>$page) {
            
                if(
    $id==$page->getId()) return $page;
            
            }
            
            return 
    false;
        
        }

    }

    $categories = array();
    $pk = array();

    foreach(
    $rows as $row) {

        
    $index array_search(row['cat_id'],$pk);    
        
        if(
    $index===false) {
        
            
    $current = new Category(row['id']);
            
    $categories[] = $category;
            
    $pk[] = row['id'];
        
        } else {
        
            
    $current $categories[$index];
        
        }
        
        if(
    is_null($row['page_id'])) continue;
        
        
    $currentPage $current->getPage($row['page_id']);
        
        if(
    $currentPage===false) {
        
            
    $currentPage = new Page($row['page_id']);
            
    $current->addPage($currentPage);
        
        }
        
        
        if(
    is_null($row['subpage_id'])) continue;
        
        
    $currentSubPage $currentPage->getPage($row['subpage_id']);

        if(
    $currentSubPage===false) {
        
            
    $currentPage->addPage(new Page($row['subpage_id']));
        
        }    



  15. #15
    SitePoint Wizard bronze trophy
    Join Date
    Jul 2006
    Location
    Augusta, Georgia, United States
    Posts
    4,151
    Mentioned
    16 Post(s)
    Tagged
    3 Thread(s)
    PHP Code:
    <?php
    class Page {

        protected 
    $id;
        protected 
    $title;
        protected 
    $file;
        protected 
    $pages;
        
        public function 
    __construct($id,$title,$file='') {
            
            
    $this->id $id;
            
    $this->title $title;
            
    $this->file $file;
            
    $this->pages = array();
        
        }
        
        public function 
    getId() {
        
            return 
    $this->id;
        
        }
        
        public function 
    getTitle() {
        
            return 
    $this->title;
        
        }
        
        public function 
    getFile() {
        
            return 
    $this->file;
        
        }
        
        public function 
    getPages() {
        
            return 
    $this->pages;
        
        }
        
        public function 
    addPage(Page $page) {
        
            
    $this->pages[] = $page;
        
        }
        
        public function 
    getPage($id) {
        
            foreach(
    $this->getPages() as $page) {
            
                if(
    $page->getId() == $id) return $page;
            
            }
            
            return 
    false;
        
        }

    }

    // test expected SQL result
    $rows = array(

        array(
            
    'cat_id'=>1
            
    ,'cat_name'=>'home'
            
    ,'page_id'=>1
            
    ,'page_title'=>'home page'
            
    ,'page_file'=>'/index.php'
            
    ,'subpage_id'=>NULL
            
    ,'subpage_title'=>NULL
            
    ,'subpage_file'=>NULL
        
    )
        ,array(
            
    'cat_id'=>2
            
    ,'cat_name'=>'contact'
            
    ,'page_id'=>2
            
    ,'page_title'=>'get in touch'
            
    ,'page_file'=>'/get_in_touch.php'
            
    ,'subpage_id'=>NULL
            
    ,'subpage_title'=>NULL
            
    ,'subpage_file'=>NULL
        
    )
        ,array(
            
    'cat_id'=>2
            
    ,'cat_name'=>'contact'
            
    ,'page_id'=>3
            
    ,'page_title'=>'guestbook'
            
    ,'page_file'=>'/guestbook.php'
            
    ,'subpage_id'=>NULL
            
    ,'subpage_title'=>NULL
            
    ,'subpage_file'=>NULL
        
    )
        ,array(
            
    'cat_id'=>2
            
    ,'cat_name'=>'contact'
            
    ,'page_id'=>4
            
    ,'page_title'=>'feedback'
            
    ,'page_file'=>'/feedback.php'
            
    ,'subpage_id'=>5
            
    ,'subpage_title'=>'further feedback'
            
    ,'subpage_file'=>'further_feedback.php'
        
    )

    );

    $categories = array();
    $pk = array();

    foreach(
    $rows as $row) {

        
    $index array_search($row['cat_id'],$pk);    
        
        if(
    $index===false) {
        
            
    $current = new Page($row['cat_id'],$row['cat_name']);
            
    $categories[] = $current;
            
    $pk[] = $row['cat_id'];
        
        } else {
        
            
    $current $categories[$index];
        
        }
        
        if(
    is_null($row['page_id'])) continue;
        
        
    $currentPage $current->getPage($row['page_id']);
        
        if(
    $currentPage===false) {
        
            
    $currentPage = new Page($row['page_id'],$row['page_title'],$row['page_file']);
            
    $current->addPage($currentPage);
        
        }
        
        
        if(
    is_null($row['subpage_id'])) continue;
        
        
    $currentSubPage $currentPage->getPage($row['subpage_id']);

        if(
    $currentSubPage===false) {
        
            
    $currentPage->addPage(new Page($row['subpage_id'],$row['subpage_title'],$row['subpage_file']));
        
        }    




    echo 
    '<pre>',print_r($categories),'</pre>';

    output:
    HTML Code:
    Array
    (
        [0] => Page Object
            (
                [id:protected] => 1
                [title:protected] => home
                [file:protected] => 
                [pages:protected] => Array
                    (
                        [0] => Page Object
                            (
                                [id:protected] => 1
                                [title:protected] => home page
                                [file:protected] => /index.php
                                [pages:protected] => Array
                                    (
                                    )
    
                            )
    
                    )
    
            )
    
        [1] => Page Object
            (
                [id:protected] => 2
                [title:protected] => contact
                [file:protected] => 
                [pages:protected] => Array
                    (
                        [0] => Page Object
                            (
                                [id:protected] => 2
                                [title:protected] => get in touch
                                [file:protected] => /get_in_touch.php
                                [pages:protected] => Array
                                    (
                                    )
    
                            )
    
                        [1] => Page Object
                            (
                                [id:protected] => 3
                                [title:protected] => guestbook
                                [file:protected] => /guestbook.php
                                [pages:protected] => Array
                                    (
                                    )
    
                            )
    
                        [2] => Page Object
                            (
                                [id:protected] => 4
                                [title:protected] => feedback
                                [file:protected] => /feedback.php
                                [pages:protected] => Array
                                    (
                                        [0] => Page Object
                                            (
                                                [id:protected] => 5
                                                [title:protected] => further feedback
                                                [file:protected] => further_feedback.php
                                                [pages:protected] => Array
                                                    (
                                                    )
    
                                            )
    
                                    )
    
                            )
    
                    )
    
            )
    
    )

  16. #16
    SitePoint Wizard bronze trophy
    Join Date
    Jul 2006
    Location
    Augusta, Georgia, United States
    Posts
    4,151
    Mentioned
    16 Post(s)
    Tagged
    3 Thread(s)
    build the menu:

    PHP Code:
    <?php
    makeMenu
    ($categories);
    function 
    makeMenu($pages) {

        echo 
    '<ul>',"\n";
        foreach(
    $pages as $page) {

            echo 
    '<li>',$page->getTitle();
        
            
    $childPages $page->getPages();
            if(!empty(
    $childPages)) makemenu($childPages);
        
            echo 
    '</li>',"\n";

        }
        echo 
    '</ul>',"\n";

    }
    ?>

  17. #17
    SitePoint Wizard
    Join Date
    Mar 2008
    Location
    United Kingdom
    Posts
    1,285
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Flamin' 'eck oddz, you've gone weeeeell beyond the call of duty, and I salute you/thee.

    Was just out getting some fresh air/fretting just there, and you've really helped out.

    I'll test it out shortly and get back to you.


    Thanks again for your patience and help with this.

  18. #18
    SitePoint Wizard
    Join Date
    Mar 2008
    Location
    United Kingdom
    Posts
    1,285
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Just a quick follow-up, I'm a little confused by the SELECT query.
    Can you show me how my 2 tables should now be structured, briefly.

    Thanks once more for all your time with this, I'll be chuffed if we get it working

  19. #19
    SitePoint Wizard bronze trophy
    Join Date
    Jul 2006
    Location
    Augusta, Georgia, United States
    Posts
    4,151
    Mentioned
    16 Post(s)
    Tagged
    3 Thread(s)
    This query isn't working?

    Code SQL:
    SELECT
         t1.id AS cat_id
         ,t1.name AS cat_name
         ,t2.id AS page_id
         ,t2.title AS page_title
         ,t2.file_name AS page_file
         ,t3.id AS subpage_id
         ,t3.title AS subpage_title
         ,t3.file_name AS subpage_file
      FROM
         bweb_cat AS t1
      LEFT
      JOIN
         bweb_pages AS t2
        ON
         t1.id = t2.cat
      LEFT
      JOIN
         bweb_pages AS t3
        ON
         t2.subcat = t3.id

    You don't necessarily need to change your tables with the above query. Instead the table is being rejoined on itself to get the subpage relationship.

    If this isn't working go ahead and post your actual table structures for both these tables because this is based on those expressed in your first post.

  20. #20
    SitePoint Wizard
    Join Date
    Mar 2008
    Location
    United Kingdom
    Posts
    1,285
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    My apologies, the query worked splendidly. I just looked at it quickly and never took in the join. Stupid, really.

    I will test this out as soon as I can, and get back. You've been a massive help.

  21. #21
    SitePoint Wizard
    Join Date
    Mar 2008
    Location
    United Kingdom
    Posts
    1,285
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    OK, just so I've got this exactly right.

    My code should be exactly like the following:


    PHP Code:

    require_once('connector.php');

    class 
    Category {

        protected 
    $id;
        protected 
    $pages;
        
        public function 
    __construct($id) {
        
            
    $this->id $id;
            
    $this->pages[] = array();
        
        }
        
        public function 
    getId() {
        
            return 
    $this->id;
        
        }
        
        public function 
    getPages() {
        
            return 
    $this->pages;
        
        }
        
        public function 
    addPage(Page $page) {
        
            
    $this->pages[] = $page;
        
        }
        
        public function 
    getPage($id) {
        
            foreach(
    $this->pages as $key=>$page) {
            
                if(
    $id==$page->getId()) return $page;
            
            }
            
            return 
    false;
        
        }

    }

    class 
    Page {

        protected 
    $id;
        protected 
    $title;
        protected 
    $file;
        protected 
    $pages;
        
        public function 
    __construct($id,$title,$file='') {
            
            
    $this->id $id;
            
    $this->title $title;
            
    $this->file $file;
            
    $this->pages = array();
        
        }
        
        public function 
    getId() {
        
            return 
    $this->id;
        
        }
        
        public function 
    getTitle() {
        
            return 
    $this->title;
        
        }
        
        public function 
    getFile() {
        
            return 
    $this->file;
        
        }
        
        public function 
    getPages() {
        
            return 
    $this->pages;
        
        }
        
        public function 
    addPage(Page $page) {
        
            
    $this->pages[] = $page;
        
        }
        
        public function 
    getPage($id) {
        
            foreach(
    $this->getPages() as $page) {
            
                if(
    $page->getId() == $id) return $page;
            
            }
            
            return 
    false;
        
        }

    }

    $sql1 "SELECT
         t1.id AS cat_id
         ,t1.name AS cat_name
         ,t2.id AS page_id
         ,t2.title AS page_title
         ,t2.file_name AS page_file
         ,t3.id AS subpage_id
         ,t3.title AS subpage_title
         ,t3.file_name AS subpage_file
      FROM
         bweb_cat AS t1
      LEFT
      JOIN
         bweb_pages AS t2
        ON
         t1.id = t2.cat
      LEFT
      JOIN
         bweb_pages AS t3
        ON
         t2.subcat = t3.id"
    ;

    $res1 mysql_query($sql1);

    $row mysql_fetch_array($res1);


    $categories = array();
    $pk = array();

    foreach(
    $rows as $row) {

        
    $index array_search($row['cat_id'],$pk);    
        
        if(
    $index===false) {
        
            
    $current = new Page($row['cat_id'],$row['cat_name']);
            
    $categories[] = $current;
            
    $pk[] = $row['cat_id'];
        
        } else {
        
            
    $current $categories[$index];
        
        }
        
        if(
    is_null($row['page_id'])) continue;
        
        
    $currentPage $current->getPage($row['page_id']);
        
        if(
    $currentPage===false) {
        
            
    $currentPage = new Page($row['page_id'],$row['page_title'],$row['page_file']);
            
    $current->addPage($currentPage);
        
        }
        
        
        if(
    is_null($row['subpage_id'])) continue;
        
        
    $currentSubPage $currentPage->getPage($row['subpage_id']);

        if(
    $currentSubPage===false) {
        
            
    $currentPage->addPage(new Page($row['subpage_id'],$row['subpage_title'],$row['subpage_file']));
        
        }    




    echo 
    '<pre>',print_r($categories),'</pre>';



    makeMenu($categories);
    function 
    makeMenu($pages) {

        echo 
    '<ul>',"\n";
        foreach(
    $pages as $page) {

            echo 
    '<li>',$page->getTitle();
        
            
    $childPages $page->getPages();
            if(!empty(
    $childPages)) makemenu($childPages);
        
            echo 
    '</li>',"\n";

        }
        echo 
    '</ul>',"\n";



    and this should ultimately give me:

    HTML Code:
    <li id="contact">
     <ul>
      <li id="get-in-touch">get in touch</li>
      <li id="guestbook">guestbook</li>
      <li id="feedback">feedback
     <ul>
      <li id="further-feedback">further feedback</li>
     </ul>
    </li>
     </ul>
    </li>

    thanks for all your help once more.

  22. #22
    SitePoint Wizard bronze trophy
    Join Date
    Jul 2006
    Location
    Augusta, Georgia, United States
    Posts
    4,151
    Mentioned
    16 Post(s)
    Tagged
    3 Thread(s)
    You don't need the category class if you use the most recent version. Ideally you may want a category class, but really they do the same thing (page and category).

    PHP Code:

    <?php
    class Page {

        protected 
    $id;
        protected 
    $title;
        protected 
    $file;
        protected 
    $pages;
        
        public function 
    __construct($id,$title,$file='') {
            
            
    $this->id $id;
            
    $this->title $title;
            
    $this->file $file;
            
    $this->pages = array();
        
        }
        
        public function 
    getId() {
        
            return 
    $this->id;
        
        }
        
        public function 
    getTitle() {
        
            return 
    $this->title;
        
        }
        
        public function 
    getFile() {
        
            return 
    $this->file;
        
        }
        
        public function 
    getPages() {
        
            return 
    $this->pages;
        
        }
        
        public function 
    addPage(Page $page) {
        
            
    $this->pages[] = $page;
        
        }
        
        public function 
    getPage($id) {
        
            foreach(
    $this->getPages() as $page) {
            
                if(
    $page->getId() == $id) return $page;
            
            }
            
            return 
    false;
        
        }

    }

    // test expected SQL result
    $rows = array(

        array(
            
    'cat_id'=>1
            
    ,'cat_name'=>'home'
            
    ,'page_id'=>1
            
    ,'page_title'=>'home page'
            
    ,'page_file'=>'/index.php'
            
    ,'subpage_id'=>NULL
            
    ,'subpage_title'=>NULL
            
    ,'subpage_file'=>NULL
        
    )
        ,array(
            
    'cat_id'=>2
            
    ,'cat_name'=>'contact'
            
    ,'page_id'=>2
            
    ,'page_title'=>'get in touch'
            
    ,'page_file'=>'/get_in_touch.php'
            
    ,'subpage_id'=>NULL
            
    ,'subpage_title'=>NULL
            
    ,'subpage_file'=>NULL
        
    )
        ,array(
            
    'cat_id'=>2
            
    ,'cat_name'=>'contact'
            
    ,'page_id'=>3
            
    ,'page_title'=>'guestbook'
            
    ,'page_file'=>'/guestbook.php'
            
    ,'subpage_id'=>NULL
            
    ,'subpage_title'=>NULL
            
    ,'subpage_file'=>NULL
        
    )
        ,array(
            
    'cat_id'=>2
            
    ,'cat_name'=>'contact'
            
    ,'page_id'=>4
            
    ,'page_title'=>'feedback'
            
    ,'page_file'=>'/feedback.php'
            
    ,'subpage_id'=>5
            
    ,'subpage_title'=>'further feedback'
            
    ,'subpage_file'=>'further_feedback.php'
        
    )

    );

    $categories = array();
    $pk = array();

    foreach(
    $rows as $row) {

        
    $index array_search($row['cat_id'],$pk);    
        
        if(
    $index===false) {
        
            
    $current = new Page($row['cat_id'],$row['cat_name']);
            
    $categories[] = $current;
            
    $pk[] = $row['cat_id'];
        
        } else {
        
            
    $current $categories[$index];
        
        }
        
        if(
    is_null($row['page_id'])) continue;
        
        
    $currentPage $current->getPage($row['page_id']);
        
        if(
    $currentPage===false) {
        
            
    $currentPage = new Page($row['page_id'],$row['page_title'],$row['page_file']);
            
    $current->addPage($currentPage);
        
        }
        
        
        if(
    is_null($row['subpage_id'])) continue;
        
        
    $currentSubPage $currentPage->getPage($row['subpage_id']);

        if(
    $currentSubPage===false) {
        
            
    $currentPage->addPage(new Page($row['subpage_id'],$row['subpage_title'],$row['subpage_file']));
        
        }    




    echo 
    '<pre>',print_r($categories),'</pre>';

    makeMenu($categories);
    function 
    makeMenu($pages) {

        echo 
    '<ul>',"\n";
        foreach(
    $pages as $page) {

            echo 
    '<li>',$page->getTitle();
        
            
    $childPages $page->getPages();
            if(!empty(
    $childPages)) makemenu($childPages);
        
            echo 
    '</li>',"\n";

        }
        echo 
    '</ul>',"\n";

    }

  23. #23
    SitePoint Wizard
    Join Date
    Mar 2008
    Location
    United Kingdom
    Posts
    1,285
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    That's brilliant oddz.

    Sorry to be a pain, but how do I then integrate this with my SQL resultset?

  24. #24
    SitePoint Wizard bronze trophy
    Join Date
    Jul 2006
    Location
    Augusta, Georgia, United States
    Posts
    4,151
    Mentioned
    16 Post(s)
    Tagged
    3 Thread(s)
    PHP Code:
    <?php
    class Page {

        protected 
    $id;
        protected 
    $title;
        protected 
    $file;
        protected 
    $pages;
        
        public function 
    __construct($id,$title,$file='') {
            
            
    $this->id $id;
            
    $this->title $title;
            
    $this->file $file;
            
    $this->pages = array();
        
        }
        
        public function 
    getId() {
        
            return 
    $this->id;
        
        }
        
        public function 
    getTitle() {
        
            return 
    $this->title;
        
        }
        
        public function 
    getFile() {
        
            return 
    $this->file;
        
        }
        
        public function 
    getPages() {
        
            return 
    $this->pages;
        
        }
        
        public function 
    addPage(Page $page) {
        
            
    $this->pages[] = $page;
        
        }
        
        public function 
    getPage($id) {
        
            foreach(
    $this->getPages() as $page) {
            
                if(
    $page->getId() == $id) return $page;
            
            }
            
            return 
    false;
        
        }

    }

    function 
    makeMenu($pages) {

        echo 
    '<ul>',"\n";
        foreach(
    $pages as $page) {

            echo 
    '<li>',$page->getTitle();
        
            
    $childPages $page->getPages();
            if(!empty(
    $childPages)) makemenu($childPages);
        
            echo 
    '</li>',"\n";

        }
        echo 
    '</ul>',"\n";

    }

    $sql 
    'SELECT
         t1.id AS cat_id
         ,t1.name AS cat_name
         ,t2.id AS page_id
         ,t2.title AS page_title
         ,t2.file_name AS page_file
         ,t3.id AS subpage_id
         ,t3.title AS subpage_title
         ,t3.file_name AS subpage_file
      FROM
         bweb_cat AS t1
      LEFT
      JOIN
         bweb_pages AS t2
        ON
         t1.id = t2.cat
      LEFT
      JOIN
         bweb_pages AS t3
        ON
         t2.subcat = t3.id'
    ;
         
    $result mysql_query($sql);

    if(!
    $result) exit;

    $categories = array();
    $pk = array();

    while(
    $row mysql_fetch_assoc($result)) {

        
    $index array_search($row['cat_id'],$pk);    
        
        if(
    $index===false) {
        
            
    $current = new Page($row['cat_id'],$row['cat_name']);
            
    $categories[] = $current;
            
    $pk[] = $row['cat_id'];
        
        } else {
        
            
    $current $categories[$index];
        
        }
        
        if(
    is_null($row['page_id'])) continue;
        
        
    $currentPage $current->getPage($row['page_id']);
        
        if(
    $currentPage===false) {
        
            
    $currentPage = new Page($row['page_id'],$row['page_title'],$row['page_file']);
            
    $current->addPage($currentPage);
        
        }
        
        
        if(
    is_null($row['subpage_id'])) continue;
        
        
    $currentSubPage $currentPage->getPage($row['subpage_id']);

        if(
    $currentSubPage===false) {
        
            
    $currentPage->addPage(new Page($row['subpage_id'],$row['subpage_title'],$row['subpage_file']));
        
        }    



    makeMenu($categories);
    ?>

  25. #25
    SitePoint Wizard
    Join Date
    Mar 2008
    Location
    United Kingdom
    Posts
    1,285
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    That's fantastic oddz, many thanks for this.

    I now need to add an element name for each category, and a hyperlink for each category and page so it'd be:

    <li id="contact"><a href="contact/">contact</a><ul>
    <li id="get-in-touch"><a href="contact/get-in-touch/">get in touch</a></li>

    etc....


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
  •