nestedSortable (toHierarchy)在MySQL中存储结果

Question

我完全搞不懂如何更新数据库中有子元素的“排序”列。我在这里和网络上看到的答案似乎只与父母打交道，仅此而已。

我基本上是在尝试使用"nestedSortable“来构建一个可排序的嵌套列表。当用户释放拖动事件时，将触发ajax调用并更新数据库。我已经让它在单级(没有父级)的项目上工作得很好，但绝对不能理解如何做到这一点。

数据库结构: id | parent | label | sort

JavaScript：

      $('ol.ROMenus').nestedSortable({
disableNesting: 'no-nest',
forcePlaceholderSize: true,
handle: 'div',
helper: 'clone',
items: 'li',
maxLevels: 3,
opacity: .6,
placeholder: 'placeholder',
revert: 250,
tabSize: 25,
tolerance: 'pointer',
toleranceElement: '> div',
update: function () {
    list = $(this).nestedSortable('toHierarchy');
    $.post(
        'includes/ajax/saveorder.inc.php',
        { order: list, section:"menus",side: menuSide },
        function(data){
            $("#ReOrderRes").hide().html(data).fadeIn('slow')
        },
        "html"
    );
}
});

来自nestedSortable的输出数组：

Array
(
[0] => Array
    (
        [id] => 1
    )

[1] => Array
    (
        [id] => 4
        [children] => Array
            (
                [0] => Array
                    (
                        [id] => 14
                        [children] => Array
                            (
                                [0] => Array
                                    (
                                        [id] => 24
                                    )

                                [1] => Array
                                    (
                                        [id] => 15
                                    )

                                [2] => Array
                                    (
                                        [id] => 28
                                    )

                            )

                    )

                [1] => Array
                    (
                        [id] => 36
                    )

                [2] => Array
                    (
                        [id] => 37
                    )

                [3] => Array
                    (
                        [id] => 38
                    )

                [4] => Array
                    (
                        [id] => 39
                    )

                [5] => Array
                    (
                        [id] => 40
                    )

                [6] => Array
                    (
                        [id] => 41
                    )

                [7] => Array
                    (
                        [id] => 42
                    )

            )

    )

[2] => Array
    (
        [id] => 67
    )

)

HTML标记：

<ol class="ROMenus">
<li id="listItem-1"><div>About Us</div></li>
<li id="listItem-4"><div>Products</div>
    <ol>
        <li id="listItem-14"><div>Furniture</div>
<ol>
<li id="listItem-24"><div>Tables</div></li>
<li id="listItem-15"><div>Chairs</div></li>
<li id="listItem-28"><div>Cupboards</div></li>
</ol>
        </li>
        <li id="listItem-36"><div>Paints</div></li>
    </ol>
</li>
</ol>

我所见过的是使用foreach循环，但这只覆盖了顶层的父项，并没有涉及到子元素：

foreach ($list as $key => $value) { #Do Something } 

我不是一个PHP/MySQL新手，我已经开发了很多年，但这一点让我感到困惑。任何帮助都将不胜感激。

Answer 1

这是递归列表打印

更新：这不是您要查找的内容-请参阅其他答案。

function displayItem($item) {
   echo "<li id={$item['id']}>";
   if ($item['children']) {
       echo '<ol>'.displayItem( $item['children']).'</ol>';
   }
   echo "</li">;
}


echo "<ol>\n".displayItems($list)."</ol>";

Answer 2

对于这类事情，CakePHP具有内置的树型模型行为能力。这听起来像是一件痛苦的事情。你可以这样做：

//##From Docs: http://book.cakephp.org/2.0/en/core-libraries/behaviors/tree.html ##
$allChildren = $this->Category->children(1); // a flat array with 11 items
// -- or --
$this->Category->id = 1;
$allChildren = $this->Category->children(); // a flat array with 11 items

// Only return direct children
$directChildren = $this->Category->children(1, true); // a flat array with
                                                      // 2 items

它的设置并不困难。这里有几个教程：

http://www.cakephpexample.com/cakephp-basics/cakephp-tree-behavior-example/

http://www.dereuromark.de/2013/02/17/cakephp-and-tree-structures/

如果你从未使用过CakePHP，这里是博客教程，它似乎是每个人使用该框架的第一块试金石：

http://book.cakephp.org/2.0/en/tutorials-and-examples/blog/blog.html

Answer 3

试试这样的东西……

function mapValues($data) {
   $str = '(' . 
       join( ',', 
           array( $data['id'], $data['label'], $data['sort'], $data['parent']) 
       . ')';
   return $str;
}

function updateList($data, $parent) {
    $list = array();
    foreach ($data as $key => $value) {
       $list[] = array( 
               id => $value['id'], label => $value['label'], 
               sort => $key, parent => $parent);
       if ($value['children']) {
           $list = array_merge($list, updateList( $value['children'], $value['id']));
       }
    }
    return $list;
}

// this may have SQL injection issues - improve as necessary....

$list = updateList($myPostedData, 0);
$textList = join( ',', array_map( 'mapValues', $list));
$sql = <<<EOF
INSERT INTO myTable( id, label, parent, sort)
VALUES $textList
ON DUPLICATE KEY UPDATE 
    id=VALUES(id), label=VALUES(label), 
    parent=VALUES(parent), sort=VALUES(sort)
EOF;

// run the SQL with your favourite mysqli/PDO or whatever library here...