Smarty Forum Index Smarty
WARNING: All discussion is moving to https://reddit.com/r/smarty, please go there! This forum will be closing soon.

How to create n-level menu from DB (using 1 query)
Goto page 1, 2  Next
 
This forum is locked: you cannot post, reply to, or edit topics.   This topic is locked: you cannot edit posts or make replies.    Smarty Forum Index -> Tips and Tricks
View previous topic :: View next topic  
Author Message
Ni7R0
Smarty n00b


Joined: 17 May 2006
Posts: 2
Location: Czech Republic

PostPosted: Wed May 17, 2006 9:07 pm    Post subject: How to create n-level menu from DB (using 1 query) Reply with quote

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 Wink


Last edited by Ni7R0 on Sat Oct 28, 2006 2:06 pm; edited 2 times in total
Back to top
View user's profile Send private message
philjohn
Smarty Rookie


Joined: 26 Feb 2004
Posts: 8

PostPosted: Wed Jun 21, 2006 8:02 am    Post subject: Better solution... Reply with quote

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
View user's profile Send private message
Hielke Hoeve
Smarty Elite


Joined: 06 Jan 2006
Posts: 406
Location: Netherlands

PostPosted: Wed Jun 21, 2006 9:59 am    Post subject: Reply with quote

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 Very Happy

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
View user's profile Send private message
Ni7R0
Smarty n00b


Joined: 17 May 2006
Posts: 2
Location: Czech Republic

PostPosted: Tue Jul 04, 2006 7:06 pm    Post subject: Reply with quote

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
View user's profile Send private message
Silence
Smarty Rookie


Joined: 21 Jun 2004
Posts: 8

PostPosted: Sat Aug 12, 2006 8:45 pm    Post subject: Reply with quote

great work - thx...

i need it for my categories from database... and thats <100 items...
Back to top
View user's profile Send private message
ivaldes1
Smarty n00b


Joined: 16 Aug 2006
Posts: 1

PostPosted: Wed Aug 16, 2006 7:29 pm    Post subject: Working example of this? Reply with quote

Hi, is there a working example of this? Thanks! -- IV
Back to top
View user's profile Send private message
ice024
Smarty n00b


Joined: 25 Aug 2006
Posts: 1

PostPosted: Mon Sep 11, 2006 2:44 am    Post subject: Reply with quote

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
View user's profile Send private message
boots
Administrator


Joined: 16 Apr 2003
Posts: 5611
Location: Toronto, Canada

PostPosted: Mon Sep 11, 2006 2:55 am    Post subject: Reply with quote

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
View user's profile Send private message
shailesh
Smarty n00b


Joined: 19 Oct 2007
Posts: 1

PostPosted: Fri Oct 19, 2007 10:32 am    Post subject: help me regarding reg parent-child Reply with quote

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
View user's profile Send private message
low.dev
Smarty n00b


Joined: 08 Mar 2008
Posts: 4

PostPosted: Sat Mar 08, 2008 8:55 am    Post subject: Reply with quote

this could be useful, yes, thanks.
Back to top
View user's profile Send private message
karlmeh
Smarty Rookie


Joined: 01 May 2008
Posts: 6

PostPosted: Thu May 01, 2008 12:30 pm    Post subject: Re: How to create n-level menu from DB (using 1 query) Reply with quote

hi all these codes are very usefull

thanks
Smile
Back to top
View user's profile Send private message
karlmeh
Smarty Rookie


Joined: 01 May 2008
Posts: 6

PostPosted: Thu May 01, 2008 12:30 pm    Post subject: Re: How to create n-level menu from DB (using 1 query) Reply with quote

hi all these codes are very usefull

thanks
Smile
Back to top
View user's profile Send private message
karlmeh
Smarty Rookie


Joined: 01 May 2008
Posts: 6

PostPosted: Thu May 01, 2008 12:31 pm    Post subject: Re: How to create n-level menu from DB (using 1 query) Reply with quote

hi all these codes are very usefull

thanks
Smile
Back to top
View user's profile Send private message
karlmeh
Smarty Rookie


Joined: 01 May 2008
Posts: 6

PostPosted: Thu May 01, 2008 12:32 pm    Post subject: NA Reply with quote

NA

Last edited by karlmeh on Fri May 02, 2008 3:19 pm; edited 1 time in total
Back to top
View user's profile Send private message
karlmeh
Smarty Rookie


Joined: 01 May 2008
Posts: 6

PostPosted: Thu May 01, 2008 12:32 pm    Post subject: Re: How to create n-level menu from DB (using 1 query) Reply with quote

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
View user's profile Send private message
Display posts from previous:   
This forum is locked: you cannot post, reply to, or edit topics.   This topic is locked: you cannot edit posts or make replies.    Smarty Forum Index -> Tips and Tricks All times are GMT
Goto page 1, 2  Next
Page 1 of 2

 
Jump to:  
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


Powered by phpBB © 2001, 2005 phpBB Group
Protected by Anti-Spam ACP