Database driven navigation PHP & MySQL

Question

Possible Duplicate:
How can I convert a series of parent-child relationships into a hierarchical tree?

i'm working on a web application. and would like to make the navigation database driven. multilevel navigation, btw. now, the tricky part of it is, that i would like to use just one table. i looked, read and searched, but couldn't find a answer.

my table structure is

ID | NAME | LINK | ALC | PARENT_ID |

0    Home   #      0     0

1    Ops    #      0     0

2    ops1   #      0     1

3    ops2   #      0     1

and the question is how do i get the db content in an array so that i'm able to create the list tags with the subcategory

<ul>
    <li>HOME</li>
    <li>OPS
        <ul>
            <li>ops1</li>
            <li>ops2</li>
        </ul>
    </li>
</ul>

you can create a recursive function for that – Mihai Matei Oct 29 '12 at 15:37 — Mihai Matei, Oct 29 '12 at 15:37

score 0 · Answer 1 · answered Oct 29 '12 at 15:41

It's called recursive functions and they call themselves. Proof of concept:

function printUL($parentID = 0){
    // select elements with PARENT_ID = $parentID (or PARENT_ID = NULL if 0)
    if(!$children){
        return;
    }
    // now print stuff
    echo '<ul>';
    foreach($children as $child){
        echo '<li>';
            echo $child['NAME']; // change as you need
            printUL($child['ID']); // functions calls itself to dig deeper
        echo '</li>';
    }
    echo '</ul>';
}
// call for root element
printUL($parentID);

Hope it makes sense. Change it to your needs.

score -1 · Answer 2 · answered Oct 29 '12 at 15:42

-1

function myTree($parent_id) {
   $sql = mysql_query("SELECT category_id, category_name 
                       FROM categories 
                       WHERE parent_id='$parent_id'");
   if(mysql_num_rows($sql) != 0) {
       echo '<ul>'; 
       while($row = mysql_fetch_array($sql)) {
           echo '<li>' . $row['category_name'];

           myTree($row['category_id']);

           echo '</li>';
       }
       echo '</ul>';
   }
}

echo '<div>';

myTree(0);

echo '</div>';

answered Oct 29 '12 at 15:42

Mihai Matei

24,166
5
32
50

Please don't suggest people use `mysql_query`. It's deprecated and unsafe by default. – tadman Oct 29 '12 at 16:06
i don't think it matters, for my question weather its mysql_query or not i just needed an approach. to get me in the right direction. i got it that far that i got the db entries in an array but couldn't come to a conclusion how to sort the array. And look there i was not even close whit my conslusion. the recursive functions is the solution. Thanks again – jeremy.k Oct 29 '12 at 16:24
@tadman the idea is how he can achieve what he want and not which type of database functions should he use.. -1 for nothing.. – Mihai Matei Oct 30 '12 at 06:15
You say that like it's not a big deal. It matters. `mysql_query` needs to go away in the worst possible way. It's severely polluting the mind-share of new PHP developers. Many people give PDO or `mysqli` answers that are not only *safe* but barely a line or two longer. – tadman Oct 30 '12 at 14:18

Database driven navigation PHP & MySQL

2 Answers2