亲子关系PHP/MYSQL [英] Parent Child Relationships PHP/MYSQL

问题描述

我有一个这样的表:

• id
• 名称
• parent_id

然后，我想根据其ID选择某些行，如下所示:

I then want to select certain rows based on their id, so something like this:

SELECT * 
  FROM TABLE 
 WHERE id IN ('1', '5', '8', '9', '35')  

我想通过此查询还显示父/子关系，例如:

I want to, from this query, also show the parent/child relationship, like:

id   parent  
-----------
1    0  
5    1  
8    0  
9    8  
35   9  

所以最终输出看起来像这样:

So the final output would look something like this:

1  
--5  

8   
--9  
 ----35  

我在mysql之外执行此操作吗，我已经尝试过使用数组，但是无法弄清楚它，或者
我是否在MYSQL中执行此操作，我也不知道该怎么做.

Do I do this outside of mysql, i have tried using arrays, but can't figure it out, or
Do I do it inside MYSQL, which i don't know how to do that either.

推荐答案

这就是我能够提供的，而且效果很好.

Here is what I was able to come with which seems to be working great.

PS-抱歉，格式不正确:( (已修复?)

1. 我从MYSQL中获取了parent_id和id，并将其放入数组键是id且值是父项的地方，所以在MySQL的while循环中，如下所示:$testarray[$id] = $parent_id;

2. 然后我通过下面的功能运行它，并按我的需要对其进行排序.

1. I grab my parent_id and id from MYSQL and put it into an arraly where the array keys are the id's and the values are the parents, so with in the while loop for mysql, something like this: $testarray[$id] = $parent_id;

2. Then I run it through the functions below, and it orders it just how I need it.

function retrieveSubTree($parent, $myarray) {
    $tempArray = $myarray;
    $array = array();           
    //now we have our top level parent, lets put its children into an array, yea!
    while ($child = array_search($parent, $tempArray)) {
        unset($tempArray[$child]);
        //now lets get all this guys children
        if (in_array($child, $tempArray)) {
            $array[$child] = retrieveSubTree($child, $tempArray);
        } else {
            $array[$child] = true;
        }
    }//end while
    return (!empty($array)) ? $array : false;
}

function retrieveTree($myarray) {
    $array = array();
    $counter = 0;
    foreach ($myarray as $key => $value) {
        $child = $key;
        $parent = $value;
        //if this child is a parent of somebody else
        if (in_array($child, $myarray) && $parent != '0') {
            while ($myarray[$parent] != '' && $myarray[$parent] != '0') {
                $newparent = $myarray[$parent];
                $parent = $newparent;
            }
            if (!array_key_exists($parent, $array)) {
                $array[$parent] = retrieveSubTree($parent, $myarray);
            }
        } else {
            //now make sure they don't appear as some child
            if (!array_key_exists($parent, $myarray)) {
                //see if it is a parent of anybody
                if (in_array($child, $myarray)) {
                    $array[$child] = retrieveSubTree($child, $myarray);
                } else {
                    $array[$child] = true;
                }
            }//end if array key
        }//end initial in array
    }//end foreach
    return (!empty($array) ? $array : false);
} 

$test = array(
    '1'=>'15',
    '2'=>'1',
    '3'=>'1',
    '4'=>'0',
    '5'=>'0',
    '6'=>'4',
    '7'=>'6',
    '8'=>'7',
    '9'=>'2',
    '10'=>'9'
);

print_r(retrieveTree($test)); 

这篇关于亲子关系PHP/MYSQL的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！