Как реализовать вывод категорий и подкатегорий на одной странице в PHP/SQL?

Question

[World Hello]

Есть две таблицы:
1. Категории

sql создания таблицы Категории

CREATE TABLE `categories` (
	`id` INT(11) UNSIGNED NOT NULL AUTO_INCREMENT,
	`title` VARCHAR(100) NOT NULL DEFAULT 'unnamed',
	`icon` VARCHAR(100) NOT NULL DEFAULT 'icon',
	PRIMARY KEY (`id`),
	UNIQUE INDEX `Индекс 2` (`title`)
)
COLLATE='utf8_general_ci'
ENGINE=InnoDB
AUTO_INCREMENT=3
;

2. Подкатегории (имеют внешний ключ на категории)

sql создания таблицы Подкатегории

CREATE TABLE `subcategories` (
	`id` INT(11) UNSIGNED NOT NULL AUTO_INCREMENT,
	`title` VARCHAR(100) NOT NULL DEFAULT 'unnamed',
	`category_id` INT(11) UNSIGNED NOT NULL,
	PRIMARY KEY (`id`),
	INDEX `id` (`category_id`),
	CONSTRAINT `id` FOREIGN KEY (`category_id`) REFERENCES `categories` (`id`) ON DELETE CASCADE
)
COLLATE='utf8_general_ci'
ENGINE=InnoDB
;

PHP код который есть сейчас (Работает, выводит только категории):

index.php

<?php
require_once 'init.php';

if (!$link) {
    $error = mysqli_connect_error();
    $content = include_template('error.php', ['error' => $error]);
}
else {
    $sql = 'SELECT * FROM categories';
    $result = mysqli_query($link, $sql);

    if ($result) {
        $categories = mysqli_fetch_all($result, MYSQLI_ASSOC);
        $content = include_template('main.php', ['categories' => $categories]);
    }
    else {
        $error = mysqli_error($link);
        $content = include_template('error.php', ['error' => $error]);
    }
}

print(include_template('layout.php', ['content' => $content]));

Как, и в каком месте кода будет правильней реализовать вывод категорий, и её подкатегорий (при условии что они могут отсутствовать)?

Answer 1

[ThunderCat]

1) по уму нужно просто nested sets
2) В вашей (не очень удачной) структуре нужно сджоинить таблицы по id - category_id.
3) Такая структура норм только для 2 уровней вложенности, что не есть хорошо.

Comments

[World Hello]

Почему структура не очень удачна? Так как лучше реализовать, вложенными множествами(что это в MySQL?) или объединением?

[ThunderCat]

Hasiev1, по тому что:
1) категории и подкатегории по сути ничем не отличаются, плодить однородные сущности - грех.
2) Вложенность не больше 2 уровней, если понадобится больше - приехали.
3)

вложенными множествами(что это в MySQL?)

То же что и в не MySQL, легко ищется

[World Hello]

ThunderCat, То есть вы предлагаете хранить в одной таблице и категории, и подкатегории? Как такую мульти-таблицу правильно спроектировать, и как понять какие подкатегории к какой категории относятся, и вообще как их друг от друга отличать (категории от подкатегорий)? Спасибо!

[World Hello]

В целом я мысль понял, буду думать в этом направлении, спасибо.

[ThunderCat]

Hasiev1, ничего сложного, все описано сотню раз, реализаций масса, материалов тоже.

[World Hello]

С SQL разобрался, но теперь не пойму как это вывести все правильно ..
Так работает, но это опять же не правильный подход?
Вложенные условия получаются ..

index.php

<?php
require_once 'init.php';

if (!$link) {
    $error = mysqli_connect_error();
    $content = include_template('error.php', ['error' => $error]);
}
else {
    $sql = 'SELECT * FROM categories WHERE `ref_id` IS NULL';
    $result = mysqli_query($link, $sql);

    if ($result) {
        $categories = mysqli_fetch_all($result, MYSQLI_ASSOC);

        $sql = 'SELECT * FROM categories WHERE `ref_id` IS NOT NULL';
        $result = mysqli_query($link, $sql);
        
        if($result) {
          $subcategories = mysqli_fetch_all($result, MYSQLI_ASSOC);
        
          $content = include_template('main.php', ['categories' => $categories, 'subcategories' => $subcategories]);
        } 
        else {
          $error = mysqli_error($link);
          $content = include_template('error.php', ['error' => $error]);
        }
    }
    else {
        $error = mysqli_error($link);
        $content = include_template('error.php', ['error' => $error]);
    }
}

print(include_template('layout.php', ['content' => $content]));

[Антон]

Hasiev1, Категория и подкатегория ничем же не отличаются, у главных категорий parent_id = 0, у вложенных - непосредственно id их родителя.
Далее просто проходим рекурсией и к массиву категории в ключ childrens по parent_id подсоединяем его потомка. Какая угодно вложенность, все работает как часы.

Смотрите на это как на псевдокод

function menu($parent_id = 0) {
  
  global $db;
  
  $result = $db->get_results("SELECT * FROM `Categories` WHERE  `Parent_Sub_ID`='" . $parent_id . "', ARRAY_A);
  
  if( ! empty($result)) {
	foreach($result as & $item) {
		$temp = menu($item['id']);
		$item['childrens'] = $temp['result'];
	}
  }
  
  return Array('result' => $result);
}