Re: [PHP] Hierarchies and MySQL with PHP
Warren Vail wrote: I did one once where the key to the table was a string, and the string contained 1 to n Node Numbers separated by a separator character. 1 1.1 1.1.1 1.2 select data from table where node between (1 and 2) resulted in an entire limb of the tree being retrieved. Limitations were the size of the string, depth of the tree (the string was truncated), and the number of digits in each node number. Problem also with ordering node numbers, node number 1 tended to be followed by node number 10, 11, 12, etc, then number 2, until I pre-determined the number of leading zeros for each node. Not pretty, but it works well for small trees. Warren Vail Been there, done that, ran into the same limitations :-) The flat table solution in the article suggested by Marek keeps a running display order integer column, updated only when items are added (or removed, but that's not strictly necessary if you don't mind holes in the sequence). When inserting a new item, it gets the display order value of the parent + 1, and the following items have their display order incremented by 1. Simple. There's also an indentation level column, which is simple enough to maintain. /Mattias -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP] Hierarchies and MySQL with PHP
Hi, I wonder what you think are the best (or least worst) strategies to store and retrieve hierarchial data (such as a threaded discussion or a multi-level menu tree) in MySQL using PHP? I have been using table structures where each row contains a parent reference, such as: Table Example: Field namedata type/db flagsComents = RowID int unsigned auto_increment not null (primary key) ParentRowID int unsigned 0 (or NULL if at top level) Name varchar(50) ... which is OK for *defining* the hierarchy. However, it's a pain to retrieve the data so that it can be displayed in a nice threaded/sorted way, where children are sorted directly below their parents. I also want the items to be nicely sorted within their own branch, of course. On MS SQL, I successfully used stored procedures that employ temporary tables and while statements and the like. That method is not available in MySQL (yet), so I'll have to do a lot of the manipulation on the web server instead, using PHP. Any suggestions? /Mattias -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Hierarchies and MySQL with PHP
This should be of your interest: http://www.evolt.org/article/Four_ways_to_work_with_hierarchical_data/17/4047/ Mattias Thorslund wrote --- napísal:: Hi, I wonder what you think are the best (or least worst) strategies to store and retrieve hierarchial data (such as a threaded discussion or a multi-level menu tree) in MySQL using PHP? I have been using table structures where each row contains a parent reference, such as: Table Example: Field namedata type/db flagsComents = RowID int unsigned auto_increment not null (primary key) ParentRowID int unsigned 0 (or NULL if at top level) Name varchar(50) ... which is OK for *defining* the hierarchy. However, it's a pain to retrieve the data so that it can be displayed in a nice threaded/sorted way, where children are sorted directly below their parents. I also want the items to be nicely sorted within their own branch, of course. On MS SQL, I successfully used stored procedures that employ temporary tables and while statements and the like. That method is not available in MySQL (yet), so I'll have to do a lot of the manipulation on the web server instead, using PHP. Any suggestions? /Mattias -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
RE: [PHP] Hierarchies and MySQL with PHP
I did one once where the key to the table was a string, and the string contained 1 to n Node Numbers separated by a separator character. 1 1.1 1.1.1 1.2 select data from table where node between (1 and 2) resulted in an entire limb of the tree being retrieved. Limitations were the size of the string, depth of the tree (the string was truncated), and the number of digits in each node number. Problem also with ordering node numbers, node number 1 tended to be followed by node number 10, 11, 12, etc, then number 2, until I pre-determined the number of leading zeros for each node. Not pretty, but it works well for small trees. Warren Vail -Original Message- From: Mattias Thorslund [mailto:[EMAIL PROTECTED] Sent: Sunday, June 27, 2004 9:59 AM To: PHP General Mail List Subject: [PHP] Hierarchies and MySQL with PHP Hi, I wonder what you think are the best (or least worst) strategies to store and retrieve hierarchial data (such as a threaded discussion or a multi-level menu tree) in MySQL using PHP? I have been using table structures where each row contains a parent reference, such as: Table Example: Field namedata type/db flagsComents = RowID int unsigned auto_increment not null (primary key) ParentRowID int unsigned 0 (or NULL if at top level) Name varchar(50) ... which is OK for *defining* the hierarchy. However, it's a pain to retrieve the data so that it can be displayed in a nice threaded/sorted way, where children are sorted directly below their parents. I also want the items to be nicely sorted within their own branch, of course. On MS SQL, I successfully used stored procedures that employ temporary tables and while statements and the like. That method is not available in MySQL (yet), so I'll have to do a lot of the manipulation on the web server instead, using PHP. Any suggestions? /Mattias -- 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] Hierarchies and MySQL with PHP
Marek Kilimajer wrote: This should be of your interest: http://www.evolt.org/article/Four_ways_to_work_with_hierarchical_data/17/4047/ Indeed! The flat table model is simple, efficient and - I think - sufficient. Thanks! I also found this article which explains the fourth method not really described above, despite the title: http://www.sitepoint.com/article/hierarchical-data-database/ That method is a bit more complicated but it also allows you to determine the (total) number of child nodes of a node, without an extra SQL query. /Mattias -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
