PHP MYSQL 插入类树

Question

对不起我的英语。我的数据库中有表:

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

所以 AFirst 是 parent_category_name 为 NULL 的根类别

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. 在正确方向上的输入。那么我现在应该赞成您的答案还是接受它？

Answer 1

您可以这样做(只显示逻辑而不是确切的语法，因为您显然可以编写自己的 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();
    }
}