RE: hierarchical mysql-records

---------------------------------------------------------------------------- ----------------- Disclaimer: The information contained in this email is intended only for the use of the person(s) to whom it is addressed and may be confidential or contain legally privileged information. If you are not the intended recipient you are hereby notified that any perusal, use, distribution, copying or disclosure is strictly prohibited. If you have received this email in error please immediately advise us by return email at postmaster@normandy.com.au and delete the email document without making a copy. ---------------------------------------------------------------------------- ----------------- I dont think MySQL support tree walking in SQL. (Oracle does though ;) The four choices you have are; 1. Decide on a limit of how many levels then alias the same table that many times. This isnt nice as you will be limited to x levels. (Make sure you outer join if you do this) 2. Have a query that find the children of parent then simply recursively find the children with the same query.. this could be nasty as you are firing off 1000's of queries 3. Mangle your database to include information about all children. So that you have an entry for each parent which then lists all of its children... you'll have to maintain this somehow using methods like 1,2, or 4 but it would make for fast searching at run time. 4. Dump the whole parent / child relationship table in one query to a 2D array, then tree walk through the array. This would be recommended. Assuming you can have a query like "SELECT PARENT_ID,CHILD_ID FROM RELATIOSHIPS_TABLE" it should be managable. Ive attached a very simple example of recursive function calling in the attached .php file.. (be warned i wrote it a while ago and may not be optimised) Id go for no 4 as the other methods are pretty painful and resource consuming. The attached example may be improved by not passing the $myarray each time but making it a global variable (booo hiss) or passing by reference. Good luck, mn -----Original Message----- From: ott89@t-online.de [mailto:ott89@t-online.de] Sent: Tuesday, October 10, 2000 8:20 PM To: php-general@lists.php.net Subject: hierarchical mysql-records I have a masql-database with records in a parent-child-structure. What I need to do is have a result-list with klinks not to a record but to all child-records of this specific entry. Its a directory system type of thing. Can anybody recommend a freeware-sample-script where I can learn more about doing this in my own scripts? Thanks Helmut