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:mattias@inreach.com] 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 name data type/db flags Coments ================================================================= 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