PHP / mySQL：导入数据并存储在分层嵌套集中,以用于jsTree
内容导读
互联网集市收集整理的这篇技术教程文章主要介绍了PHP / mySQL：导入数据并存储在分层嵌套集中,以用于jsTree，小编现在分享给大家，供广大互联网技能从业者学习和参考。文章包含6229字，纯文字阅读大概需要9分钟。
内容图文
我正在使用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"
一切都将是主“组”的子级,该“组”是1级节点.所有可被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);
}
解决方法:
给定$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;
此时,您可以一次插入所有行.
编辑：现在,脚本应该正确处理所有必需的值.
内容总结
以上是互联网集市为您收集整理的PHP / mySQL：导入数据并存储在分层嵌套集中,以用于jsTree全部内容，希望文章能够帮你解决PHP / mySQL：导入数据并存储在分层嵌套集中,以用于jsTree所遇到的程序开发问题。 如果觉得互联网集市技术教程内容还不错，欢迎将互联网集市网站推荐给程序员好友。
内容备注
版权声明：本文内容由互联网用户自发贡献，该文观点与技术仅代表作者本人。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容， 请发送邮件至 gblab@vip.qq.com 举报，一经查实，本站将立刻删除。
内容手机端
扫描二维码推送至手机访问。
来源：【匿名】