Welcome to WebmasterWorld Guest from 54.196.238.210

Forum Moderators: coopster & jatar k

Message Too Old, No Replies

How to make hyper-linked structure of a hierarchy tree with categories

Tree structure in PHP/Mysql

     

tabish

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

5+ Year Member



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

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

WebmasterWorld Senior Member swa66 is a WebmasterWorld Top Contributor of All Time 10+ Year Member



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

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

5+ Year Member



@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

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

WebmasterWorld Senior Member swa66 is a WebmasterWorld Top Contributor of All Time 10+ Year Member



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

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

5+ Year Member



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

swa66

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

WebmasterWorld Senior Member swa66 is a WebmasterWorld Top Contributor of All Time 10+ Year Member



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

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

5+ Year Member



@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 :)
 

Featured Threads

Hot Threads This Week

Hot Threads This Month