Recursive tree traversal with mysql through PHP

Question

I am creating a questionnaire for a client that requires the questions to be organized by 3 layers of levels. I've successfully created the U.I. however I've been trying for the last 3 hours to pull data from a database in such a way that everything loads in the right place. The database is organized like so by the client so I have no control over it:

id    description    parentId    
1      Level 1        0           
2      Level 2        0           
3      Level 1a       1   
4      Level 1b       1 
5      Level 1a1      3

I have found a similar question to mine on the site but when I attempted it's solution I got the following on repeat infinetly:

Code:

function makeList($par_id = 0) {
    //your sql code here
    $result = mysql_query("SELECT * FROM pB_test WHERE parentId = $par_id");
    $pages = mysql_fetch_array( $result );

    if (count($pages)) {
        echo '<ul>';
        foreach ($pages as $page) {
            echo '<li>', $page['description'];
                makeList($page['parentId']);
                echo '</li>';
        }
        echo '</ul>';
    }
}

makeList();

Output:

Does anyone know how to fix this and what the issue is exactly? Cheers

MySQL does not support recursive `JOIN`s, which is what you really need, however if you know you only have three levels and will only ever have three levels, you can just left join the table onto itself three times — DaveRandom, Jun 12 '12 at 09:44

score 8 · Answer 1 · answered Mar 09 '13 at 05:30

8

it's not good to call mysql server and fetch result each time

what if you have over 100 rows? or 200+

use this to query only once:

$result = mysql_query("SELECT * FROM test");
$arrs = array();

while ($row = mysql_fetch_array($result, MYSQL_ASSOC)) {
    $arrs[] = $row;
}

function build_tree($arrs, $parent_id=0, $level=0) {
    foreach ($arrs as $arr) {
        if ($arr['parent_id'] == $parent_id) {
            echo str_repeat("-", $level)." ".$arr['name']."<br />";
            build_tree($arrs, $arr['id'], $level+1);
        }
    }
}

build_tree($arrs);

common example for table

  id    name    parent_id

answered Mar 09 '13 at 05:30

vladkras

16,483
4
45
55

1

You can put `global $arrs;` (instead of passing it as a parameter) inside the function. It wont be a black box any more but you will use much less memory. Also use mysqli instead of mysql. Anyway I like it. – Falk May 02 '14 at 12:11
This will always fetch the complete table, potentially using Terabytes of RAM - no possibility to fetch a subtree, – Eugen Rieck May 07 '16 at 12:18
@EugenRieck, just add `WHERE parent_id=$parent_id` clause to your query and 2nd param to `build_tree($arrs, $parent_id)` call. It's obvious that it's not suitable for big data, but will you wrap **terabytes** of results in `
- ` as OP wants?
– vladkras May 10 '16 at 09:21
Exactly. I would never wrap terabytes into `
- `, but I would put terabytes into a DB. This broken code will always fetch all rows, so the answer to `what if you have over 100 rows? or 200+` is easy: Definitly not this, as it will break horribly on any sizeable tree.
– Eugen Rieck May 10 '16 at 09:25
@EugenRieck, I meant 100+ **li**st is possible (say in science document) and this will cause 100+ mysql queries each time, which is much worse then 100 times bigger php array – vladkras May 10 '16 at 11:47
@EugenRieck, oh, come on, 3-level menu with 100 entries will cause 1000000 **small queries**. learn math – vladkras May 11 '16 at 10:18
2

Thanks @vladkras, by far this is the best solution I've found – Klaujesi Jun 22 '16 at 21:19

Eugen Rieck · Accepted Answer · 2016-12-18T12:56:10.373

7

Do this recursivly:

function printChildQuestions($parentid) {
  $sql="SELECT * FROM pB_test WHERE parentID=$parentid";
  $result=mysql_query($sql);
  $i=0;
  while (true) {
    $row=mysql_fetch_array($result);
    if (!$row) break;
    if ($i==0) echo "<ul>";
    $i=1;
    echo '<li>'.$row['id'].'&nbsp;'.$row['description'].'&nbsp;'.$row['parentId'].'</li>';
    printChildQuestions($row['id']);
  }
  if ($i>0) echo '</ul>';
}

printChildQuestions(0);

edited Dec 18 '16 at 12:56

answered Jun 12 '12 at 09:49

Eugen Rieck

64,175
10
70
92

It works! Thank you so much! You win the internets today mate! – Keiran Lovett Jun 12 '12 at 09:54
Ok, how would I then organize the data into divs so say level1 was a container for all of it's children and there was another div container within that. Then level2 was the next container down the list for that? – Keiran Lovett Jun 12 '12 at 09:58
You gotta ask a seperate question: I am a PHP/SQL guy, not a HTML guy, sorry for that – Eugen Rieck Jun 12 '12 at 09:59
Well it should still be applicable right? How do I split it up so each level can nest it's own html markup? If you can't do it that's alright. Thanks for the help though! – Keiran Lovett Jun 12 '12 at 10:03
Basically start from my code: Where I echo `
` becomes your ending HTML. The line `
...

`, ending `

Eugen Rieck

Jun 12 '12 at 10:06

K, I'll give it a shot. Cheers mate! – Keiran Lovett Jun 12 '12 at 10:07

I dont' understand when $i value became > 0 – Lucabro Nov 07 '13 at 12:58

bad answer, too many queries to DB, `while (true) { $row=mysql_fetch_array($result); if (!$row) break;` instead of simply `while($row=mysql_fetch_array)` stupid variable `$i`, etc. – vladkras May 04 '14 at 05:54

$i and the if statements using it can be deleted without affecting the result. – DanAllen Dec 18 '16 at 07:42

@DanAllen Good catch, thanks! I initially forgot the line `$i=1`. The construction around `$i` is ment to make sure, an **empty** submenu doesn' give an empty `

`. – Eugen Rieck Dec 18 '16 at 12:58

Recursive tree traversal with mysql through PHP

2 Answers2

Linked