Understanding Recursion in PHP/mysql

Question

I am trying to create and understand recursion in php/mysql

my database looks something like the following

id | woid | parent | name
-------------------------------
1  | 1111 | 0      | pdf
2  | 1111 | 0      | docs
3  | 2222 | 0      | pdf
4  | 2222 | 0      | docs
5  | 3333 | 0      | pdf
6  | 1111 | 2      | folder1
7  | 2222 | 4      | folder3
8  | 3333 | 5      | folder

I would like to have it where if there are tons and tons of parent folders, it will be able to go through all of them. how do i go about doing this.

looks like this question posted not long ago: stackoverflow.com/questions/5399288/… — user557846
– user557846, Commented Mar 23, 2011 at 0:34

Michael McTiernan · Accepted Answer · 2011-03-23 00:42:25Z

6

In order to get SQL to return a more tree-like structure would actually be pretty difficult. Instead, it probably makes more sense (and you'd have a LOT more control over your output) to reorganize the SQL results using PHP.

SELECT id, woid, parent, name FROM files ORDER by parent ASC;

This will get us everything. Then we iterate through the resultset, storing the result into an array keyed by parent.

$folders = array();
foreach ($resultset AS $row) {
    $row['id'] = (int)$row['id'];
    $row['parent'] = (int)$row['parent'];
    if (!$folders[$row['parent']]) $folders[$row['parent']] = array();
    $folders[$row['parent']][] = $row;
}

Then we can output this array recursively:

function buildTree($inputArray, $parent = 0) {
    $return = array();
    foreach ($inputArray[$parent] AS $key => $row) {
        if ($inputArray[$row['id']]) {
            $row['children'] = buildTree($inputArray, $row['id']);
        }
        $return[] = $row;
    }
    return $return;
}

$myStuff = buildTree($folders);

answered Mar 23, 2011 at 0:42

Michael McTiernan

5,3132 gold badges27 silver badges16 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

numerical25 Over a year ago

Hey Michael, that sounds like a great idea. What I am initially trying to do is add create folders per Work Order. so woid represents the Work Order ID. What if there are TONS of work orders. would this get slow ??

Michael McTiernan Over a year ago

I have no idea what "TONS" means. I work for a large social network. TONS to me means >100,000,000 rows. If your dataset is truly huge, it would make sense to re-evaluate what you're doing.

numerical25 Over a year ago

I am thinking probably 1000 work orders a year. 3 or 4 folders per work order 6 files on average per work order.

Michael McTiernan Over a year ago

And how do you plan to display this list? Does it make sense to only select one level at a time?

numerical25 Over a year ago

I would like to display only directories within a particular Work Order. Each work order has its own directory So I could probably still do what you did. just change the query to 'SELECT id, woid, parent, name FROM files WHERE woid=xxxx ORDER by parent ASC;'

Wilbo Baggins · Accepted Answer · 2011-03-23 00:34:04Z

0

If you are looking for an efficient way to find all parent nodes (or the super parent node), given a node deep down in your hierarchy, you are entering the realm of graph theory, which comes with many own challenges, especially when the structure of your graph is represented in a single table such as in your own case.

Check out this resource for a good overview: http://www.artfulsoftware.com/mysqlbook/sampler/mysqled1ch20.html

answered Mar 23, 2011 at 0:34

Wilbo Baggins

2,7513 gold badges26 silver badges40 bronze badges

Collectives™ on Stack Overflow

Understanding Recursion in PHP/mysql

2 Answers 2

5 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

5 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related