Need ideas on how to loop through a database table based on coreesponding Id

[MockY]

The title is not really fitting but I have no idea how to word it appropriately. I'm building a photo gallery and I need to populate a drop down with folders that I can move the selected picture or folder to.

 

I have a table with 3 fields, Id, ParentFolderId, Name. For demostration purposes, lets say it has these values:

 

+----+----------------+--------+
| Id | ParentFolderId | Name   |
+----+----------------+--------+
|  1 |              0 | 2011   |
|  2 |              1 | Flowers  |
|  3 |              1 | Misc   |
|  4 |              5 | Computers |
|  5 |              0 | 2010   |
|  6 |              5 | Fishing |
|  7 |              6 | Europe |
+----+----------------+--------+

I want to echo it out like this

 

2010

2010 : Computers

2010 : Fishing

2010 : Fishing : Europe

2011

2011 : Flowers

2011 : Misc

 

I have been toying around with some ideas, both for mysql and php,  but none of them really does what I want. So now I would like some help in order to solve this. One folder could potentially have 10 or even 15 subfolders and not just three levels as I've shown here.

[Psycho]

Is there a limit to the number of levels you will allow?

 

EDIT: Scratch that, I see you stated that. There are resources out there for doing mult-level  hierarchical results such as this. Let me see what I can dig up.

[xyph]

Code:

 

<?php

$sql = new MySQLi( 'localhost','root','','db');

$query = "SELECT `id`, `parent`, `name` FROM `folders`";
$result = $sql->query( $query );
$arr = $result->fetch_all( MYSQLI_ASSOC );
$result->free();

print_r( makeTree($arr) );

function makeTree( $results, $parent = 0 ) {

$r = array();
foreach( $results as $result ) {
	if( $result['parent'] == $parent ) {
		$r[] = $result['name'];
		$children = makeTree( $results, $result['id'] );
		if( !empty($children) ) $r[] = $children;
	}
}

return $r;

}

?>

 

Output

 

Array
(
    [0] => 2011
    [1] => Array
        (
            [0] => Flowers
            [1] => Misc
        )

    [2] => 2010
    [3] => Array
        (
            [0] => Computers
            [1] => Fishing
            [2] => Array
                (
                    [0] => Europe
                )

        )

)

[Psycho]

xyph posted a good solution, but I have an alternative that may be much easier.

 

I assume that these records are getting created through some manner that reads the file structure, so it should be fairly simple to determine the full path for each folder from the parent. If so, then just modify your table to include the full path for each folder as follows:

 

+----+----------------+-------------------------------+
| Id | ParentFolderId | Name      | FullPath
+----+----------------+-------------------------------+
|  1 |              0 | 2011      | 2011
|  2 |              1 | Flowers   | 2011/Flowers
|  3 |              1 | Misc      | 2011/Misc
|  4 |              5 | Computers | 2010/Computers
|  5 |              0 | 2010      | 2010
|  6 |              5 | Fishing   | 2010/Fishing
|  7 |              6 | Europe    | 2010/Fishing/Europe
+----+----------------+--------------------------------+

 

Then you can get the results in the proper order using

SELECT * FROM table
ORDER BY FullPath

 

And creating the options as you requested is a trivial task.

$selectOptions = '';
while($row = mysql_fetch_assoc($result))
{
    $label = str_replace("/", " : ", $row['FullPath']);
    $selectOptions .= "<option value=\"{$row['Id']}\">{$label}</option>\n";
)

[MockY]

Thanks you both so much for helping me think outside the box.

 

mjdamato, your solution is overall much simpler to implement and understand, but if I changed the table structure, I would have to change much of my code. Not a big deal however, but I rather not do so. However, your solution makes more sense to me and I will probably go with that if I don't get xyph's solution up and running.

 

I have yet to move over to a OOP approach when it comes to interacting with the database (habit I guess), and when trying to apply the code, I receive the following error

Call to undefined method mysqli_result::fetch_all()

So now I'm trying to make it work with the good ol'

$query = "SELECT Id, ParentFolderId, Name FROM folders";
$result = mysql_query($query);

and then store the result with mysql_fetch_assoc($result);

 

So yeah, I will probably have to bite the bullet and change the database around, but I'll give it some more minutes.

 

Again, thanks for the input.

 

[Psycho]

mjdamato, your solution is overall much simpler to implement and understand, but if I changed the table structure, I would have to change much of my code.

 

I have no investment in that solution, but I don't see that it would require much effort. You would only need to add the column and update the code that is used to add/edit those records to include that additional piece of data. Since this code we are talking about is the only code that would use the full path value none of the other code that may use those records would need to be changed.

[MockY]

You are correct. I jumped the gun a little 

Yeah, I'm going with your solution. It really made it much easier. Thank you very much.

Just feel bad xyph spend time creating one as well....

[xyph]

That's a standard function I always keep close by.

 

Two or three changes and it was pasted for ya.

[MockY]

That's a standard function I always keep close by.

 

Two or three changes and it was pasted for ya.

Either way, I'm grateful for your interest in solving my problem.