Tree view &amp; MySQL please..

Tree view & MySQL please..

lordgreg

Hello all..

I have (for me HARD) very easy problem - mathematical? It is about MySql db
table. I have a table main with 3 columns:
1st col: ID
2nd col: ID_PARENT
3rd col: NAME

now.. here is an example:

ID ID_PARENT NAME
1 0 Web space
2 0 E-mail
3 1 Free
4 1 Payed
5 2 POP3
6 5 Software
7 2 Web based

i just want to make all posible tree structures. for my problem that are:

Web space
Web space -> Free
Web space -> Payed
E-mail
E-mail -> POP3
E-mail -> POP3 -> Software
E-mail -> Web based
...

I just want all tree structures to be printed on the page
I think.. this is not hard, but i don't get it.. i don't know what to do..
if you did understand my question.. please help me
(i hope you get the problem from my table)

ps: yes, i did searched the forums and found a forum just like mine but didn't get any help out.. thank you all

thank you very much..

Anon

Go to nexen.net and take all the function for the tree.

shivers

You need to write a function and call it recursively to build the tree. Something like this should do the job:

(assumes $dblink = your mySQL connection ID)

function go($parentID) {
global $dblink;

$q = "SELECT * FROM table WHERE parent=$parentID";
$qr = mysql_query($qr, $dblink);

while ($qdata = mysql_fetch_array($qr)) {

echo $qdata["NAME"];

go($qdata["ID_PARENT"]);

};

}

Then call it like this;

go(NULL);

(obviously, replace the NULL with zero if your ID fields are defined NOT NULL)

Format the output as you see fit and off you go 😉

yelvington

It's actually pretty easy. Write a recursive function that takes an ID as an argument. The function should build a (global) string, working right to left. If the parent ID is zero, then you have a root item and the string should be printed. If the parent ID isn't zero, the function calls itself and passes the parent ID as an argument.

Essentially you're printing "bread crumb trails" for each object in your database. Here is a function that I use in my message board system:

// Print a breadcrumb trail from 0 to this location $id
function breadcrumbs($id)
{
global $roomid;
global $db;
global $prattle_rooms;
global $breadcrumbtrail;
$result = mysql_db_query($db, "select * from $prattle_rooms where
roomid='$id'");
$room = mysql_fetch_object($result);
if ($id == $roomid)
$breadcrumbtrail = $room->title;
else
$breadcrumbtrail = "<a href=\"prattle.php?roomid=$room->roomid\">$room->title</a> > $breadcrumbtrail";

    if ($room->roomparent) breadcrumbs($room->roomparent);

    return $breadcrumbtrail;
    }

And the result looks something like this:

Lobby > Geekstuff: Talk about Linux, PHP, MySQL, et cetera > PHP-Flash-talk

Anon

This doesnt resolve the problem, if you have BIG tree: You need to call that recursive function (from which each makes one query) y times where y is the number of rows.

If you have for example table with 1000 rows and the figure of tree is like "stairs": theoretically there should run 1000 copies of the recursive function of the same time and they make 1000 queries! So it is not very resource-saving solution.

Im facing with the abovementioned problem right now. I have feeling that sorting can be made with sql tools, and whole problem could be solved with one query, but it is still feeling...

linuxer

Dear Shane,

Thanks for this code . It saved me a lot of trouble.

Once again thanks !