hierarchical directory database

[csevey]

Here's the situation.

I have a database full of directory information, structured like this:

ID PID Name

The ID is a unique ID to that directory, the PID is the ID to that of its parent directory, the name is the name of the directory that gets displayed.

So a few samples would be like this

ID PID Name
0 0 Root
1 0 Directory1
2 0 Directory2
3 1 Directory3
4 3 Directory4

From a hierarchical point of view, it would look like this.

Root
....Directory1
........Directory3
............Directory4
....Directory2

The problem: Now, I am trying to figure out how to write a part of the script where when you delete one directory, every subdirectory also gets removed.

So if I delete Directory1, Directory3 and Directory4 also should be removed from the database.

So if you delete ID1, you can:
Delete entry where ID == 1
Delete entry where PID == 1

This will still still leave the 4th directory though. So obviously I'll need some type of loop statement that will travel up and down the directory system and remove un-needed directories.

Can anyone lend me a hand?

Thanks,

CSevey

[keith73]

Try this:

<?php
//use 4 as for the test, most likely the id will be past through _GET or _POST
$id = 4;
$dirList = array();
$dirList = getChildren($id);
//place the original id # in the array.
array_unshift($dirList,$id);

/*****************************************
function    : getChildren
description : get all children below the parent pid
return value: ARRAY
parameters  : 1
    pid = INT, parent id
*****************************************/

function getChildren($pid)
{
$children = array();
$query = "SELECT ID FROM categories WHERE PID=$pid";
$result = @mysql_query($query);
if ( $result ){
    while ( $row = mysql_fetch_object($result) ){
        $children = array_merge($children, getChildren($row->ID));
    }//end while
    return($children);
}//end if

}//END function getChildren

it returns only the list of ID #'s.

• keith

[csevey]

Keith,

Thanks for your help. I have the following code here

<?php 

/***************************************** 
function    : getChildren 
description : get all children below the parent pid 
return value: ARRAY 
parameters  : 1 
    pid = INT, parent id 
*****************************************/ 

function getChildren($pid) 
{ 
$children = array(); 
$query = "SELECT ID FROM dir WHERE PID=$pid"; 
$result = mysql_query($query) or die("Query failed : " . mysql_error());
if ( $result ){ 
    while ( $row = mysql_fetch_object($result) ){ 
        $children = array_merge($children, getChildren($row->ID)); 
    }//end while 
    return($children);
}//end if

}//END function getChildren 





$link = mysql_connect("localhost", "root", "")
  or die("Could not connect : " . mysql_error());
  print "Connected successfully";
  mysql_select_db("ea_directory") or die("Could not select database");


//use 2 as for the test, most likely the id will be past through _GET or _POST
$id = 2;
$dirList = array();
$dirList = getChildren($id);
//place the original id # in the array.
array_unshift($dirList, $id);

$IDs = implode(" ", $dirList);

echo $IDs;

?>

Basicly I just altered it to conform to my testing database, and add a line to implode the array and print it, however I cant seem to get it to display anything other then the original ID that was entered.

Am I doing something wrong here?

Thanks,

CSevey

[keith73]

sorry for the late reply.
I didn't check the forums over the weekend.

Basicly I just altered it to conform to my testing database, and add a line to implode the array and print it, however I cant seem to get it to display anything other then the original ID that was entered.

Am I doing something wrong here?

looks like the code you added is ok.
Did you check to see that the function getChildren actually returns valid results to the array?

try adding this line after the function is called.

print "results: " . count($dirList) . "<br>\n";
print "values: " . join(",",$dirList) . "<br>\n";

that will tell you if the array was populated with data and what the array contains.

I will look at the function tonight and test it.

• keith

[csevey]

I added those two lines.

Connected Successfully
Results: 1
Values: 2

So the original ID is getting added to the array ok. However there must be something wrong in the function and subdirectories are not getting added correctly.

I've been playing with it here and there, however I am still a bit of a noob at PHP yet so I havnt been able to put my finger on it yet.

Thank,

CSevey

[keith73]

ok, forget that first function I posted.
I got this function from either this site or from alt.php. The one I posted previously was a modification of this function that I had used elsewhere, but I think I posted an older, non-working version of it.

this function here definitely works. Just modify the query statement to your liking.

I'm sorry but I don't remember who the original author was that posted this.

function children($id)

{
$folders = array($id);
$r = mysql_query("select id from categories where parent=$id");
if (mysql_num_rows($r))
while ($row = mysql_fetch_array($r))
   $folders = array_merge($folders, children($row[0]));
return $folders;
}

• keith

[csevey]

Perfect.

Connected Successfully

Results: 5
Values: 2,5,7,6,8

Thanks for all your help. Appreciate it.

CSevey