PHP和MYSQL的算法疑问

Question

已知表t1

    id     txt          parent
    --------------------------
    1      上山打老虎    0
    2      一二三四五    0
    3      老虎不在家    1
    4      啦啦啦啦啦    1
    5      啊啊啊啊啊    2
    6      嗯嗯恩恩恩    0
    .
    .
    .

希望通过一次查询

mysql_query("SELECT id,txt,parent FROM t1 ORDER BY id DESC")

经过php对数据的重新排列

这里的代码应该怎么写？？？

得到如下结构的数据

<ul>
    <li id=6>
        <b>嗯嗯恩恩恩</b>
    </li>
    <li id=2>
        <b>一二三四五</b>
        <ul>
            <li id=5>
                <b>啊啊啊啊啊</b>
            </li>
        </ul>
    </li>
    <li id=1>
        <b>上山打老虎</b>
        <ul>
            <li id=4>
                <b>啦啦啦啦啦</b>
            </li>
            <li id=3>
                <b>老虎不在家</b>
            </li>
        </ul>
    </li>
</ul>

这个过程中的PHP处理代码或者算法应该怎么写？求指点。

Answer 1

<?php
header('Content-Type: text/plain;charset=utf-8');
$arr = array(
    array(1,'Web Server',0),
    array(2,'Database',  0),
    array(3,'Nginx',     1),
    array(4,'Apache',    1),
    array(5,'MySQL',     2),
    array(6,'Browser',   0),
);
$new = array();
foreach($arr as $v) {
    if($v[2]==0) {
        $new[$v[0]][$v[0]] = $v[1];
    } else {
        $new[$v[2]][$v[0]] = $v[1];
    }
}
print_r($new);
//输出:
Array
(
    [1] => Array
        (
            [1] => Web Server
            [3] => Nginx
            [4] => Apache
        )

    [2] => Array
        (
            [2] => Database
            [5] => MySQL
        )

    [6] => Array
        (
            [6] => Browser
        )

)

Answer 2

Code

/**
 * 无限子级递归循环
 * @param  [type]  $data [description]
 * @param  integer $pid  [description]
 * @return [type]        [description]
 */
function unlimitedForChild ($data, $pid = 0) {
    $array = array();
    foreach ($data as $value) {
        if ($value['parent'] == $pid) {
            $value['child'] = unlimitedForChild($data, $value['id']);
            $array[] = $value;
        }
    }
    return $array;
}

$data = array(
    array(
        'id' => 1,
        'txt' => '上山打老虎',
        'parent' => 0
    ),
    array(
        'id' => 2,
        'txt' => '一二三四五',
        'parent' => 0
    ),
    array(
        'id' => 3,
        'txt' => '老虎不在家',
        'parent' => 1
    ),
    array(
        'id' => 4,
        'txt' => '啦啦啦啦啦',
        'parent' => 1
    ),
    array(
        'id' => 5,
        'txt' => '啊啊啊啊啊',
        'parent' => 2
    ),
    array(
        'id' => 6,
        'txt' => '嗯嗯恩恩恩',
        'parent' => 0
    ),
    //附加3级
    array(
        'id' => 7,
        'txt' => '第三级',
        'parent' => 3
    ),
);

$result = unlimitedForChild($data);

echo '<pre>';
print_r($result);
echo '</pre>';

Result

Array
(
    [0] => Array
        (
            [id] => 1
            [txt] => 上山打老虎
            [parent] => 0
            [child] => Array
                (
                    [0] => Array
                        (
                            [id] => 3
                            [txt] => 老虎不在家
                            [parent] => 1
                            [child] => Array
                                (
                                    [0] => Array
                                        (
                                            [id] => 7
                                            [txt] => 第三级
                                            [parent] => 3
                                            [child] => Array
                                                (
                                                )

                                        )

                                )

                        )

                    [1] => Array
                        (
                            [id] => 4
                            [txt] => 啦啦啦啦啦
                            [parent] => 1
                            [child] => Array
                                (
                                )

                        )

                )

        )

    [1] => Array
        (
            [id] => 2
            [txt] => 一二三四五
            [parent] => 0
            [child] => Array
                (
                    [0] => Array
                        (
                            [id] => 5
                            [txt] => 啊啊啊啊啊
                            [parent] => 2
                            [child] => Array
                                (
                                )

                        )

                )

        )

    [2] => Array
        (
            [id] => 6
            [txt] => 嗯嗯恩恩恩
            [parent] => 0
            [child] => Array
                (
                )

        )

)

Answer 3

<?php
$arr = [
    [1,'Web Server',0],
    [2,'Database',  0],
    [3,'Nginx',     1],
    [4,'Apache',    1],
    [5,'MySQL',     2],
    [6,'Browser',   0],
];
$new = array();
foreach($arr as $v) {
    if($v[2]==0) {
        $new[$v[0]]['name'] = $v[1];
    }else{
        $new[$v[2]]['sub'][$v[0]]['name'] = $v[1];
    }
}
print_r($new);
///////

Array
(
    [1] => Array
        (
            [name] => Web Server
            [sub] => Array
                (
                    [3] => Array
                        (
                            [name] => Nginx
                        )

                    [4] => Array
                        (
                            [name] => Apache
                        )

                )

        )

    [2] => Array
        (
            [name] => Database
            [sub] => Array
                (
                    [5] => Array
                        (
                            [name] => MySQL
                        )

                )

        )

    [6] => Array
        (
            [name] => Browser
        )

)