nestedSortable (toHierarchy)在MySQL中存储结果 - nestedSortable (toHierarchy) storing result in MySQL

我完全不知道如何为有子元素的数据库更新"sort"列。我在这里和网上看到的答案似乎只涉及父母，没有更进一步。

我基本上试图使用"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新手，并且已经开发了很多年，但是这个问题把我难住了。

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

试试这样....

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...

这是一个递归列表打印

更新:这不是你要找的-请参阅其他答案

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>";