Recursive fonction (parent, child + MYSQL select)

For PHP gurus

[tomda]

Hi guys,

My database is the taxonomic classification of animals found in a particular region.

ID - CATEGORY - NAME - PARENT
******************************
1 - KINGDOM - ANIMALS - 0
2 - CLASS - MAMMALS - 1
3 - CLASS - BIRDS - 1
etc...

Categories can be 'kingdom', 'phylum', 'subphylum', 'superclass', 'class', 'subclass', 'superorder', 'order', 'suborder', 'superfamily', 'family', 'subfamily', 'genus', 'species', 'subspecies', so the tree-structure is deep and the database is large (2300 records).

I want to create a BASIC recursive function in PHP+MYSQL and generate a tree structure which will STOP at "species".

Any tutorial I should see or script I could use to start my project.

Thanks

[mcibor]

There are two ways of doing that: get all results and then recurse, or recurse getting results. I think the latter is easier, but first is faster (just one db querry).

I would recommend writing the desired view on a piece of paper and then checking how can you do it.

Best regards
Michal Cibor

[edited by: ergophobe at 6:11 pm (utc) on April 1, 2005]

[tomda]

Thanks mcibor,

Very hard to get good recursive script on G, luckily I found after three hours of intense search, two basic scripts...

I must admit recursive function, which seems very simple and used by many websites, is new to me. The worst is the array and how to create the tree structure from the array (I hate arrays).

Thanks

[edited by: ergophobe at 6:13 pm (utc) on April 1, 2005]

[tomda]

Just because I don't like to left my thread unfinished and also, because I had difficult time to find a good tutorial (in PHP+MySql) about recursive function, allow me to drop one basic example and list other possibilities.
**************************
For those looking for a ressource to know the BASIC about recursive function or other possibilities to create a tree structure from a simple id-parent relationship, type "recursive fruit red yellow function" in Google.

There are two (three?) methods :
1/ Recursive function : pretty simple, just one query in a recursive function (that is a function which calls itself). Disadvantage is the memory, specially if you have many many levels (like in my table in post#1).
2/ Preorder tree traversal : complicated unless you are used to it, but once you understand how it works, you can do and output whatever you want... The *ick is that you need to draw the tree structure on paper and allocate numbers at the left and the right of each box (see reference). Drawing my tree structure with 2300 records, sorry I can't!
3/ It seems there is another possibility which is to use another coding system to establish the relation between parent and child, something which looks like 1-2-3 (third subsublevel of the second sublevel of the 1st category) or 1-2-15 (fifteen subsublevel of the second level of the 1st category), etc... This should work I guess.

So, you will below an example of a basic recursive function and another example showing to can use CSS to have a tree-like structure.

The basic function :

// *******************
// THE FUNCTION ITSELF
// *******************
function recursive_function($parent=null) {
// USEFUL IF YOU WANT TO STOP AT A CERTAIN LEVEL
$i=1;

//DO THE SIMPLE QUERY - PARENT VALUE
$requete = "SELECT id,category,name,parent FROM table WHERE parent";
if ($parent == null) {$requete .= " is null ";} else {$requete .= " ='$parent' ";}
$res=mysql_query($requete) or die ("Fail recursive");

// OUTPUT THE NAME
while ($row=mysql_fetch_array($res)) {echo "".strtoupper($row['name']."<br>";

//RETRIEVE ID AND CALLS THE RECURSIVE AGAIN OR STOP IT
// YOU CAN STOP AT A CERTAIN LEVEL (HERE 4)
if ($i!="4") {recursive_function($row['id']);}
// OR WHEN THE CATEGORY HAS A CERTAIN VALUE
//if ($row['sup_cat']!="species") {recursive_function($row['id']);}

// INCREMENT YOUR LEVEL
++$i; }}

// ****************
// RUN THE FUNCTION
// ****************
$parent="10";
print_r (recursive_function($parent));

Hope this will help someone...

Now the trick to have a sample of a tree structure is to use CSS.

//ADD THIS TO YOUR STYLESHEET (A DIFFERENT MARGIN FOR EVERY LEVEL/CATEGORY
<style type="text/css">
.kingdom {margin-left:5px;}
.phylum {margin-left:20px;}
.subphylum {margin-left:35px;}
.superclass {margin-left:50px;}
.class {margin-left:65px; }
.subclass {margin-left:80px;} //etc...
</style>

/* TO BE REPLACED IN YOUR FUNCTION */
// OUTPUT THE NAME USING CSS
while ($row=mysql_fetch_array($res)) {echo "<span class='".$row['category'].">".strtoupper($row['name']."</span><br>";

Note that I have use category name in my style sheet, but you can easily use the $i variable (e.g .style1, .style2, .style3, etc...)

OK, that's it! Don't forget you can add a switch statement in your function to show or not show certain levels/categories. But because my post is already long enough, you should be able to do it by yourself.

Any questions, remarks/errors and improvements are welcome.