Find subfolders in database

classy80

I have table in a database containing posts with folders. Each post contains following columns:

id = identification
parent = the folder current folder is in
name = the name of the folder

Is there anyone who knows how to write a function that returns all the subfolders that is in a folder. The functioncall should look like this:

$subfolders = getsubfolders($folderid);

I think it should be a recursiv function;

Best regards// Andreas

the_Igel

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

classy80

Thanks for the help, I will try that! 🙂

daveyboy

Getting all the children is easy, but with a similar table, how would you get an ascending hierarchy back to the top level folder - like the navigation at the top of each page:
PHP Builder Community Forums > PHP Help > Coding > Find subfolders in database

I've been scratching my head over this for a bit now. Is there a fast way to do this other than looping thru successive results and using a shed-load of queries / getting a list of all categories?

There must be a more efficient way to do this.

the_Igel

In case the only information you got is the ID of the parent, then the only way to get all the ancestors is to iterate get_parent() function, which is evident.

But you can invent more effective structure to speed it up. You should store more information in the table to make it faster.

Say, you're sure that you have no more then 2¹⁶ categories in every level of hierarchy. Then you can store all the hierarchy in one field, having first two bytes for the top-level ancestor, next two - for its child, and so on down to the current element, and zerofill since.
Then you would be able to retrieve all the hierarchy with one query.

daveyboy

I'd mulled that one over, too, or something along those lines - i.e. storing the full path to each section along with it, but it seemed a little clumsy, sort of against the principles of db's where you should only put one bit of data in each field.

How do dmoz, yahoo! and the ilk do it? It appears they parse the URL and query the db using the hierarchy in the URL. Just a guess. Doesn't seem to beat the recursive querying, though.

Hmm. I suppose yours is the way to go, Igel. Thanks for the suggestions.

the_Igel

To be faster, you have to have some extra information. If you want to get all the ancestors with one query, you should know, what they are. Either you store all ancestors' id in every record, or you get their names from URL, or whatever.
I think that one single, however great, number for identificating record position in the hierarchy, isn't high price.

daveyboy

I had similar misgivings over adding an arbitrary amount of hidden entries to a form with javascript at runtime. Ultimately, I did it your way and concat'ed them all together in one input. Not so nice in theory, but the fastest, easiest way in practice. And that's what counts, I suppose.

Thanks for your ideas, Igel.