对不起,我的英语不好。我的数据库中有一个表:
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.在正确方向上的输入。那么,我现在应该提升你的答案,还是接受它?
发布于 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();
}
}
发布于 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);
}
}
https://stackoverflow.com/questions/50858517
复制相似问题