PHP/MySQL : Import data and store in hierarchical nested set for use with jsTree

Question

我正在使用 jsTree 查看作为嵌套集(左、右、级别等)存储在 mySQL 数据库中的分层数据。这工作正常，但我需要允许用户通过上传 CSV 文件来导入数据。当他们这样做时，表格中的任何现有数据都将被删除，因此我不必担心更新左/右字段。

他们将要上传的数据将采用以下格式:

"Code","Title"
"100","Unit 100"
"200","Unit 200"
"101","Task 101: This is a task"
"102","Task 102: Another task"
"201","Task 201: Yet another"   
"300","Unit 300"
"301","Task 301: Another one"

一切都将是一级节点的主要“组”的子节点。所有可被 100 整除的“代码”(即 100、200、300)都将是级别 2(父节点..“组”的子节点)。所有其他的将是它们各自父节点的 3 级(子)节点(即 101 和 102 是 100 的子节点，201 是 200 的子节点，等等)

mySQL 中的结果表应该如下所示:

id  parent_id   position    left    right   level   title
1   0           0           1       18      0       ROOT     
2   1           0           2       17      1       Group
3   2           0           3       8       2       Unit 100
4   2           1           9       12      2       Unit 200
5   3           0           4       5       3       Task 101: This is a task
6   3           1           6       7       3       Task 102: Another task
7   4           0           10      11      3       Task 201: Yet another   
8   2           2           13      16      2       Unit 300
9   8           0           14      15      3       Task 301: Another one

这棵树看起来像这样:

我的问题是:使用 PHP，完成此任务的最佳方法是什么？我已经有了代码，可以提取上传的 CSV 文件中包含的数据并将其存储在数组中，但我不确定将其转换为嵌套集的逻辑应该是什么样子。

现在，数据存储在名为 $data 的二维数组中(格式为 $data[$col][$row]):

$data[0][0] = "Code";
$data[0][1] = "100";
$data[0][2] = "200";
$data[0][3] = "101";
$data[0][4] = "102";
$data[0][5] = "201";
$data[0][6] = "300";
$data[0][7] = "301";
$data[1][0] = "Title";
$data[1][1] = "Unit 100";
$data[1][2] = "Unit 200";
$data[1][3] = "Task 101: This is a task";
$data[1][4] = "Task 102: Another task";
$data[1][5] = "Task 201: Yet another";
$data[1][6] = "Unit 300";
$data[1][7] = "Task 301: Another one";

Array ( [0] => Array ( [0] => Code [1] => 100 [2] => 200 [3] => 101 [4] => 102 [5] => 201 [6] => 300 [7] => 301 ) [1] => Array ( [0] => Title [1] => Unit 100 [2] => Unit 200 [3] => Task 101: This is a task [4] => Task 102: Another task [5] => Task 201: Yet another [6] => Unit 300 [7] => Task 301: Another one ) )

如有任何帮助，我们将不胜感激。我现在可以正确计算 parent_id、位置和级别...我只需要弄清楚左/右部分。这是我目前正在使用的代码(感谢让我开始使用 Matteo):

$rows = array();

// insert ROOT row
$rows[] = array(
    'id' => 1,
    'parent_id' => 0,
    'position' => 0,
    'left' => 1,
    'right' => 10000,       // just a guess, will need updated later
    'level' => 0,
    'title' => 'ROOT',
);

echo "<br>";
print_r($rows[0]);

// insert group row
$rows[] = array(
    'id' => 2,
    'parent_id' => 1,
    'position' => 0,
    'left' => 2,
    'right' => 9999,        // just a guess, will need updated later
    'level' => 1,
    'title' => 'Group',
);

echo "<br>";
print_r($rows[1]);

// next ID to be used
$id = 3;

// keep track of code => ID correspondence
$map = array();

// parse data
for ($i = 1, $c = count($data[0]); $i < $c; ++$i) {
    // save ID in the map
    $map[$data[0][$i]] = $id;

    // initialize the current row
    $row = array(
        'id' => $id,
        'parent_id' => 1,           
        'position' => 0,            
        'left' => 0,
        'right' => 0,           
        'level' => 1,               
        'title' => $data[1][$i],
    );

    // if the code is multiple of 100
    if ($data[0][$i] % 100 == 0) {
        $row['parent_id'] = 2;
        $row['level'] = 2;
        $row['position'] = (floor($data[0][$i] / 100)) - 1;
    } else {
        // get parent id from map
        $row['parent_id'] = $map[floor($data[0][$i] / 100) * 100];
        $row['level'] = 3;
        $row['position'] = $data[0][$i] % 100;
    }

    // add the row
    $rows[] = $row;

    ++$id;

    echo "<br>";
    print_r($row);
}

Answer 1

给定你的 $data 数组，你可以像这样解析它:

// this will contain all the rows to be inserted in your DB
$rows = array();

// insert ROOT row
$rows[0] = array(
    'id' => 1,
    'parent_id' => 0,
    'position' => 0,
    'level' => 0,
    'left' => 1,
    'right' => 10000,
    'title' => 'ROOT',
);

// insert group row
$rows[1] = array(
    'id' => 2,
    'parent_id' => 1,
    'position' => 0,
    'level' => 1,
    'left' => 2,
    'right' => 9999,
    'title' => 'Group',
);

// keep trace of code => ID correspondence
$map = array();

// next ID to be used
$id = 3;

// keep father => sons relationship
$tree = array();

// keep trace of code => row index correspondence
$indexes = array();

// next row index
$index = 2;

// parse your data
for ($i = 1, $c = count($data[0]); $i < $c; ++$i) {
    // current code
    $code = $data[0][$i];

    // save ID in the map
    $map[$code] = $id;

    // update the indexes map
    $indexes[$code] = $index;

    // prepare the current row
    $row = array(
        'id' => $id,
        'title' => $data[1][$i],
    )

    // get the value of code mod 100
    $mod = $code % 100;

    // if the code is multiple of 100
    if ($mod == 0) {
        // the parent_id is 2
        $row['parent_id'] = 2;

        // it is level two
        $row['level'] = 2;

        // compute position
        $row['position'] = floor($code / 100) - 1;
    }
    else {
        // get the parent code
        $parent = floor($code / 100) * 100;

        // get parent id from map using parent code
        $row['parent_id'] = $map[$parent];

        // it is level three
        $row['level'] = 3;

        // save position
        $row['position'] = $mod;

        // save in relationship tree
        $tree[$parent][] = $code;
    }

    // add the row
    $rows[$index] = $row;

    // prepare next id
    ++$id;

    // update row index
    ++$index;
}

// sort the relationship tree base on the parent code (key)
ksort($tree, SORT_NUMERIC);

// next left value
$left = 3;

// now, using the relationship tree, assign left and right
foreach ($tree as $parent => $sons) {
    // calculate parent left value
    $parentLeft = $left;

    // prepare next left value
    ++$left;

    // to be sure that the sons are in order
    sort($sons, SORT_NUMERIC);

    // assign values to sons
    foreach ($sons as $son) {
        // index in the rows array
        $index = $indexes[$son];

        // set left value
        $rows[$index]['left'] = $left;

        // set right value
        $rows[$index]['right'] = $left + 1;

        // increment left value
        $left += 2;
    }

    // calculate parent right value
    $parentRight = $left;

    // prepare next left value
    ++$left;

    // index of parent in the rows array
    $index = $indexes[$parent];

    // set the values
    $rows[$index]['left'] = $parentLeft;
    $rows[$index]['right'] = $parentRight;
}

// update group row right value
$rows[1]['right'] = $left;

// prepare next left value
++$left;

// update root row right value
$rows[0]['right'] = $left;

此时，您可以一次插入所有行。

编辑:现在脚本应该正确处理所有必需的值。