"Nested Set Model" or "modified preorder tree traversal"

I've been searching the web for the past few hours trying to find a simple
drop-in class or functions to implement "Nested Set Model" or "modified
preorder tree traversal".

I've found several tutorials and related links:

http://dev.mysql.com/tech-resources/articles/hierarchical-da ta.html
http://www.sitepoint.com/article/hierarchical-data-database

http://www.zend.com/zend/tut/tutorial-ferrara2.php?article=t utorial-ferrara2
&id=3453&open=1&anc=0&view=1
http://simon.incutio.com/archive/2003/06/19/storingTrees
http://istherelifeafter.com/joecelko.html
http://www.codeproject.com/cs/database/Trees_in_SQL_database s.asp

I even found some outdated PEAR DB_NestedSet class from 2003 that seemed way
to overly complicated. And some other PHP4_Mysql3MPTTA class on PHP Classes
that was equally complicated and written for ancient PHP4 and mySQL 3!!

Hasn't anyone else done this before (recently)?
Did everyone just write their own code every time?
Anyone have some easy to use code that allows for:
Add, delete, update/rename, select, show tree, bread crumb display, etc.

I use PHP 5.0.5 and mySQL 5.0.18, so ideally it would take advantage of all
the optimizations and bells & whistles of those more modern versions.

TIA.

D.Vin

--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: "Nested Set Model" or "modified preorder tree traversal"

> Hasn't anyone else done this before (recently)?
> Did everyone just write their own code every time?
> Anyone have some easy to use code that allows for:
> Add, delete, update/rename, select, show tree, bread crumb display, etc.
>
> I use PHP 5.0.5 and mySQL 5.0.18, so ideally it would take advantage of all
> the optimizations and bells & whistles of those more modern versions.

The php or mysql version doesn't matter, it's more to do with how you
structure your database.

Instead of your id/parentid relationship the way I did it was you create
a 'node' with this information in it.

So:

select categoryid, node, categoryname from all_categories;
categoryid | node | categoryname
------------+----------+--------------
9 | 0009 | Parent
10 | 0010 | Another Parent
11 | 00090011 | Sub Cat
13 | 00100013 | Sub Cat 2
12 | 00100012 | Sub Cat 3

catid '11' is a subcategory of '9'.
catid '13' is a subcat of '10'.

You'll need to pad each node out to a certain length so you don't get
overlaps (in my case I used 4 giving me 9999 categories at each level).
The longer each node is the more categories you can have at each level
but the smaller number of categories you have altogether.

Don't forget mysql & other databases only let you index up to 255
characters of a text field (in my case I used a varchar(200) for the
node field), so while you could in theory have infinite levels it's not
really practical (unless you use full text indexing).


Inserting is easy - you get the parent that you're inserting under and
add your padded node on the end of it giving you the full path.


Deleting is easy as well - to delete a category and all subcategories:

delete from all_categories where node like '0009%';


Moving things around is harder..


If you need more info let me know.

--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: "Nested Set Model" or "modified preorder tree traversal"

On Tue, Feb 21, 2006 at 09:38:53PM -0800, Daevid Vincent wrote:
> I've been searching the web for the past few hours trying to find a simple
> drop-in class or functions to implement "Nested Set Model" or "modified
> preorder tree traversal".

I actually fell in love of this method of doing trees when I
discovered it about a year ago, when researching a fast way to do
a parent child retrieval.

>
> I've found several tutorials and related links:
>
> http://dev.mysql.com/tech-resources/articles/hierarchical-da ta.html
> http://www.sitepoint.com/article/hierarchical-data-database
>
> http://www.zend.com/zend/tut/tutorial-ferrara2.php?article=t utorial-ferrara2
> &id=3453&open=1&anc=0&view=1
> http://simon.incutio.com/archive/2003/06/19/storingTrees
> http://istherelifeafter.com/joecelko.html
> http://www.codeproject.com/cs/database/Trees_in_SQL_database s.asp

oh i have to add some of these to my list, i didn't have a couple
of them.

>
> I even found some outdated PEAR DB_NestedSet class from 2003 that seemed way
> to overly complicated. And some other PHP4_Mysql3MPTTA class on PHP Classes
> that was equally complicated and written for ancient PHP4 and mySQL 3!!

yeah, i kind of got that feeling with the PEAR class as well, i
think it was designed that way to be as flexible as possible. I'm
not familiar with the other class, i tend to avoid anything from
PHP Classes.

>
> Hasn't anyone else done this before (recently)?
> Did everyone just write their own code every time?
> Anyone have some easy to use code that allows for:
> Add, delete, update/rename, select, show tree, bread crumb display, etc.

It would be nice to have a tool to manage the functionality. There
will always be the issue with any common tool that is built for
this purpose is how do you relate the table that defines the nested
with the table that actually holds the data.

I believe that why the PEAR and other classes you came across
seemed to be overly complicated. I have found that it is easier to
write my own management (as awful as that sounds) for the job at
hand.

>
> I use PHP 5.0.5 and mySQL 5.0.18, so ideally it would take advantage of all
> the optimizations and bells & whistles of those more modern versions.

5.0.5? you mentioned that php4 is ancient, 5.0.5 is more ancient
than php4.. infact 5.0.x is rather dead.

PHP 4.4.2 - Released Jan 13 2006
php 5.1.2 - Released Jan 12 2006
PHP 5.0.5 - Released Sept 6 2005

I wouldn't expect any more releases of 5.0.x version.


Curt.
--
cat .signature: No such file or directory

--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

RE: "Nested Set Model" or "modified preorder tree traversal"

Hi,
As your using MySQL5, I'd recommend putting all the Nested Set code in stored procedures, that way the client doesn't need
to know about anything about the method, and the left, right columns never need to be retrieved.



Jared

> I've been searching the web for the past few hours trying to
> find a simple drop-in class or functions to implement "Nested
> Set Model" or "modified preorder tree traversal".
>
> I've found several tutorials and related links:
>
> http://dev.mysql.com/tech-resources/articles/hierarchical-da ta.html
> http://www.sitepoint.com/article/hierarchical-data-database
>
> http://www.zend.com/zend/tut/tutorial-ferrara2.php?article=t ut
> orial-ferrara2
> &id=3453&open=1&anc=0&view=1
> http://simon.incutio.com/archive/2003/06/19/storingTrees
> http://istherelifeafter.com/joecelko.html
> http://www.codeproject.com/cs/database/Trees_in_SQL_database s.asp
>
> I even found some outdated PEAR DB_NestedSet class from 2003
> that seemed way to overly complicated. And some other
> PHP4_Mysql3MPTTA class on PHP Classes that was equally
> complicated and written for ancient PHP4 and mySQL 3!!
>
> Hasn't anyone else done this before (recently)?
> Did everyone just write their own code every time?
> Anyone have some easy to use code that allows for:
> Add, delete, update/rename, select, show tree, bread crumb
> display, etc.
>
> I use PHP 5.0.5 and mySQL 5.0.18, so ideally it would take
> advantage of all the optimizations and bells & whistles of
> those more modern versions.
>
> TIA.
>
> D.Vin
>
> --
> PHP General Mailing List (http://www.php.net/) To
> unsubscribe, visit: http://www.php.net/unsub.php
>

--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

RE: "Nested Set Model" or "modified preorder tree traversal"

Peter Brawley on the mySQL list pointed me at:
http://www.artfulsoftware.com/mysqlbook/sampler/mysqled1ch20 .html

Which uses mySQL 5's procedures and such.
I haven't implemented this yet, but it appears to be what I wanted for the
most part.

> -----Original Message-----
> From: Curt Zirzow [mailto:czirzow@gmail.com]
> Sent: Tuesday, February 21, 2006 10:58 PM
> To: php-general@lists.php.net
> Subject: Re: [PHP] "Nested Set Model" or "modified preorder
> tree traversal"
>
> On Tue, Feb 21, 2006 at 09:38:53PM -0800, Daevid Vincent wrote:
> > I've been searching the web for the past few hours trying
> to find a simple
> > drop-in class or functions to implement "Nested Set Model"
> or "modified
> > preorder tree traversal".
>
> I actually fell in love of this method of doing trees when I
> discovered it about a year ago, when researching a fast way to do
> a parent child retrieval.
>
> >
> > I've found several tutorials and related links:
> >
> >
> http://dev.mysql.com/tech-resources/articles/hierarchical-da ta.html
> > http://www.sitepoint.com/article/hierarchical-data-database
> >
> >
> http://www.zend.com/zend/tut/tutorial-ferrara2.php?article=t ut
> orial-ferrara2
> > &id=3453&open=1&anc=0&view=1
> > http://simon.incutio.com/archive/2003/06/19/storingTrees
> > http://istherelifeafter.com/joecelko.html
> > http://www.codeproject.com/cs/database/Trees_in_SQL_database s.asp
>
> oh i have to add some of these to my list, i didn't have a couple
> of them.
>
> >
> > I even found some outdated PEAR DB_NestedSet class from
> 2003 that seemed way
> > to overly complicated. And some other PHP4_Mysql3MPTTA
> class on PHP Classes
> > that was equally complicated and written for ancient PHP4
> and mySQL 3!!
>
> yeah, i kind of got that feeling with the PEAR class as well, i
> think it was designed that way to be as flexible as possible. I'm
> not familiar with the other class, i tend to avoid anything from
> PHP Classes.
>
> >
> > Hasn't anyone else done this before (recently)?
> > Did everyone just write their own code every time?
> > Anyone have some easy to use code that allows for:
> > Add, delete, update/rename, select, show tree, bread crumb
> display, etc.
>
> It would be nice to have a tool to manage the functionality. There
> will always be the issue with any common tool that is built for
> this purpose is how do you relate the table that defines the nested
> with the table that actually holds the data.
>
> I believe that why the PEAR and other classes you came across
> seemed to be overly complicated. I have found that it is easier to
> write my own management (as awful as that sounds) for the job at
> hand.
>
> >
> > I use PHP 5.0.5 and mySQL 5.0.18, so ideally it would take
> advantage of all
> > the optimizations and bells & whistles of those more modern
> versions.
>
> 5.0.5? you mentioned that php4 is ancient, 5.0.5 is more ancient
> than php4.. infact 5.0.x is rather dead.
>
> PHP 4.4.2 - Released Jan 13 2006
> php 5.1.2 - Released Jan 12 2006
> PHP 5.0.5 - Released Sept 6 2005
>
> I wouldn't expect any more releases of 5.0.x version.
>
>
> Curt.
> --
> cat .signature: No such file or directory
>
> --
> PHP General Mailing List (http://www.php.net/)
> To unsubscribe, visit: http://www.php.net/unsub.php
>
>

--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

RE: [PHP] "Nested Set Model" or "modified preorder tree traversal" [SOLVED]

------=_NextPart_000_0032_01C63D79.85B8B650
Content-Type: text/plain;
charset="us-ascii"
Content-Transfer-Encoding: 7bit

Since I couldn't find any short and sweet drop in code for this, I made my
own. Here it is in case anyone else finds it helpful. Should be pretty
straight forward. I use my own mysql wrapper functions, but you can pretty
much figure out what they do and S&R your own.

Hopefully this works as an attachement.

D.Vin

> -----Original Message-----
> From: Daevid Vincent [mailto:daevid@daevid.com]
> Sent: Wednesday, February 22, 2006 12:42 PM
> To: 'Curt Zirzow'; php-general@lists.php.net
> Subject: RE: [PHP] "Nested Set Model" or "modified preorder
> tree traversal"
>
> Peter Brawley on the mySQL list pointed me at:
> http://www.artfulsoftware.com/mysqlbook/sampler/mysqled1ch20 .html
>
> Which uses mySQL 5's procedures and such.
> I haven't implemented this yet, but it appears to be what I
> wanted for the
> most part.
>
> > -----Original Message-----
> > From: Curt Zirzow [mailto:czirzow@gmail.com]
> > Sent: Tuesday, February 21, 2006 10:58 PM
> > To: php-general@lists.php.net
> > Subject: Re: [PHP] "Nested Set Model" or "modified preorder
> > tree traversal"
> >
> > On Tue, Feb 21, 2006 at 09:38:53PM -0800, Daevid Vincent wrote:
> > > I've been searching the web for the past few hours trying
> > to find a simple
> > > drop-in class or functions to implement "Nested Set Model"
> > or "modified
> > > preorder tree traversal".
> >
> > I actually fell in love of this method of doing trees when I
> > discovered it about a year ago, when researching a fast way to do
> > a parent child retrieval.
> >
> > >
> > > I've found several tutorials and related links:
> > >
> > >
> > http://dev.mysql.com/tech-resources/articles/hierarchical-da ta.html
> > > http://www.sitepoint.com/article/hierarchical-data-database
> > >
> > >
> > http://www.zend.com/zend/tut/tutorial-ferrara2.php?article=t ut
> > orial-ferrara2
> > > &id=3453&open=1&anc=0&view=1
> > > http://simon.incutio.com/archive/2003/06/19/storingTrees
> > > http://istherelifeafter.com/joecelko.html
> > >
> http://www.codeproject.com/cs/database/Trees_in_SQL_database s.asp
> >
> > oh i have to add some of these to my list, i didn't have a couple
> > of them.
> >
> > >
> > > I even found some outdated PEAR DB_NestedSet class from
> > 2003 that seemed way
> > > to overly complicated. And some other PHP4_Mysql3MPTTA
> > class on PHP Classes
> > > that was equally complicated and written for ancient PHP4
> > and mySQL 3!!
> >
> > yeah, i kind of got that feeling with the PEAR class as well, i
> > think it was designed that way to be as flexible as possible. I'm
> > not familiar with the other class, i tend to avoid anything from
> > PHP Classes.
> >
> > >
> > > Hasn't anyone else done this before (recently)?
> > > Did everyone just write their own code every time?
> > > Anyone have some easy to use code that allows for:
> > > Add, delete, update/rename, select, show tree, bread crumb
> > display, etc.
> >
> > It would be nice to have a tool to manage the functionality. There
> > will always be the issue with any common tool that is built for
> > this purpose is how do you relate the table that defines the nested
> > with the table that actually holds the data.
> >
> > I believe that why the PEAR and other classes you came across
> > seemed to be overly complicated. I have found that it is easier to
> > write my own management (as awful as that sounds) for the job at
> > hand.



------=_NextPart_000_0032_01C63D79.85B8B650
Content-Type: text/plain; charset=us-ascii

--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
------=_NextPart_000_0032_01C63D79.85B8B650--

RE: [PHP] "Nested Set Model" or "modified preorder tree traversal" [SOLVED]

UGH!!!!!! LAME! This list does not allow attachments apparently. *sigh*.

Sorry, here it is as a giant "paste" then...

------------------------------------->8 snip
8<--------------------------------
/*
# This is all pretty much copy/paste code from this URL:
# http://dev.mysql.com/tech-resources/articles/hierarchical-da ta.html

here are some other references:

http://www.sitepoint.com/article/hierarchical-data-database

http://www.zend.com/zend/tut/tutorial-ferrara2.php?article=t utorial-ferrara2
&id=3453&open=1&anc=0&view=1
http://simon.incutio.com/archive/2003/06/19/storingTrees
http://istherelifeafter.com/joecelko.html
http://www.codeproject.com/cs/database/Trees_in_SQL_database s.asp

and here's an ASP technique that looks interesting
http://www.4guysfromrolla.com/webtech/101202-1.shtml

and it appears that Rails already has acts_as_nested_set:

http://www.nabble.com/acts_as_tree-with-Modified-Preorder-Tr aversal--t929560
..html
*/

require_once('../includes/db.inc.php');

//TODO: [dv] There needs to be a function to MOVE a category to another
spot.
// especially since we have a way to delete and move
children up.
// This could be a hybrid of insert_category_after() I
think.

/**
* Delete a category (and optionally all of it's children) from the tree
*
* @access public
* @return void
* @param mixed $category the string name or category ID of the
category to delete.
* @param boolean $deleteChildren (false) otherwise move all the
subcategories up to the level of the deleted category
* @author Daevid Vincent [daevid@daevid.com]
* @version 1.1
* @date 03/01/06
*/
function delete_category($category = null, $deleteChildren = false)
{
if (is_null($category)) return false;

SQL_QUERY('LOCK TABLE categories WRITE');

if (is_leaf_node($category))
{
$sth = SQL_QUERY("SELECT @myLeft := lft, @myRight := rgt,
@myWidth := rgt - lft + 1 FROM categories
WHERE ".(
(is_numeric($category)) ? "id = '".$category."'" : "name =
'".SQL_ESCAPE($category)."'" )." LIMIT 1");
if ($sth && SQL_NUM_ROWS($sth) == 1)
{
SQL_QUERY("DELETE FROM categories WHERE lft BETWEEN
@myLeft AND @myRight");

SQL_QUERY('UPDATE categories SET rgt = rgt -
@myWidth WHERE rgt > @myRight');
SQL_QUERY('UPDATE categories SET lft = lft -
@myWidth WHERE lft > @myRight');
}
}
else
{
if ($deleteChildren) //just delete the little bastards
{
$sth = SQL_QUERY("SELECT @myLeft := lft, @myRight :=
rgt, @myWidth := rgt - lft + 1 FROM categories
WHERE ".(
(is_numeric($category)) ? "id = '".$category."'" : "name =
'".SQL_ESCAPE($category)."'" )." LIMIT 1");
if ($sth && SQL_NUM_ROWS($sth) == 1)
{
SQL_QUERY("DELETE FROM categories WHERE lft
BETWEEN @myLeft AND @myRight");

SQL_QUERY('UPDATE categories SET rgt = rgt -
@myWidth WHERE rgt > @myRight');
SQL_QUERY('UPDATE categories SET lft = lft -
@myWidth WHERE lft > @myRight');
}
}
else //move them up to the level of the deleted category
{
$sth = SQL_QUERY("SELECT @myLeft := lft, @myRight :=
rgt, @myWidth := rgt - lft + 1 FROM categories
WHERE ".(
(is_numeric($category)) ? "id = '".$category."'" : "name =
'".SQL_ESCAPE($category)."'" )." LIMIT 1");
if ($sth && SQL_NUM_ROWS($sth) == 1)
{
SQL_QUERY("DELETE FROM categories WHERE lft
= @myLeft");

SQL_QUERY('UPDATE categories SET rgt = rgt -
1, lft = lft - 1 WHERE lft BETWEEN @myLeft AND @myRight');
SQL_QUERY('UPDATE categories SET rgt = rgt -
2 WHERE rgt > @myRight');
SQL_QUERY('UPDATE categories SET lft = lft -
2 WHERE lft > @myRight');
}
}
}

SQL_QUERY('UNLOCK TABLES');
}

/**
* Inserts a new category to the right of an existing category
*
* @access public
* @return integer ID of the newely inserted category or false.
* @param string $newname the name of the new category.
* @param mixed $leftnode the name or ID of the immediate left
category in the sibline tree heirarchy.
* @author Daevid Vincent [daevid@daevid.com]
* @version 1.1
* @date 02/27/06
*/
function insert_category_after($newname = null, $leftnode = null)
{
//global $OPTION;
//$OPTION['debug'] = true;

if (is_null($newname) || is_null($leftnode)) return false;
if (is_numeric($leftnode) && $leftnode < 1) return false;

SQL_QUERY('LOCK TABLE categories WRITE');

if (is_leaf_node($leftnode))
{
$sth = SQL_QUERY("SELECT @myLeft := lft FROM categories
WHERE ".(
(is_numeric($leftnode)) ? "id = '".$leftnode."'" : "name =
'".SQL_ESCAPE($leftnode)."'" )." LIMIT 1");
if ($sth && SQL_NUM_ROWS($sth) == 1)
{
SQL_QUERY('UPDATE categories SET rgt = rgt + 2 WHERE
rgt > @myLeft');
SQL_QUERY('UPDATE categories SET lft = lft + 2 WHERE
lft > @myLeft');
SQL_QUERY("INSERT INTO categories(name, lft, rgt)
VALUES('".SQL_ESCAPE($newname)."', @myLeft + 1, @myLeft + 2)");
$id = SQL_INSERT_ID();
}
}
else
{
$sth = SQL_QUERY("SELECT @myRight := rgt FROM categories
WHERE ".(
(is_numeric($leftnode)) ? "id = '".$leftnode."'" : "name =
'".SQL_ESCAPE($leftnode)."'" )." LIMIT 1");
if ($sth && SQL_NUM_ROWS($sth) == 1)
{
SQL_QUERY('UPDATE categories SET rgt = rgt + 2 WHERE
rgt > @myRight');
SQL_QUERY('UPDATE categories SET lft = lft + 2 WHERE
lft > @myRight');
SQL_QUERY("INSERT INTO categories(name, lft, rgt)
VALUES('".SQL_ESCAPE($newname)."', @myRight + 1, @myRight + 2)");
$id = SQL_INSERT_ID();
}
}

SQL_QUERY('UNLOCK TABLES');
//$OPTION['debug'] = false;
return ($id > 0) ? $id : false;
}

/**
* Shows a tally of the number of products in each category
*
* @access public
* @return array
* @author Daevid Vincent [daevid@daevid.com]
* @version 1.0
* @date 02/23/06
*/
function get_category_product_tally()
{
return SQL_QUERY_ARRAY_PAIR("SELECT parent.name,
COUNT(products.name)
FROM
categories AS node, categories AS parent, products
WHERE
node.lft BETWEEN parent.lft AND parent.rgt

AND node.id = products.category_id
GROUP BY
parent.name
ORDER BY
node.lft");
}

/**
* Returns an array of all the categories defined
*
* @access public
* @return array
* @author Daevid Vincent [daevid@daevid.com]
* @version 1.0
* @date 02/23/06
*/
function get_categories()
{
return SQL_QUERY_ARRAY_PAIR("SELECT id, name FROM categories ORDER
BY name");
}

/**
* Returns an array of all the categories in the tree starting from a given
category
*
* @access public
* @return array
* @param string $name the name of the category to start with.
* @author Daevid Vincent [daevid@daevid.com]
* @version 1.0
* @date 02/23/06
*/
function get_category_tree($name = null)
{
if (is_null($name)) return false;

return SQL_QUERY_ARRAY_PAIR("SELECT node.id, node.name
FROM
categories AS node, categories AS parent
WHERE
node.lft BETWEEN parent.lft AND parent.rgt

AND parent.name = '".SQL_ESCAPE($name)."'
ORDER BY
node.lft");
}

/**
* Show the path of categories to a given node.
*
* @access public
* @return array
* @param mixed $category the string name of the category or its ID to
start with.
* @author Daevid Vincent [daevid@daevid.com]
* @version 1.0
* @date 02/23/06
*/
function get_path($category = 1)
{
return SQL_QUERY_ARRAY_PAIR("SELECT parent.id, parent.name
FROM
categories AS node, categories AS parent
WHERE
node.lft BETWEEN parent.lft AND parent.rgt
AND ".(
(is_numeric($category)) ? "node.id = '".$category."'" : "node.name =
'".SQL_ESCAPE($category)."'" )."
ORDER BY
node.lft");
}

/**
* Show the products of that category, as well as list its immediate
sub-categories,
* but not the entire tree of categories beneath it.
*
* @access public
* @return array
* @param mixed $category the string name of the category or its ID to
start with.
* @param boolean $showParent (false) Show the parent node.
* @author Daevid Vincent [daevid@daevid.com]
* @version 1.0
* @date 02/23/06
*/
function get_subcategories($category = 1, $showParent = false)
{
return SQL_QUERY_ARRAY_PAIR("
SELECT node.id, node.name,
(COUNT(parent.name) - (sub_tree.depth + 1)) AS depth
FROM categories AS node,
categories AS parent,
categories AS sub_parent,
(
SELECT node.id, node.name,
(COUNT(parent.name) - 1) AS depth
FROM categories AS node,
categories AS parent
WHERE node.lft BETWEEN
parent.lft AND parent.rgt
AND ".(
(is_numeric($category)) ? "node.id = '".$category."'" : "node.name =
'".SQL_ESCAPE($category)."'" )."
GROUP BY node.name
ORDER BY node.lft
)AS sub_tree
WHERE node.lft BETWEEN parent.lft AND
parent.rgt
AND node.lft BETWEEN sub_parent.lft
AND sub_parent.rgt
AND sub_parent.name = sub_tree.name
GROUP BY node.name
HAVING depth ".( ($showParent) ? '<=' :
'=')." 1
ORDER BY node.lft");
}

/**
* Returns an array of all the category names in the tree and their depths
*
* @access public
* @return array
* @param string $name the name of the category to start with.
* @author Daevid Vincent [daevid@daevid.com]
* @version 1.0
* @date 02/23/06
*/
function get_depths($name = null)
{
if (is_null($name))
{
return SQL_QUERY_ARRAY_PAIR("SELECT node.name,
(COUNT(parent.name) - 1) AS depth
FROM
categories AS node, categories AS parent

WHERE node.lft BETWEEN parent.lft AND parent.rgt

GROUP BY node.name

ORDER BY node.lft");
}
else
{
return SQL_QUERY_ARRAY_PAIR("
SELECT node.name,
(COUNT(parent.name) - (sub_tree.depth + 1)) AS depth
FROM categories AS node,
categories AS parent,
categories AS sub_parent,
(
SELECT node.name,
(COUNT(parent.name) - 1) AS depth
FROM categories AS
node,
categories AS parent
WHERE node.lft
BETWEEN parent.lft AND parent.rgt
AND node.name =
'".SQL_ESCAPE($name)."'
GROUP BY node.name
ORDER BY node.lft
)AS sub_tree
WHERE node.lft BETWEEN parent.lft
AND parent.rgt
AND node.lft BETWEEN
sub_parent.lft AND sub_parent.rgt
AND sub_parent.name =
sub_tree.name
GROUP BY node.name
ORDER BY node.lft");
}
}

/**
* Returns an array of all the leaf nodes
*
* @access public
* @return array
* @author Daevid Vincent [daevid@daevid.com]
* @version 1.0
* @date 02/23/06
* @see is_leaf_node()
*/
function get_leaf_nodes()
{
return SQL_QUERY_ARRAY_PAIR('SELECT id, name FROM categories WHERE
rgt = lft + 1');
}

/**
* Returns boolean if category is a leaf node
*
* @access public
* @return boolean
* @param mixed $category the string name of the category or its ID.
* @author Daevid Vincent [daevid@daevid.com]
* @version 1.0
* @date 02/23/06
* @see get_leaf_nodes()
*/
function is_leaf_node($category = null)
{
if (!is_null($category))
{
if ($sth = SQL_QUERY("SELECT id, name FROM categories WHERE
rgt = lft + 1

AND ".( (is_numeric($category)) ? "id = '".$category."'" : "name =
'".SQL_ESCAPE($category)."'" )))
return (SQL_NUM_ROWS($sth) == 1);
}

return false;
}

/**
* Rename a category
*
* @access public
* @return false if invalid input
* @param mixed $category the string name of the category or its ID.
* @param string $newname the name of the new category.
* @author Daevid Vincent [daevid@daevid.com]
* @version 1.1
* @date 03/01/06
*/
function rename_category($category = null, $newname = null)
{
if (is_null($category) || is_null($newname)) return false;

SQL_QUERY("UPDATE categories SET name = '".SQL_ESCAPE($newname)."'
WHERE ".( (is_numeric($category)) ? "id = '".$category."'" : "name =
'".SQL_ESCAPE($category)."'" )." LIMIT 1", true);
}

/**
* Prints out the tree with indentations in a very basic textual way
*
* @access public
* @return void
* @param integer $id the $id of the node to highlite
* @author Daevid Vincent [daevid@daevid.com]
* @version 1.1
* @date 02/27/06
* @see print_nested_tree_html()
*/
function print_nested_tree_txt($id = 1)
{
$sth = SQL_QUERY("SELECT node.id as id, CONCAT( REPEAT( ' ',
(COUNT(parent.name) - 1) ), node.name) AS name
FROM categories AS node, categories
AS parent
WHERE node.lft BETWEEN parent.lft
AND parent.rgt
GROUP BY node.name
ORDER BY node.lft");
if ($sth)
{
print "\n";
while($row = SQL_ASSOC_ARRAY($sth))
print (($row['id'] == $id) ? '['.$row['name'].']' :
$row['name']) . "\n";
}
}

/**
* Prints out the tree with indentations in a nice HTML way with hyperlinks
and highlight current category
*
* @access public
* @return void
* @param integer $id the $id of the node to highlite
* @author Daevid Vincent [daevid@daevid.com]
* @version 1.1
* @date 02/27/06
* @see print_nested_tree_txt()
*/
function print_nested_tree_html($id = 1)
{
$sth = SQL_QUERY("SELECT node.id as id, node.name AS name,
(COUNT(parent.name) - 1) as indent
FROM categories AS node, categories
AS parent
WHERE node.lft BETWEEN parent.lft
AND parent.rgt
GROUP BY node.name
ORDER BY node.lft");
if ($sth)
while($row = SQL_ASSOC_ARRAY($sth))
echo ($row['id'] == $id)
?
"".
str_repeat('  ', $row['indent']) . $row['name'] . ''
:
str_repeat('  ',
$row['indent'])."'
."
\n";
}
?>

------------------------------------->8 snip
8<--------------------------------

> -----Original Message-----
> From: Daevid Vincent [mailto:daevid@daevid.com]
> Sent: Wednesday, March 01, 2006 9:46 PM
> To: 'Php-Db'
> Subject: [PHP-DB] RE: [PHP] "Nested Set Model" or "modified
> preorder tree traversal" [SOLVED]
>
> Since I couldn't find any short and sweet drop in code for
> this, I made my
> own. Here it is in case anyone else finds it helpful. Should be pretty
> straight forward. I use my own mysql wrapper functions, but
> you can pretty
> much figure out what they do and S&R your own.
>
> Hopefully this works as an attachement.
>
> D.Vin
>
> > -----Original Message-----
> > From: Daevid Vincent [mailto:daevid@daevid.com]
> > Sent: Wednesday, February 22, 2006 12:42 PM
> > To: 'Curt Zirzow'; php-general@lists.php.net
> > Subject: RE: [PHP] "Nested Set Model" or "modified preorder
> > tree traversal"
> >
> > Peter Brawley on the mySQL list pointed me at:
> > http://www.artfulsoftware.com/mysqlbook/sampler/mysqled1ch20 .html
> >
> > Which uses mySQL 5's procedures and such.
> > I haven't implemented this yet, but it appears to be what I
> > wanted for the
> > most part.
> >
> > > -----Original Message-----
> > > From: Curt Zirzow [mailto:czirzow@gmail.com]
> > > Sent: Tuesday, February 21, 2006 10:58 PM
> > > To: php-general@lists.php.net
> > > Subject: Re: [PHP] "Nested Set Model" or "modified preorder
> > > tree traversal"
> > >
> > > On Tue, Feb 21, 2006 at 09:38:53PM -0800, Daevid Vincent wrote:
> > > > I've been searching the web for the past few hours trying
> > > to find a simple
> > > > drop-in class or functions to implement "Nested Set Model"
> > > or "modified
> > > > preorder tree traversal".
> > >
> > > I actually fell in love of this method of doing trees when I
> > > discovered it about a year ago, when researching a fast way to do
> > > a parent child retrieval.
> > >
> > > >
> > > > I've found several tutorials and related links:
> > > >
> > > >
> > >
> http://dev.mysql.com/tech-resources/articles/hierarchical-da ta.html
> > > > http://www.sitepoint.com/article/hierarchical-data-database
> > > >
> > > >
> > > http://www.zend.com/zend/tut/tutorial-ferrara2.php?article=t ut
> > > orial-ferrara2
> > > > &id=3453&open=1&anc=0&view=1
> > > > http://simon.incutio.com/archive/2003/06/19/storingTrees
> > > > http://istherelifeafter.com/joecelko.html
> > > >
> > http://www.codeproject.com/cs/database/Trees_in_SQL_database s.asp
> > >
> > > oh i have to add some of these to my list, i didn't have a couple
> > > of them.
> > >
> > > >
> > > > I even found some outdated PEAR DB_NestedSet class from
> > > 2003 that seemed way
> > > > to overly complicated. And some other PHP4_Mysql3MPTTA
> > > class on PHP Classes
> > > > that was equally complicated and written for ancient PHP4
> > > and mySQL 3!!
> > >
> > > yeah, i kind of got that feeling with the PEAR class as well, i
> > > think it was designed that way to be as flexible as possible. I'm
> > > not familiar with the other class, i tend to avoid anything from
> > > PHP Classes.
> > >
> > > >
> > > > Hasn't anyone else done this before (recently)?
> > > > Did everyone just write their own code every time?
> > > > Anyone have some easy to use code that allows for:
> > > > Add, delete, update/rename, select, show tree, bread crumb
> > > display, etc.
> > >
> > > It would be nice to have a tool to manage the functionality. There
> > > will always be the issue with any common tool that is built for
> > > this purpose is how do you relate the table that defines
> the nested
> > > with the table that actually holds the data.
> > >
> > > I believe that why the PEAR and other classes you came across
> > > seemed to be overly complicated. I have found that it is
> easier to
> > > write my own management (as awful as that sounds) for the job at
> > > hand.
>
>
>

--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php