php - 在 PHP 和 MySQL 中格式化类别树

Question

我有这个数据库（MySQL）：

CREATE TABLE `psStoreCategories` (
  `id_categories_of_the_store` int(10) UNSIGNED NOT NULL,
  `enable` char(1) COLLATE utf8_unicode_ci NOT NULL DEFAULT '0',
  `level` int(11) NOT NULL DEFAULT '0',
  `parent_id` int(11) NOT NULL DEFAULT '0',
  `name_pl` varchar(85) COLLATE utf8_unicode_ci DEFAULT NULL,
  `url_address_pl` varchar(125) COLLATE utf8_unicode_ci DEFAULT NULL,
  `name_en` varchar(85) COLLATE utf8_unicode_ci DEFAULT NULL,
  `url_address_en` varchar(125) COLLATE utf8_unicode_ci DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;


ALTER TABLE `psStoreCategories`
  ADD PRIMARY KEY (`id_categories_of_the_store`);


ALTER TABLE `psStoreCategories`
  MODIFY `id_categories_of_the_store` int(10) UNSIGNED NOT NULL AUTO_INCREMENT;
COMMIT;

样本数据：http ://serwer1356363.home.pl/pub/sql.zip

样本类别树：

Desktop and Mobile Applications
Desktop and Mobile Applications->Android Apps
Desktop and Mobile Applications->Android Apps->Games
Desktop and Mobile Applications->Android Apps->Games->Action
Desktop and Mobile Applications->Games
Desktop and Mobile Applications->Games->Action
Desktop and Mobile Applications->Games->Adventure
Desktop Applications
Desktop Applications->Games
Desktop Applications->Games->Action
Desktop Applications->Games->Adventure

最终，将有10个子类别深

我的PHP代码：

public function getListOfCategories(int $level, int $parentId): array
    {
        $names = $this->getValuesToString($this->createValuesToArray("name", ""));
        $result = $this->_db->query("SELECT id_categories_of_the_store, enable, level, $names FROM psStoreCategories WHERE parent_id=:parent_id ORDER by name_pl ASC;", array("parent_id" => $parentId));
        return $result;
    }



public function getListOfSubCategories(int $idCategory): array
    {
        $result = $this->_db->query("SELECT id_categories_of_the_store, enable, level, name_pl FROM psStoreCategories WHERE enable = 1 and  parent_id=:parent_id ORDER by name_pl ASC;", array( "parent_id"=> $idCategory));
        return $result;
    }

    public function getListOfFirstLevelCategories(): array
    {
        $result = $this->_db->query("SELECT id_categories_of_the_store, enable, level, name_pl FROM psStoreCategories WHERE enable = 1 and level=:level ORDER by name_pl ASC;", array("level" => 0));
        return $result;
    }

由于上述代码，我收到：

- main category 1
- main category 2
- main category 3
- main category 4
- main category 5
- main category 6
- main category 7
- main category 8

(getListOfFirstLevelCategories())

单击第一个类别后，我只有：

- sub category 1
- sub category 2
- sub category 3
- sub category 4
- sub category 5
- sub category 6
- sub category 7
- sub category 8

我需要这样的东西：

- main category 1
- main category 2
   -- sub category 1
   -- sub category 2
   -- sub category 3
   -- sub category 4
   -- sub category 5
   -- sub category 6
   -- sub category 7
   -- sub category 8
- main category 3
- main category 4
- main category 5
- main category 6
- main category 7
- main category 8

这是我的树枝文件：

<div class="card-content">
                            <div class="card-body card-dashboard card-dashboard-correct">
                                {% if pageTemplate.pageHeader.pageValue.level == '0'  %}
                                <a href="{{ pageTemplate.pageHeader.baseHref }}{{ pageTemplate.pageHeader.pageValue.mainCategoryUrl }}/AddNewEntry" class="btn btn-success btn-min-width btn-glow mr-1 mb-1 col-lg-2  addNewVall">Dodaj nową kategorię główną</a>
                                <br/><br/>
                                {% endif %}
                                <div class="clearLine"></div>

                                {% for data in pageTemplate.pageHeader.pageValue.dataArray %}
                                    <p class="categoryLevel1">- {{ data.name_pl }} {% if data.enable == 1 %}<span class="badge badge-default badge-info">Aktywny</span>{% endif %} {% if data.enable == '' %}<span class="badge badge-default badge-danger">Wyłączony</span>{% endif %}
                                        <a href="{{ pageTemplate.pageHeader.baseHref }}{{ pageTemplate.pageHeader.pageValue.mainCategoryUrl }}/EditEntry?id={{ data.id_categories_of_the_store }}" class="categoryLevelOption categoryLevelOption1" >[edytuj]</a>
                                        {% if pageTemplate.pageHeader.pageValue.level < 4 %}
                                            <a href="{{ pageTemplate.pageHeader.baseHref }}{{ pageTemplate.pageHeader.pageValue.mainCategoryUrl }}?level={{ data.level +1 }}&parentId={{ data.id_categories_of_the_store }}" class="categoryLevelOption">[Rozwiń]</a>
                                            <a href="{{ pageTemplate.pageHeader.baseHref }}{{ pageTemplate.pageHeader.pageValue.mainCategoryUrl }}/AddNewEntry/?level={{ data.level +1 }}&parentId={{ data.id_categories_of_the_store }}" class="categoryLevelOption categoryLevelOption1">[Dodaj podkategorię]</a>
                                        {% endif %}
                                        <a href="{{ pageTemplate.pageHeader.baseHref }}{{ pageTemplate.pageHeader.pageValue.mainCategoryUrl }}/removeEntry?level={{ data.level  }}&id={{ data.id_categories_of_the_store }}" class="categoryLevelOption categoryLevelOption2" onclick="return confirm('Czy napewno chcesz usunąć ten rekord?')">[Usuń]</a></p>
                                {% endfor %}

                            </div>
                        </div>

怎么做？

标签： phpmysqlajaxhtmltwig