PHP MYSQL插入类别树
PHP MYSQL插入类别树
提问于 2018-06-14 21:18:17
对不起，我的英语不好。我的数据库中有一个表：
CREATE TABLE `export_raw_categories` (
`category_id` int(11) NOT NULL,
`category_name` varchar(128) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL ,
`parent_category_id` int(11) DEFAULT NULL ,
`parent_category_name` varchar(128) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL ,
`profile_name` varchar(128) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=COMPACT;我在PHP中有一个数组：
Array
(
[0] => Array
(
[0] => AFirst
[1] => ASecond
[2] => AThird
[3] => AFourth
[4] => AFifth
)
[1] => Array
(
[0] => BFirst
[1] => BSecond
[2] => BThird
[3] => BFourth
)
[2] => Array
(
[0] => BFirst
[1] => BSixth
)
)例如：
键为0的数组是目录树，如：AFirst\ASecond\AThird\AFourth\AFifth
因此，parent_category_name为NULL时，AFirst是根类别
ASecond属于parent_category_name AFirst类别
AThird属于parent_category_name ASecond类别
诸若此类。
我的问题是如何在数据库中分配合适的parent_category_id？
下面是我的PHP代码(Codeigniter)，但没有给出好的结果：
foreach ($d as $key => $val) {
foreach ($val as $sub_key => $sub_val) {
$root_cat = array(
'profile_name' => 'XYZ',
'category_name' => $val[0],
'parent_category_name' => null,
'root_category' => null
);
$query = $this->db->get_where('export_raw_categories', $root_cat);
if ($query->num_rows() == 0) {
$this->db->insert('export_raw_categories', $root_cat);
$root_id = $this->db->insert_id();
} else if ($query->num_rows() == 1) {
$root_id = $query->row('category_id');
}
if (isset($val[$sub_key - 1])) {
$category_name = $sub_val;
$parent_category_name = $val[$sub_key - 1];
$root_cat = $val[0];
$category = array(
'profile_name' => 'XYZ',
'category_name' => $sub_val,
'parent_category_name' => $parent_category_name,
'parent_category_id' => $root_id,
'root_category' => $parent_category_name . "\\" . $category_name
);
$query = $this->db->get_where('export_raw_categories', $category);
if ($query->num_rows() == 0) {
$this->db->insert('export_raw_categories', $category);
}
}
}
}更新
这是可行的：
foreach ($arr as $key => $categories) {
$parent = NULL;
foreach ($categories as $key_cat => $cat) {
$category = array(
'profile_name' => 'XYZ',
'category_name' => $cat,
'parent_category_name' => isset($categories[$key_cat - 1]) ? $categories[$key_cat - 1] : null,
'parent_category_id' => $parent
// 'root_category' => $parent_category_name . "\\" . $category_name
);
$query = $this->db->get_where('export_raw_categories', $category);
if ($query->num_rows() == 0) {
$this->db->insert('export_raw_categories', $category);
$parent = $this->db->insert_id();
} else if ($query->num_rows() == 1) {
$parent = $query->row('category_id');
}
}
}感谢@Rok D.在正确方向上的输入。那么，我现在应该提升你的答案，还是接受它？
回答 2
Stack Overflow用户
回答已采纳
发布于 2018-06-14 21:36:58
您可以这样做(只显示逻辑，而不是确切的语法，因为您显然可以编写自己的SQL查询。
foreach ($arr as $categories) {
$existingRoot = $this->db->query(); // SELECT category_id... WHERE category_name = $categories[0]
// If only roots can appear as 1st item then add AND parent_category_id = 0
$parent = $existingRoot ? $existingRoot->category_id : 0;
foreach ($categories as $cat) {
if ($cat == $existingRoot->category_name) {
continue;
}
$this->db->insert();
$parent = $this->db->insert_id();
}
}Stack Overflow用户
发布于 2018-06-14 22:14:59
我把它分成两个函数-一个迭代基数组-另一个负责数据处理。
像这样的东西应该是可行的
public function tree()
{
$d = [
[
'AFirst', 'ASecond', 'AThird', 'AFourth', 'AFifth'
],
[
'BFirst', 'BSecond', 'BThird', 'BFourth'
],
[
'BFirst', 'BSixth'
]
];
foreach($d AS $arrData)
{
$this->insert($arrData);
}
}
function insert($arrData, $arrParentData = null)
{
if (is_array($arrData) && count($arrData) > 0)
{
$strData = array_shift($arrData);
$arrDbData = [
'category_name' => $strData,
];
if ($arrParentData)
{
$arrDbData = array_merge($arrDbData, $arrParentData);
}
$this->db->insert('export_raw_categories', $arrDbData);
$parentId = $this->db->insert_id();
$arrParentData = [
'parent_category_name' => $strData,
'parent_category_id' => $parentId
];
$this->insert($arrData, $arrParentData);
}
}页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/50858517
复制相关文章
相似问题