View previous topic :: View next topic |
Author |
Message |
Ni7R0 Smarty n00b
Joined: 17 May 2006 Posts: 2 Location: Czech Republic
|
Posted: Wed May 17, 2006 9:07 pm Post subject: How to create n-level menu from DB (using 1 query) |
|
|
Hello,
I've got solution to create nice n-level menu from id - name - parent table in a DB using only one DB query. It's a easy-to-use PHP class and a Smarty template. Here you go:
PHP class (it's without comments, so it might be a little tricky to understand):
Code: |
<?php
class MenuNodes
{
var $filter = 0;
function GetDBData($sql)
{
$result = mysql_query($sql) or die($sql.": ".mysql_error());
while ($row = mysql_fetch_array($result,MYSQL_ASSOC))
{
$data[] = $row;
}
return $data;
}
function FilterMethod($row)
{
//you need to replace $row['parent'] by your name of column, which is holding the parent's id of current entry!
return $row['parent'] == $this->filter;
}
function CreateNestedArray(&$data, &$arr, $parent, $startDepth, $maxDepth)
{
if ($maxDepth-- == 0) return;
$index = 0;
$startDepth++;
$this->filter = $parent;
$children = array_filter($data, array($this, "FilterMethod"));
foreach ($children as $child)
{
$arr[$index] = $child;
$arr[$index]['depth'] = $startDepth;
//you need to replace $child['id'] by your name of column, which is holding the id of current entry!
$this->CreateNestedArray($data, $arr[$index]['children'], $child['id'], $startDepth, $maxDepth);
$index++;
}
}
function CreateResult($sql,$parent, $startDepth, $maxDepth)
{
$data = $this->GetDBData($sql);
$arr = array();
$this->CreateNestedArray($data, $arr, $parent, $startDepth, $maxDepth);
return $arr;
}
}
?> |
Simple Smarty template (messju's defun class is required!):
Code: |
<ul>
{defun name="menurecursion" list=$menu}
{foreach from=$list item=element}
<li>{$element.id}: {$element.title}
{if !empty($element.children)}
<ul>{fun name="menurecursion" list=$element.children}</ul>
{/if}
</li>
{/foreach}
{/defun}
</ul>
|
Usage:
Code: | <?php
require("libs/Smarty.class.php");
$smarty = new Smarty;
$smarty->compile_check = true;
mysql_connect("localhost","username","password");
mysql_select_db("my_database");
$sql = "SELECT id, parent, title FROM tree_menu";
$nodes = new MenuNodes();
/*
prototype and usage of CreateResult: array CreateResult (string query, int parent, int startdepth, int maxdepth)
query - SQL query to select requered data (id, parent and title) with
parent - the first entry (root) - use 0 to display whole tree
startdepth - starting number for tracking depth (the first one is incremented by one)
maxdepth - maximal level of recursion (tree) - use maxdepth<0 for infinite depth
return value - array containing nested array
*/
$menu = $nodes->CreateResult($sql, 0, 0, -1);
$smarty->assign("menu",$menu);
$smarty->display('template.tpl');
?> |
I hope it will help someone
Last edited by Ni7R0 on Sat Oct 28, 2006 2:06 pm; edited 2 times in total |
|
Back to top |
|
philjohn Smarty Rookie
Joined: 26 Feb 2004 Posts: 8
|
Posted: Wed Jun 21, 2006 8:02 am Post subject: Better solution... |
|
|
Your solution still requires recursion in the PHP engine, this is a bad thing if you have a very large tree as you'll eventually run out of stack space (and it's a lot slower than the better method I'm about to introduce).
The better way to store heirarchical data and represent it is to do away with the adjaceny list model and move to a nested set model (such as the Modified Preorder Tree Traversal).
You can then retreive the entire tree in 1 database query and display it using a loop (no recursion required).
It's a bit complex but has many other benefits (like the ability to retrieve the path to a page in a single database query).
Good starter tutorial available here: http://www.sitepoint.com/article/hierarchical-data-database
There are other nested set methods that provide more benefits (such as faster moves/adds/removes) but those go very deeply into number and set theory and I simply haven't had time to digest the paper I read on the subject to even start implementing such a solution, so for the time being MPTT works for me. |
|
Back to top |
|
Hielke Hoeve Smarty Elite
Joined: 06 Jan 2006 Posts: 406 Location: Netherlands
|
Posted: Wed Jun 21, 2006 9:59 am Post subject: |
|
|
Usage of a Tree traversal is quite useful but most menus don't cover as much subentries as it takes to run out of stack. I do find it good practice to use complicated algorithms, just for fun
philjohn, the link you gave does show recursive functions to build the tree. _________________ Debug XHTML Compliance
SmartyPaginate
Smarty License Questions
---
(About Unix) The learning curve is full of aha! moments, such as that glorious day that the full beauty of grep and, later, find is revealed in all its majesty. --- Robert Uhl <ruhl@4dv.net> |
|
Back to top |
|
Ni7R0 Smarty n00b
Joined: 17 May 2006 Posts: 2 Location: Czech Republic
|
Posted: Tue Jul 04, 2006 7:06 pm Post subject: |
|
|
Philjohn, I know the traversal-like solutions, but this one isn't so hard to implement and eventually understand. This code is not designed for trees with thousands of items and/or levels, but it's surely enough for standard use (tens or hundreds of items). Recursion in PHP engine and servers which run it are fast, so it's still quite fast solution. |
|
Back to top |
|
Silence Smarty Rookie
Joined: 21 Jun 2004 Posts: 8
|
Posted: Sat Aug 12, 2006 8:45 pm Post subject: |
|
|
great work - thx...
i need it for my categories from database... and thats <100 items... |
|
Back to top |
|
ivaldes1 Smarty n00b
Joined: 16 Aug 2006 Posts: 1
|
Posted: Wed Aug 16, 2006 7:29 pm Post subject: Working example of this? |
|
|
Hi, is there a working example of this? Thanks! -- IV |
|
Back to top |
|
ice024 Smarty n00b
Joined: 25 Aug 2006 Posts: 1
|
Posted: Mon Sep 11, 2006 2:44 am Post subject: |
|
|
hi,i found if you add another parentlist column in your database table besides id parentid ,you will easily use this new column to get the subtree and path and "add, delete,edit" operation!
parentlist fomat: -1,1,5,8 |
|
Back to top |
|
boots Administrator
Joined: 16 Apr 2003 Posts: 5611 Location: Toronto, Canada
|
Posted: Mon Sep 11, 2006 2:55 am Post subject: |
|
|
Just as a small comment, modified Pre-order Traversal gives the benefit of being able to select tree subsets relatively easily. The major disadvantadge to MPT is that inserts/deletes are somewhat expensive -- but typical menus don't change frequently enough to warrant that being an important issue, imo.
That said, it is nice to see different approaches and solutions to common problem types.
Cheers. |
|
Back to top |
|
shailesh Smarty n00b
Joined: 19 Oct 2007 Posts: 1
|
Posted: Fri Oct 19, 2007 10:32 am Post subject: help me regarding reg parent-child |
|
|
Hi.
i m able to print the array but nt able to print it in template. please help me regarding this.
<ul>
{defun name="menurecursion" list=$menu}
{foreach from=$list item=element}
<li>{$element.id}: {$element.title}
{if !empty($element.children)}
<ul>{fun name="menurecursion" list=$element.children}</ul>
{/if}
</li>
{/foreach}
{/defun}
</ul> |
|
Back to top |
|
low.dev Smarty n00b
Joined: 08 Mar 2008 Posts: 4
|
Posted: Sat Mar 08, 2008 8:55 am Post subject: |
|
|
this could be useful, yes, thanks. |
|
Back to top |
|
karlmeh Smarty Rookie
Joined: 01 May 2008 Posts: 6
|
Posted: Thu May 01, 2008 12:30 pm Post subject: Re: How to create n-level menu from DB (using 1 query) |
|
|
hi all these codes are very usefull
thanks
|
|
Back to top |
|
karlmeh Smarty Rookie
Joined: 01 May 2008 Posts: 6
|
Posted: Thu May 01, 2008 12:30 pm Post subject: Re: How to create n-level menu from DB (using 1 query) |
|
|
hi all these codes are very usefull
thanks
|
|
Back to top |
|
karlmeh Smarty Rookie
Joined: 01 May 2008 Posts: 6
|
Posted: Thu May 01, 2008 12:31 pm Post subject: Re: How to create n-level menu from DB (using 1 query) |
|
|
hi all these codes are very usefull
thanks
|
|
Back to top |
|
karlmeh Smarty Rookie
Joined: 01 May 2008 Posts: 6
|
Posted: Thu May 01, 2008 12:32 pm Post subject: NA |
|
|
NA
Last edited by karlmeh on Fri May 02, 2008 3:19 pm; edited 1 time in total |
|
Back to top |
|
karlmeh Smarty Rookie
Joined: 01 May 2008 Posts: 6
|
Posted: Thu May 01, 2008 12:32 pm Post subject: Re: How to create n-level menu from DB (using 1 query) |
|
|
Guys, I am sorry for multiple post. I want to delete duplicate posts.
i request the admin to have a look on this issue.
I am really sorry for this mistake.
thanks.
Last edited by karlmeh on Fri May 02, 2008 3:19 pm; edited 1 time in total |
|
Back to top |
|
|