php - Parent - Child relationship within a single table

652

I have a table which is like the following.

parentid   uid
10001      10001
10001      10002
10001      10003
10002      10004
10004      10005
10003      10006
10005      10007

I need to establish the parent child relationship between rows in this single table.

I need to get the parent in the reverse order till 4 levels. For example the last record is uid 10007 whose parentid is 10005. Now uid 10005's parent is 10004 and 10004's parent is 10002 and 10002's parent is 10001.

I am using MySQL so recursion seems to be not possible. What are the options that I have and how do I address this multi-level issue. I use PHP/MySQL.

Thanks in advance guys.

715

Answer

Solution:

Since you have a finite 4 levels, you shouldn't need recursion (although it'd be handy to be able to use eg MS SQL CTEs).

Something like:

SELECT
  t4.uid as child, 
  --t3.uid as parent,
  --t2.uid as grand_parent,
  --t1.uid as great_grand_parent,
  t1.parentid as great_great_grand_parent
FROM
  your_table_name t1

  inner join your_table_name t2
  on t2.parentid = t1.uid

  inner join your_table_name t3
  on t3.parentid = t2.uid

  inner join your_table_name t4
  on t4.parentid = t3.uin

where 
  t4.uid = '10007' -- your start node.

If you need to do this for multiple nodes, you'd need to join this to something that selects your start nodes, or eg replace the aboveWHERE t4.uid = '10007' clause to beWHERE t4.uid IN (SELECT DISTINCT uid FROM your_table_name)

This was done freehand so apologies for typos.

People are also looking for solutions to the problem: html - PHP redirect doesn't work on IE?

Source

Didn't find the answer?

Our community is visited by hundreds of web development professionals every day. Ask your question and get a quick answer for free.

Ask a Question

Write quick answer

Do you know the answer to this question? Write a quick response to it. With your help, we will make our community stronger.

Similar questions

Find the answer in similar questions on our website.