homepage Welcome to WebmasterWorld Guest from 54.234.147.84
register, free tools, login, search, subscribe, help, library, announcements, recent posts, open posts,
Subscribe to WebmasterWorld
Home / Forums Index / Code, Content, and Presentation / PHP Server Side Scripting
Forum Library, Charter, Moderators: coopster & jatar k

PHP Server Side Scripting Forum

    
How to make hyper-linked structure of a hierarchy tree with categories
Tree structure in PHP/Mysql
tabish




msg:4505380
 10:10 am on Oct 8, 2012 (gmt 0)

Hello All,

This is my DB structure:

cat_ID| cat_name | cat_nicename | lft | rgt
_________________________________________________
287 | Wallpapers | wallpapers | 61 | 62
1656 | Gadget | gadget | 63 | 64
1958 | Sms | sms | 65 | 66
2901 | fun | fun | 67 | 68
4419 | Joke | joke | 69 | 70
4775 | health | health | 71 | 72
5098 | Mobile | mobile | 73 | 90
5100 | TV | tv | 91 | 92
5101 | Main | main | 93 | 94
5102 | Nokia | nokia | 76 | 83
5103 | Samsung | samsung | 84 | 87
5104 | Micromax | micromax | 88 | 89
5105 | Nokia Sub 1 | nokiasub1 | 77 | 78
5106 | Nokia Sub 2 | nokiasub2 | 79 | 82
5107 | Nokia Sub 3 | nokiasub3 | 80 | 81
5109 | Galaxy | galaxy | 85 | 86



i am retrieving Tree hierarchy of a particular node using this query:

$sql = "SELECT n.cat_ID,n.cat_name,n.cat_nicename, (COUNT(*)-1)
AS depth FROM " .TBL_CATEGORIES. " AS n, " .TBL_CATEGORIES. " AS p
WHERE p.cat_nicename='mobile'
AND n.lft BETWEEN p.lft AND p.rgt
GROUP BY n.lft ORDER BY n.lft;";


Funtion prints my TREE is:


$result = '';
while (!empty($tree)) {
$currNode = array_shift($tree);
$result .= _tab($currNode['depth']) . $currNode['cat_name'].' -> Depth '.$currNode['depth'].'<br />';
}
function _tab($depth) { $tabs = ''; while ($depth-- > 0) $tabs .= ' '; return $tabs; }

print $result;



It prints tree like this:

Mobile ->Depth 0
Nokia -> Depth 1
Nokia Sub 1 -> Depth 2
Nokia Sub 2 -> Depth 2
Nokia Sub 3 -> Depth 3
Samsung -> Depth 1
Galaxy -> Depth 2
Micromax -> Depth 1



The -> Depth i am showing here just to make everyone understand about the depth my tree receive to display hierarchy.

From Database i am getting these two fields cat_name, cat_nicename cat_name is displaying the name of the node and cat_nicename is to display short name to use in url.

Now what I want is, I want to Hyperlink each category and subcategories using this depth and cat_nicename . The link i am showing below before each category and sub-cats:


Mobile ->Depth 0 -> http://sitename.com/mobile
Nokia -> Depth 1 -> http://sitename.com/mobile/nokia
Nokia Sub 1 -> Depth 2 -> http://sitename.com/mobile/nokia/nokiasub1
Nokia Sub 2 -> Depth 2 -> http://sitename.com/mobile/nokia/nokiasub2
Nokia Sub 3 -> Depth 3 -> http://sitename.com/mobile/nokia/nokiasub2/nokiasub3
Samsung -> Depth 1 -> http://sitename.com/mobile/samsung
Galaxy -> Depth 2 -> http://sitename.com/mobile/samsung/galaxy
Micromax -> Depth 1 -> http://sitename.com/mobile/micromax


in webmaster world it is not showing the hierarchy actually, but i hope you can get what i mean?

Each loop of above While contains cat_nicename , that contains the shortform of that directory name.

So my question is:

How can i make this permalink structure using same function above? on each loop it will keep adding hyperlink with directory name according to their Parent and Depth?


Warm Regards

 

swa66




msg:4505384
 10:26 am on Oct 8, 2012 (gmt 0)

I'm not sure I fully got how your tree is modeled in your database (all I'd expect is a parent id).

Maybe the first part is to explain just what lft and rgt mean in your table.

Anyway what I would do:
- just keep the parent id for every category
- load a tree in memory that can be walked efficiently (catid -> parentcatid) is all you need ...
- And then you can walk up to the root from any catid, in the inmemory array, which gives you a list of cats to get the details like url, what to show etc from, you even get the order (to show them in - well reverse, but that's easy enough).
[Although, no doubt somebody with more SQL skills than myself could walk the tree in SQL just as well]

tabish




msg:4505387
 10:38 am on Oct 8, 2012 (gmt 0)

@swa66 .. thank you for the reply.. This tree is based on a nested set model "Nested set model" will give you first result of wiki. Please take a look and then may be you will be able to help me better :)

swa66




msg:4505403
 10:57 am on Oct 8, 2012 (gmt 0)

Ah, I never used that model as I felt unsafe with updating the tree. (any update means you need to update a lot -if not all- of your entries.

Anyway: [mikehillyer.com...] has the query you seek under the title "Retrieving a Single Path"

tabish




msg:4505419
 12:03 pm on Oct 8, 2012 (gmt 0)

@swa66 .. I resolved it myself :) .. by the way.. what nested model you suggest then?

swa66




msg:4505423
 12:17 pm on Oct 8, 2012 (gmt 0)

As I said: I only use a parentid for this. There are drawbacks to it too (esp. if you do not have a consistent or predictable depth), but it's at least a lot easier to repair should it ever go out of sync.

tabish




msg:4505427
 12:23 pm on Oct 8, 2012 (gmt 0)

@swa66 .. Ahh i get it now.. :)

actually nested model set it very fast when you try to receive and display tree, only updation is a bit slow.. when you have lots of entries... single query does all the job in nested model, while ParentID one takes lots of quaries. So far all my work is done using single query only :)

Global Options:
 top home search open messages active posts  
 

Home / Forums Index / Code, Content, and Presentation / PHP Server Side Scripting
rss feed

All trademarks and copyrights held by respective owners. Member comments are owned by the poster.
Home ¦ Free Tools ¦ Terms of Service ¦ Privacy Policy ¦ Report Problem ¦ About ¦ Library ¦ Newsletter
WebmasterWorld is a Developer Shed Community owned by Jim Boykin.
© Webmaster World 1996-2014 all rights reserved