sub forums

[doddsey_65]

im using the modified preorder tree traversal heirarchal system for my forum. (http://articles.sitepoint.com/article/hierarchical-data-database/2)

basically where you store the left and right id to form relationships between forums like phpbb does. but im struggling to show subforums underneath their parents. eg:

 

forum id    left id     right id     parent id
    1            1           8               0          this is a category
    2            2           7               1          this is a forum belonging to the category
    3            3           6               1          this is a forum belonging to the category
    4            4           5               2          this is a subforum belonging to forum 2

 

i can display all of the forums within their categories fine but cant think how to add the subforums underneath the forums that have them. has anyone got any advice?

 

[doddsey_65]

im a step further forward. i can now show if a forum has sub forums but i am struggling to show the sub forum name.

 

this is how i tell if a forum has any subforums:

 

    $fids = $pids = array();

    foreach($row as $key => $value)
    {
            for($i=0; $i<count($row); $i++)
            {
                $fids[] = $row[$i]['f_fid'];
                $pids[] = $row[$i]['f_pid'];
            }
            if(in_array($forum_id, $pids))
            {
                echo 'has child<br />';
            }

 

but i cant show the name. any ideas?

[doddsey_65]

anyone have any ideas? if i havent explained clearly enough then please ell me and i will try to elborate.

[gizmola]

I'm stumped.  You have an article with php code and structure that shows you how to implement the algorithm and do various things.  Then you show us snippets of code, with no context, when the entire point of these algorithms is for bending a relational database into supporting hierarchies using tables.  You didn't show any schema or any queries.  Who could possibly guess what your problems are from a bunch of array variables that were defined and loaded somewhere else.

[doddsey_65]

ok ill try to make it clearer. explaining things isnt a strong point of mine.

 

heres the annotated code. $link just refers to the database connection. $template adds the variables to a file with the html code in it. basically just echos the variables assigned to it.

 

$query = $link->query("SELECT * FROM ".TBL_PREFIX."forums
                            ORDER BY f_lid ASC"); // select every forum and order by the left id
    $row = $query->fetchAll(); // this is a PDO query
    
    $fids = $pids = $children = $child_ids = array();

    foreach($row as $key => $value)
    {
        $forum_id = $row[$key]['f_fid'];

for($i=0; $i<count($row); $i++)
            {
                $fids[] = $row[$i]['f_fid']; // gather all of the forum ids
                $pids[] = $row[$i]['f_pid']; // gather all of the parent ids
            }
            
            if($row[$key]['f_type'] == 'c')
            {
                $template->is_child = true; // forum is a child so dont display it on main list
            }
            else
            {
                $template->is_child = false; // forum can be displayed on main list
            }
            
            // if the forum id is in the list of parent ids
            if(in_array($row[$key]['f_fid'], $pids)) 
            {
                $template->has_child = true; // this forum has children
                
                // populate child ids array with the forum ids of this forums children
                $child_ids[] = $row[$forum_id]['f_fid']; 
                
                for($i=0; $i<count($child_ids); $i++)
                {
                    // get the child forum names from the ids above
                    $children[] = $row[$child_ids[$i]]['f_name'];
                }
                // echo the list of children for this forum
                $template->child_name = implode(' | ', $children);    
            }
            else
            {    // forum doesnt have children
                $template->has_child = false;
            }

 

as you can see i can now display which forum has children and display the name of the child. my only problem now is that the $children array only gets populated with one value when 2 exist.

[doddsey_65]

still struggling with this. this is the revised code but still it only shows 1 of the subforums:

 

$fids = $pids = $children = $list = array();
    
    $query = $link->query("SELECT * FROM ".TBL_PREFIX."forums
                            ORDER BY f_lid ASC");
    $row = $query->fetchAll();
    
    foreach($row as $key => $value)
    {
        $forum_id = $row[$key]['f_fid'];
        
        if($row[$key]['f_pid'] == 0)  
        {
            $template->is_cat = true; 
            $template->f_cat_name = $row[$key]['f_name'];
            $template->c_url_name = create_url($template->f_cat_name);    
        }    
        else
        {    
            for($i=0; $i<count($row); $i++)
            {
                $fids[] = $row[$i]['f_fid']; // gather all of the forum ids
                $pids[] = $row[$i]['f_pid']; // gather all of the parent ids
            }
            
            if($row[$key]['f_type'] == 'c')
            {
                $template->is_child = true; // forum is a child so dont display it on main list            
            }
            else
            {
                $template->is_child = false; // forum can be displayed on main list
            }
            
            // if the forum id is in the list of parent ids
            if(in_array($forum_id, $pids)) 
            {

                $template->has_child = true; // this forum has children

                $children[$forum_id] = $row[$key]['f_name'];
                
                $list[] = $children[$forum_id]['name'];

                // echo the list of children for this forum
                $template->child_name = implode(' | ', $list);    
            }
            else
            {    // forum doesnt have children
                $template->has_child = false;
            }
            
                
                var_dump($list);

 

can anyone help?

[ZacTopher]

I don't think you're looking at this the right way.. The left and right id's you provided does not have the correct layout based on the result you're expecting

 

Your result would have been:

This is a category #1

- This is forum #1 under category #1

-- This is forum #2 under category #1

--- This is a subforum belonging to forum #1

 

The correct design should look like this (Pay attention to left and right id)

forum id    left id     right id     parent id
    1            1           8               0          this is a category #1
    2            2           5               1          this is a forum #1 under category #1
    3            6           7               1          this is a forum #2 under category #1
    4            3           4               2          this is a subforum belonging to forum #1

 

The output of the following will be:

This is category #1

- This is forum #1 under category #1

-- This is a subforum belonging to forum #1

- This is forum #2 under category #1

 

 

for the code you can use these steps as a guide:

1. select the left and right ID from the item you want to use as parent,

2. update the left and right id's multiply old value with 2 (e.g: left_id = left_id + 2) on all rows with a left id higher than the right id from step 1

3. update the right id only, multiply with 2, where right id from step 1 is between left and right id

4. create $left and $right variables where $left equals to right id from step 1 and $right equals to right id + 1

5. insert the new item with these numbers and you should be very close

 

So now to select a root and it's childs you can use the following query

SELECT p.forum_id, p.left_id, p.right_id, f.* FROM forums p LEFT JOIN forums f ON (p.forum_id=1) WHERE f.left_id BETWEEN p.left_id AND p.right_id

 

Hope this helps you on your way. I know I've been struggling alot with mptt

 

Regards,

- ZacTopher