how to list a self relation table?

Question

I have a table like this:

+----+-------+--------+
| id | title | parent |
+----+-------+--------+
| 1  | text1 |  NULL  |
+----+-------+--------+
| 2  | text2 |  NULL  |
+----+-------+--------+
| 3  | text3 |  NULL  |
+----+-------+--------+
| 4  | text4 |   1    |
+----+-------+--------+
| 5  | text5 |   1    |
+----+-------+--------+
| 6  | text6 |   2    |
+----+-------+--------+
| 7  | text7 |   3    |
+----+-------+--------+
| 8  | text8 |   5    |
+----+-------+--------+

"parent" is foreign key to "id" and I want to list these rows in html.

the list should be like this:

• text1
    ○ text4
    ○ text5
        ♦ text8
• text2
    ○ text6
• text3
    ○ text7

I need the sql_code and php.

thanks...

With MySQL you will need to do that in your application code as MySQL doesn't support recursive queries (unlike most other DBMS) — , Nov 30 '12 at 12:13
you should go through http://stackoverflow.com/questions/11064913/achieve-hierarchy-in-a-less-number-of-mysql-queries it will be helpfull to you — Sashi Kant, Nov 30 '12 at 12:59
@a_horse_with_no_name maybe you should add that or [this one](http://stackoverflow.com/questions/13634660/mysql-complicated-relational-select/13634733#comment18702907_13634733) to the [pro forma comments](http://meta.stackexchange.com/q/136609/148672) ;) — Conrad Frix, Nov 30 '12 at 16:25

score 1 · Answer 1 · answered Nov 30 '12 at 12:17

1

You should look into managing hierarchical data with MySQL:

SELECT 
     t1.title AS lev1, t2.title as lev2, t3.title as lev3, t4.title as lev4
FROM 
     category AS t1
LEFT JOIN 
     category AS t2 ON t2.parent = t1.id
LEFT JOIN 
     category AS t3 ON t3.parent = t2.id
LEFT JOIN 
     category AS t4 ON t4.parent = t3.id

Note that the article recommends a different approach from the common parent_id/id setup.

answered Nov 30 '12 at 12:17

Wayne Whitty

19,513
7
44
66

What if the level is not constant like in http://stackoverflow.com/questions/11064913/achieve-hierarchy-in-a-less-number-of-mysql-queries – Sashi Kant Nov 30 '12 at 13:00

score 0 · Answer 2 · answered Nov 30 '12 at 13:26

Above MySQL code is Excellent .. This is my little effort for PHP code

while($lev = mysql_fetch_array($category)){

    echo $lev['lev1'];
    echo '<br>';
    if($lev['lev2'] != NULL)
    {
        &nbsp;echo $lev['lev2'];
        echo '<br>';
        if($lev['lev3'] != NULL)
        {
            &nbsp;&nbsp;echo $lev['lev3'];
            echo '<br>';
            if($lev['lev4'] != NULL)
            {
                echo $lev['lev4'];  
            }   
        }   
    }
}

how to list a self relation table?

2 Answers2