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: http://stackoverflow.com/questions/5399288/mysql-recursive-query-replacement-in-php — , Mar 23 '11 at 00:34

score 6 · Accepted Answer · answered Mar 23 '11 at 00:42

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 '11 at 00:42

Michael McTiernan

5,153
2
25
16

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 ?? – numerical25 Mar 23 '11 at 02:38
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. – Michael McTiernan Mar 23 '11 at 02:49
I am thinking probably 1000 work orders a year. 3 or 4 folders per work order 6 files on average per work order. – numerical25 Mar 23 '11 at 03:09
And how do you plan to display this list? Does it make sense to only select one level at a time? – Michael McTiernan Mar 23 '11 at 03:11
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;' – numerical25 Mar 23 '11 at 12:05

score 0 · Answer 2 · answered Mar 23 '11 at 00:34

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

Understanding Recursion in PHP/mysql

2 Answers2