PHP: полный мануал с примерами, функциями и ООП

Какая связь нужна каталогу и как её читать

Каталог магазина похож на папки: Электроника содержит Компьютеры, они — Ноутбуки, дальше идут Игровые и Компактные. На сайте нужны разные представления одних связей: дети выбранного раздела, вся ветка, путь к текущему товару и страница товаров этой ветки. Храним связи в таблицах и просим MySQL вернуть именно нужное представление.

Модель называется списком смежности: строка хранит своего непосредственного родителя. Образ папок помогает представить вложенность, но в MySQL это обычные строки и числовые ссылки, а не физические каталоги диска. В этой модели у категории один parent_id; несколько родителей потребуют отдельной таблицы связей. Строки, ключи и таблицы и параметры запросов нужны до этой главы. Мы разберём рекурсивный запрос в SQL; рекурсия PHP решает другой уровень задачи.

Запустить каталог в отдельной учебной базе

Каждый ZIP содержит example.php, hq113-helper.php и README. Нужны PHP 8.4+ с PDO_MYSQL/mysqlnd, локальный MySQL 8.4 и отдельная учебная база. Настройте PHPMAN_MYSQL_HOST, PHPMAN_MYSQL_PORT, PHPMAN_MYSQL_DATABASE, PHPMAN_MYSQL_USER и PHPMAN_MYSQL_PASSWORD по главе о подключении. Контрольный стенд: PHP 8.5.11 и MySQL 8.4.13, 127.0.0.1:33070. Выполните php example.php, откройте result.html.

Self-FK нельзя создать на TEMPORARY-таблице. Поэтому каждый запуск создаёт собственные обычные InnoDB-таблицы с новым случайным именем mysql113_hierarchy_ и удаляет только их в finally; товары удаляются перед категориями. CREATE идёт без IF NOT EXISTS, флаги владения выставляются только после успеха. Права CREATE, SELECT, INSERT, UPDATE, REFERENCES, DROP выдаются только на учебную базу. Не отключаем FOREIGN_KEY_CHECKS и не меняем настройки сервера.

Создать родительскую связь и открыть один уровень каталога

В меню магазина нужно сначала показать «Каталог» и «Услуги», а при открытии Каталога — только «Быт» и «Электроника». Для этого каждой категории достаточно хранить ID непосредственного родителя. Десятки отдельных колонок «уровень1», «уровень2» создавать не нужно.

Исходные данные

Что получится

Главная запись SQL

CREATE TABLE categories (
    id INT UNSIGNED PRIMARY KEY,
    parent_id INT UNSIGNED NULL,
    title VARCHAR(80) NOT NULL,
    sort_order SMALLINT UNSIGNED NOT NULL,
    KEY parent_sort (parent_id,sort_order,id),
    FOREIGN KEY(parent_id) REFERENCES categories(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

SELECT id,title FROM categories
WHERE parent_id IS NULL ORDER BY sort_order,id;

SELECT id,title FROM categories
WHERE parent_id=? ORDER BY sort_order,id; -- параметр: 1

Этот фрагмент предполагает таблицы categories/products и данные выше. Самостоятельная программа создаёт нужные таблицы с уникальными именами, передаёт значения параметрами и не требует запуска предыдущего архива.

id — уникальный номер самой строки. parent_id — номер другой категории, на которую она ссылается. NULL здесь означает отсутствие родителя; это не 0 и не пустая строка. Поэтому корни ищут через IS NULL, а не = NULL. Корень — верхняя категория; ребёнок — строка, у которой parent_id равен id родителя; потомок может находиться через несколько таких шагов. Несколько корней образуют лес.

FOREIGN KEY(parent_id) REFERENCES categories(id) связывает таблицу с ней самой. У обоих столбцов одинаковый тип INT UNSIGNED, а id — PRIMARY KEY. Для NULL отдельный родитель не нужен; для числа MySQL проверяет наличие соответствующего id. Родителей вставляем раньше детей. Программа реально получает код 1452 при попытке сохранить parent_id=999 и проверяет, что новая строка не появилась.

Индекс parent_sort начинается с parent_id: он соответствует поиску непосредственных детей. sort_order задаёт редакционный порядок, id делает его однозначным при одинаковых позициях. Поиск WHERE parent_id=1 не захватывает внуков и не требует рекурсии. Символ ? — параметр значения; в PHP вызывается prepare, затем execute([1]).

Границы и ошибки

FK проверяет существование родителя, но не отсутствие циклов, число корней или правильность бизнес-категории. ON DELETE RESTRICT не позволяет удалить родителя, пока на него ссылаются дети. Политика переноса и удаления ветки требует отдельного решения; CASCADE не следует включать только ради удобства очистки.

<?php
declare(strict_types=1);
require __DIR__.'/hq113-helper.php';
$db=hq113Connect();
$runtime=['php'=>PHP_VERSION,'client'=>$db->getAttribute(PDO::ATTR_CLIENT_VERSION),'server'=>$db->getAttribute(PDO::ATTR_SERVER_VERSION)];
$prefix='mysql113_hierarchy_'.bin2hex(random_bytes(8));
$categories=$prefix.'_categories'; $products=$prefix.'_products';
$categoriesCreated=false; $productsCreated=false;
$details=[];
try {
    hq113CreateCategories($db,$categories); $categoriesCreated=true;
    hq113Seed($db,$categories);
    $ct=hq113Name($categories);
$roots=hq113Rows($db,"SELECT id,title FROM $ct WHERE parent_id IS NULL ORDER BY sort_order,id");
    $children=hq113Rows($db,"SELECT id,title FROM $ct WHERE parent_id=? ORDER BY sort_order,id",[1]);
    $orphanCode=null;
    try{$db->prepare("INSERT INTO $ct (id,parent_id,title,sort_order) VALUES (?,?,?,?)")->execute([99,999,'Категория без родителя',1]);}
    catch(PDOException $error){if((int)($error->errorInfo[1]??0)!==1452){throw $error;}$orphanCode=1452;}
    $format=fn(array $items)=>implode('; ',array_map(fn(array $item)=>$item['id'].' '.$item['title'],$items));
    $rows=[['Корни: parent_id IS NULL',$format($roots)],['Прямые дети Каталога 1',$format($children)],['Попытка сослаться на parent_id=999',(string)$orphanCode.': родитель отсутствует']];
    $checks=['roots_are_1_and_30'=>hq113Ids($roots)===[1,30],'direct_children_ordered'=>hq113Ids($children)===[20,10],'orphan_rejected'=>$orphanCode===1452,'no_orphan_inserted'=>(int)hq113Rows($db,"SELECT COUNT(*) AS total FROM $ct WHERE id=99")[0]['total']===0];
    $details=['root_ids'=>hq113Ids($roots),'direct_child_ids'=>hq113Ids($children),'foreign_key_error'=>$orphanCode];
} finally {
    if($db->inTransaction()){$db->rollBack();}
    if($productsCreated){$db->exec('DROP TABLE '.hq113Name($products));}
    if($categoriesCreated){$db->exec('DROP TABLE '.hq113Name($categories));}
}
$remaining=hq113Rows($db,'SELECT COUNT(*) AS total FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA=DATABASE() AND TABLE_NAME IN (?,?)',[$categories,$products]);
$checks['owned_tables_removed']=(int)$remaining[0]['total']===0;
$db=null;
hq113Report('mysql113-hierarchy-parent-and-children', ['Сценарий', 'Что получили'], $rows, $checks, $runtime, $details);
echo 'mysql113-hierarchy-parent-and-children: saved result.html and result.json
';

mysql113-hierarchy-parent-and-children: saved result.html and result.json

Открыть фактический HTML-результат · Скачать результат JSON

Попробуйте сами. Откройте parent_id=10 вместо 1. Ответ: Компьютеры 11 и Мониторы 15; Ноутбуки 12 появятся только при следующем уровне. Попытка вставить категорию с parent_id=NULL создаст ещё один корень, а не ошибку FK.

Получить выбранную ветку из пяти уровней одним запросом

Посетитель открыл «Электронику». Нужны все её подкатегории, включая «Компактные» на пятом уровне этой ветки, но не кухонные товары из соседнего «Быта». Выполнять один запрос на каждого потомка не нужно.

Исходные данные

Что получится

Главная запись SQL

WITH RECURSIVE branch AS (
    SELECT id,parent_id,title,0 AS depth
    FROM categories WHERE id=?
    UNION ALL
    SELECT c.id,c.parent_id,c.title,b.depth+1
    FROM categories AS c JOIN branch AS b ON c.parent_id=b.id
    WHERE b.depth<16
)
SELECT id,parent_id,title,depth
FROM branch ORDER BY depth,id;
-- Параметр: 10. depth=0 означает саму выбранную категорию.

Этот фрагмент предполагает таблицы categories/products и данные выше. Самостоятельная программа создаёт нужные таблицы с уникальными именами, передаёт значения параметрами и не требует запуска предыдущего архива.

WITH RECURSIVE объявляет временный именованный результат branch для одного SQL-запроса. Это не сохранённая таблица и не PHP-функция. Первый SELECT называют якорем: он задаёт исходную категорию. UNION ALL добавляет результаты следующего шага. Во втором SELECT b обозначает уже достигнутые строки предыдущей итерации, а c — очередных детей. Условие c.parent_id=b.id выбирает направление вниз.

depth — число переходов от выбранного начала: у 10 оно равно 0, у 16 — 4. Поэтому пять уровней включают стартовый. WHERE b.depth<16 ограничивает дальнейшее углубление; это предел нашей учебной задачи, а не настройка сервера. В этой корректной исходной ветке обход заканчивается раньше, потому что новых детей больше нет. Начало 999 даёт пустой результат, а не корневой каталог.

ORDER BY depth,id намеренно группирует строки по расстоянию от начала. Это удобный отчёт об уровнях, но ещё не порядок раскрытого меню. Меню будет показано отдельно через путь сортировки. PHP получает готовые семь строк: не загружает весь каталог и не выполняет собственный рекурсивный поиск в массиве.

Границы и ошибки

UNION ALL здесь допустим потому, что данные образуют дерево с одним родителем у узла. Предел depth сам по себе не отличает полный результат от обрезанного. Первые примеры используют известные корректные данные; последний сценарий вводит явные признаки цикла и неполноты. Ограничение глубины также не ограничивает число детей одного узла.

<?php
declare(strict_types=1);
require __DIR__.'/hq113-helper.php';
$db=hq113Connect();
$runtime=['php'=>PHP_VERSION,'client'=>$db->getAttribute(PDO::ATTR_CLIENT_VERSION),'server'=>$db->getAttribute(PDO::ATTR_SERVER_VERSION)];
$prefix='mysql113_hierarchy_'.bin2hex(random_bytes(8));
$categories=$prefix.'_categories'; $products=$prefix.'_products';
$categoriesCreated=false; $productsCreated=false;
$details=[];
try {
    hq113CreateCategories($db,$categories); $categoriesCreated=true;
    hq113Seed($db,$categories);
    $ct=hq113Name($categories);
$sql="WITH RECURSIVE branch AS (
        SELECT id,parent_id,title,0 AS depth FROM $ct WHERE id=?
        UNION ALL
        SELECT c.id,c.parent_id,c.title,b.depth+1
        FROM $ct AS c JOIN branch AS b ON c.parent_id=b.id
        WHERE b.depth<16
    ) SELECT id,parent_id,title,depth FROM branch ORDER BY depth,id";
    $found=hq113Rows($db,$sql,[10]);
    $rows=[];foreach($found as $row){$rows[]=[(int)$row['id'],(int)$row['parent_id'],$row['title'],(int)$row['depth']];}
    $missing=hq113Rows($db,$sql,[999]);
    $checks=['branch_ids'=>hq113Ids($found)===[10,11,15,12,13,14,16],'five_levels'=>max(array_map('intval',array_column($found,'depth')))===4,'outside_branch_absent'=>!in_array(20,hq113Ids($found),true) && !in_array(30,hq113Ids($found),true),'missing_start_empty'=>$missing===[]];
    $details=['selected_root'=>10,'depth_zero_is_selected_root'=>true,'depth_limit'=>16,'missing_start_rows'=>count($missing)];
} finally {
    if($db->inTransaction()){$db->rollBack();}
    if($productsCreated){$db->exec('DROP TABLE '.hq113Name($products));}
    if($categoriesCreated){$db->exec('DROP TABLE '.hq113Name($categories));}
}
$remaining=hq113Rows($db,'SELECT COUNT(*) AS total FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA=DATABASE() AND TABLE_NAME IN (?,?)',[$categories,$products]);
$checks['owned_tables_removed']=(int)$remaining[0]['total']===0;
$db=null;
hq113Report('mysql113-hierarchy-selected-branch', ['ID', 'parent_id', 'Категория', 'Глубина от 10'], $rows, $checks, $runtime, $details);
echo 'mysql113-hierarchy-selected-branch: saved result.html and result.json
';

mysql113-hierarchy-selected-branch: saved result.html and result.json

Открыть фактический HTML-результат · Скачать результат JSON

Попробуйте сами. Поменяйте старт на 12. Получатся 12, 13, 14, 16 с глубинами 0,1,1,2. Если нужны только потомки без самой категории, добавьте WHERE depth>0 во внешний SELECT, сохранив старт в якоре.

Построить хлебные крошки от текущей категории к корню

На странице «Компактные» нужна навигационная цепочка: Каталог → Электроника → Компьютеры → Ноутбуки → Игровые → Компактные. Один self-JOIN показал бы только текущую категорию и одного родителя; здесь предков пять.

Исходные данные

Что получится

Главная запись SQL

WITH RECURSIVE ancestors AS (
    SELECT id,parent_id,title,0 AS distance
    FROM categories WHERE id=?
    UNION ALL
    SELECT p.id,p.parent_id,p.title,a.distance+1
    FROM categories AS p JOIN ancestors AS a ON p.id=a.parent_id
    WHERE a.distance<16
)
SELECT id,parent_id,title,distance
FROM ancestors ORDER BY distance DESC;
-- Параметр: 16. Для вывода уже упорядоченных названий:
-- PHP: implode(' → ', array_column($rows, 'title'));

Этот фрагмент предполагает таблицы categories/products и данные выше. Самостоятельная программа создаёт нужные таблицы с уникальными именами, передаёт значения параметрами и не требует запуска предыдущего архива.

Для обхода вверх меняется условие соединения: p.id=a.parent_id. Мы больше не ищем строки, которые ссылаются на текущую, а читаем ту строку, на которую указывает текущая. distance растёт от нуля у 16 до пяти у 1. SQL сортирует по distance DESC, поэтому в PHP сразу приходит правильная последовательность ссылок.

Возвращаем id и title каждого звена отдельно: id нужен для адреса категории, title — для подписи. implode склеивает уже упорядоченные подписи только для текстового представления. Здесь нет загрузки и сортировки всей базы в PHP, а также нет GROUP_CONCAT со скрытым ограничением длины строки. В HTML каждую подпись экранируют; соединение названий само по себе не делает безопасную разметку.

Полная программа проверяет, что первая полученная строка имеет parent_id=NULL, а последняя — ID 16. Это важно: цепочку, остановленную произвольным пределом посередине, нельзя выдавать за полный путь. Если начальная категория не найдена, интерфейс должен отдельно показать отсутствие страницы, а не пустую «успешную» цепочку.

Границы и ошибки

В модели с несколькими родителями у одного узла может быть несколько допустимых путей: нужно выбрать правило навигации, а не случайно взять первый. При скрытых категориях следует решить, какие предки доступны посетителю; SQL-структура дерева сама по себе не является политикой доступа.

<?php
declare(strict_types=1);
require __DIR__.'/hq113-helper.php';
$db=hq113Connect();
$runtime=['php'=>PHP_VERSION,'client'=>$db->getAttribute(PDO::ATTR_CLIENT_VERSION),'server'=>$db->getAttribute(PDO::ATTR_SERVER_VERSION)];
$prefix='mysql113_hierarchy_'.bin2hex(random_bytes(8));
$categories=$prefix.'_categories'; $products=$prefix.'_products';
$categoriesCreated=false; $productsCreated=false;
$details=[];
try {
    hq113CreateCategories($db,$categories); $categoriesCreated=true;
    hq113Seed($db,$categories);
    $ct=hq113Name($categories);
$found=hq113Rows($db,"WITH RECURSIVE ancestors AS (
        SELECT id,parent_id,title,0 AS distance FROM $ct WHERE id=?
        UNION ALL
        SELECT p.id,p.parent_id,p.title,a.distance+1
        FROM $ct AS p JOIN ancestors AS a ON p.id=a.parent_id
        WHERE a.distance<16
    ) SELECT id,parent_id,title,distance FROM ancestors ORDER BY distance DESC",[16]);
    $rows=[];foreach($found as $position=>$row){$rows[]=[$position+1,(int)$row['id'],$row['title']];}
    $breadcrumb=implode(' → ',array_column($found,'title'));
    $checks=['ancestor_ids'=>hq113Ids($found)===[1,10,11,12,13,16],'reached_real_root'=>$found[0]['parent_id']===null,'current_category_last'=>(int)$found[count($found)-1]['id']===16,'breadcrumb_text'=>$breadcrumb==='Каталог → Электроника → Компьютеры → Ноутбуки → Игровые → Компактные'];
    $details=['breadcrumb'=>$breadcrumb,'selected_id'=>16,'order_was_computed_by_sql'=>true];
} finally {
    if($db->inTransaction()){$db->rollBack();}
    if($productsCreated){$db->exec('DROP TABLE '.hq113Name($products));}
    if($categoriesCreated){$db->exec('DROP TABLE '.hq113Name($categories));}
}
$remaining=hq113Rows($db,'SELECT COUNT(*) AS total FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA=DATABASE() AND TABLE_NAME IN (?,?)',[$categories,$products]);
$checks['owned_tables_removed']=(int)$remaining[0]['total']===0;
$db=null;
hq113Report('mysql113-hierarchy-breadcrumbs', ['Позиция ссылки', 'ID', 'Название'], $rows, $checks, $runtime, $details);
echo 'mysql113-hierarchy-breadcrumbs: saved result.html and result.json
';

mysql113-hierarchy-breadcrumbs: saved result.html and result.json

Открыть фактический HTML-результат · Скачать результат JSON

Попробуйте сами. Для Мониторов 15 цепочка будет Каталог 1 → Электроника 10 → Мониторы 15. Замените DESC на ASC и посмотрите, почему начинать меню с текущей страницы неудобно: порядок обхода и порядок показа — разные задачи.

Практический вопрос: Один self-JOIN теряет старших предков в хлебных крошках. Вопрос показывает только два уровня вместо всей цепочки. Наш отдельный каталог поднимается от ID 16 к корню и возвращает шесть упорядоченных строк для отдельных ссылок. Обсуждение показывает потребность; правила сверены по официальным источникам, код и данные написаны отдельно.

Вывести родителей и их ветви в устойчивом порядке меню

Раскрытое меню должно поставить «Быт» перед «Электроникой», а «Кухню» сразу под «Бытом». Сортировка всего ответа по id или depth разорвёт ветви. Нужен порядок «родитель, затем вся его ветка».

Исходные данные

Что получится

Главная запись SQL

WITH RECURSIVE tree AS (
    SELECT id,title,0 AS depth,
           CAST(CONCAT(LPAD(sort_order,5,'0'),'-',LPAD(id,10,'0'))
                AS CHAR(512) CHARACTER SET ascii) AS sort_path
    FROM categories WHERE parent_id IS NULL
    UNION ALL
    SELECT c.id,c.title,t.depth+1,
           CONCAT(t.sort_path,'/',LPAD(c.sort_order,5,'0'),'-',LPAD(c.id,10,'0'))
    FROM categories AS c JOIN tree AS t ON c.parent_id=t.id
    WHERE t.depth<16
)
SELECT id,title,depth FROM tree
ORDER BY sort_path COLLATE ascii_bin,id;

Этот фрагмент предполагает таблицы categories/products и данные выше. Самостоятельная программа создаёт нужные таблицы с уникальными именами, передаёт значения параметрами и не требует запуска предыдущего архива.

LPAD(sort_order,5,'0') дополняет позицию нулями слева; ID получает 10 цифр. Поэтому числовой порядок 2 перед 10 сохраняется и при строковом сравнении. Внутри одного уровня повторяющиеся позиции разрешены: ID служит вторым критерием. Разделитель / отделяет уровни. Например, путь Каталога начинается с 00001-0000000001, а путь Быта продолжает его сегментом /00010-0000000020.

Тип и доступная ширина столбца рекурсивного CTE выводятся из якорного SELECT. Если якорь отдаёт короткую строку, последующий CONCAT не делает её бесконечной. CAST(... AS CHAR(512)) задаёт место заранее. Пять цифр вмещают SMALLINT UNSIGNED, десять — INT UNSIGNED; при нашем максимуме 16 переходов путь занимает не более 288 символов. В реальном результате самый длинный путь равен 101 символу. Нельзя бездумно увеличивать глубину, оставляя прежнюю ширину.

ascii_bin явно задаёт сравнение технических сегментов, а не языковую сортировку русских заголовков. ORDER BY стоит во внешнем SELECT. Не полагаемся на порядок обхода движка; также не сортируем готовый массив PHP. В metrics.json сохранены полные sort_path для сопоставления с меню.

Границы и ошибки

sort_path вычисляется для выдачи, а не хранится здесь в таблице. Вывод огромного раскрытого дерева может быть дорогим независимо от способа сортировки. Для обычного меню часто достаточно подгружать прямых детей; для частого сложного чтения рассматривают заранее поддерживаемые пути или таблицу замыкания, учитывая цену их обновления.

<?php
declare(strict_types=1);
require __DIR__.'/hq113-helper.php';
$db=hq113Connect();
$runtime=['php'=>PHP_VERSION,'client'=>$db->getAttribute(PDO::ATTR_CLIENT_VERSION),'server'=>$db->getAttribute(PDO::ATTR_SERVER_VERSION)];
$prefix='mysql113_hierarchy_'.bin2hex(random_bytes(8));
$categories=$prefix.'_categories'; $products=$prefix.'_products';
$categoriesCreated=false; $productsCreated=false;
$details=[];
try {
    hq113CreateCategories($db,$categories); $categoriesCreated=true;
    hq113Seed($db,$categories);
    $ct=hq113Name($categories);
$found=hq113Rows($db,"WITH RECURSIVE tree AS (
        SELECT id,parent_id,title,sort_order,0 AS depth,
               CAST(CONCAT(LPAD(sort_order,5,'0'),'-',LPAD(id,10,'0')) AS CHAR(512) CHARACTER SET ascii) AS sort_path
        FROM $ct WHERE parent_id IS NULL
        UNION ALL
        SELECT c.id,c.parent_id,c.title,c.sort_order,t.depth+1,
               CONCAT(t.sort_path,'/',LPAD(c.sort_order,5,'0'),'-',LPAD(c.id,10,'0'))
        FROM $ct AS c JOIN tree AS t ON c.parent_id=t.id
        WHERE t.depth<16
    ) SELECT id,title,depth,sort_path FROM tree ORDER BY sort_path COLLATE ascii_bin,id");
    $rows=[];foreach($found as $position=>$row){$rows[]=[$position+1,(int)$row['id'],(int)$row['depth'],$row['title']];}
    $pathLengths=array_map('strlen',array_column($found,'sort_path'));
    $checks=['depth_first_business_order'=>hq113Ids($found)===[1,20,21,10,11,12,13,16,14,15,30],'all_categories_once'=>count($found)===11 && count(array_unique(hq113Ids($found)))===11,'paths_not_truncated'=>max($pathLengths)===101 && max($pathLengths)<512,'tree_not_plain_id_order'=>hq113Ids($found)!==[1,10,11,12,13,14,15,16,20,21,30]];
    $details=['sort_paths'=>array_column($found,'sort_path'),'id_digit_width'=>10,'position_digit_width'=>5,'maximum_path_characters'=>max($pathLengths),'declared_path_characters'=>512];
} finally {
    if($db->inTransaction()){$db->rollBack();}
    if($productsCreated){$db->exec('DROP TABLE '.hq113Name($products));}
    if($categoriesCreated){$db->exec('DROP TABLE '.hq113Name($categories));}
}
$remaining=hq113Rows($db,'SELECT COUNT(*) AS total FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA=DATABASE() AND TABLE_NAME IN (?,?)',[$categories,$products]);
$checks['owned_tables_removed']=(int)$remaining[0]['total']===0;
$db=null;
hq113Report('mysql113-hierarchy-stable-order', ['Позиция', 'ID', 'Глубина', 'Категория'], $rows, $checks, $runtime, $details);
echo 'mysql113-hierarchy-stable-order: saved result.html and result.json
';

mysql113-hierarchy-stable-order: saved result.html and result.json

Открыть фактический HTML-результат · Скачать результат JSON

Попробуйте сами. Дайте Электронике sort_order=5: её ветвь целиком окажется перед Бытом. При равных позициях двух детей первым будет меньший id. Заменять путь одним ORDER BY sort_order,id нельзя: тогда позиция Кухни начнёт сравниваться с позициями чужих уровней.

Практический вопрос: Почему растущий путь рекурсивного CTE вызывает Data too long. Начальная строка задаёт слишком узкий тип для последующих CONCAT. Здесь CAST в якоре выбирается вместе с границей глубины и шириной числовых сегментов; исходный код вопроса и ответа не копируется. Обсуждение показывает потребность; правила сверены по официальным источникам, код и данные написаны отдельно.

Отдать страницу товаров поддерева и точные общие счётчики

Страница «Ноутбуки» должна показать два самых дешёвых доступных опубликованных товара во всей своей ветке и общее количество подходящих товаров. Мониторы, чайник, скрытый прототип и отсутствующий на складе ноутбук в этот экран не входят.

Исходные данные

Что получится

Главная запись SQL

WITH RECURSIVE branch AS (
    SELECT id,0 AS depth FROM categories WHERE id=?
    UNION ALL
    SELECT c.id,b.depth+1 FROM categories c JOIN branch b ON c.parent_id=b.id
    WHERE b.depth<16
), eligible AS (
    SELECT p.id,p.title,p.price_rub,p.stock
    FROM products p JOIN branch b ON b.id=p.category_id
    WHERE p.active=1 AND p.stock>0
)
SELECT id,title,price_rub,stock,
       COUNT(*) OVER () AS total_products,
       SUM(stock) OVER () AS total_units,
       SUM(price_rub*stock) OVER () AS total_value_rub
FROM eligible ORDER BY price_rub,id LIMIT 2;
-- Параметр: 12, категория «Ноутбуки».

Этот фрагмент предполагает таблицы categories/products и данные выше. Самостоятельная программа создаёт нужные таблицы с уникальными именами, передаёт значения параметрами и не требует запуска предыдущего архива.

Сначала branch задаёт принадлежность категории выбранной ветке. eligible — следующий именованный результат того же WITH: он оставляет нужные поля подходящих товаров. У каждого товара ровно один category_id, а в корректном дереве каждая категория встречается в branch один раз. Поэтому JOIN даёт одну строку на товар, и счётчики не удваиваются.

COUNT(*) OVER () считает строки всего отфильтрованного eligible, SUM(stock) OVER () — доступные единицы, а сумма price_rub*stock — стоимость запасов этих товаров. Звёздочка в COUNT(*) означает подсчёт строк и не равна SELECT * с передачей всех полей. Оконные функции не объединяют товары в одну строку: итог повторяется рядом с каждой строкой страницы. Пустые скобки OVER () здесь означают весь подходящий набор, без разделения на группы.

Внешние ORDER BY price_rub,id и LIMIT 2 формируют страницу. SQL возвращает два товара, но счётчик 3 включает и подходящий товар 101 за пределами страницы. Это логическая форма запроса; оптимизатор может выполнять шаги иначе. Сортировка массива после получения первых двух произвольных строк не даёт эквивалентного результата. Подробности оконных расчётов доступны в оконных отчётах.

Не добавляйте к этой выдаче произвольный JOIN тегов или фотографий перед счётом: связь «один ко многим» размножит строки. Для фильтра по тегу уместен EXISTS, для нескольких независимых итогов — отдельная агрегация. Если товар может относиться к нескольким категориям ветки, используйте семантику существования подходящей связи или предварительно уникальный набор ID товаров.

Границы и ошибки

Пустая выдача не несёт строки с total_products=0: приложению нужно отдельно представить нулевой итог либо запросить сводку с тем же фильтром. Полные COUNT/SUM всё ещё обрабатывают весь подходящий набор, даже если клиент получает две строки. Это не бесплатная пагинация: индексы и план проверяют на реальных объёмах. Для мультитенантного каталога условия доступа должны охватывать категории, товары и сводку.

<?php
declare(strict_types=1);
require __DIR__.'/hq113-helper.php';
$db=hq113Connect();
$runtime=['php'=>PHP_VERSION,'client'=>$db->getAttribute(PDO::ATTR_CLIENT_VERSION),'server'=>$db->getAttribute(PDO::ATTR_SERVER_VERSION)];
$prefix='mysql113_hierarchy_'.bin2hex(random_bytes(8));
$categories=$prefix.'_categories'; $products=$prefix.'_products';
$categoriesCreated=false; $productsCreated=false;
$details=[];
try {
    hq113CreateCategories($db,$categories); $categoriesCreated=true;
    hq113Seed($db,$categories);
    $ct=hq113Name($categories);
hq113CreateProducts($db,$products,$categories); $productsCreated=true;
    $pt=hq113Name($products);
    $insert=$db->prepare("INSERT INTO $pt (id,category_id,title,price_rub,stock,active) VALUES (?,?,?,?,?,?)");
    foreach([[101,16,'Ноутбук Мини',90000,2,1],[102,14,'Ультрабук Лайт',70000,3,1],[103,15,'Монитор 24',15000,5,1],[104,21,'Чайник',3000,9,1],[105,13,'Прототип',120000,1,0],[106,12,'Ноутбук База',50000,0,1],[107,16,'Ноутбук Тревел',80000,1,1]] as $product){$insert->execute($product);}
    $sql="WITH RECURSIVE branch AS (
        SELECT id,0 AS depth FROM $ct WHERE id=?
        UNION ALL
        SELECT c.id,b.depth+1 FROM $ct AS c JOIN branch AS b ON c.parent_id=b.id
        WHERE b.depth<16
    ), eligible AS (
        SELECT p.id,p.title,p.price_rub,p.stock
        FROM $pt AS p JOIN branch AS b ON b.id=p.category_id
        WHERE p.active=1 AND p.stock>0
    )
    SELECT id,title,price_rub,stock,
           COUNT(*) OVER () AS total_products,
           SUM(stock) OVER () AS total_units,
           SUM(price_rub*stock) OVER () AS total_value_rub
    FROM eligible ORDER BY price_rub,id LIMIT 2";
    $found=hq113Rows($db,$sql,[12]);
    $rows=[];foreach($found as $row){$rows[]=[(int)$row['id'],$row['title'],(int)$row['price_rub'],(int)$row['stock'],(int)$row['total_products'],(int)$row['total_units'],(int)$row['total_value_rub']];}
    $checks=['page_ids'=>hq113Ids($found)===[102,107],'full_filtered_totals'=>count($rows)===2 && $rows[0][4]===3 && $rows[0][5]===6 && $rows[0][6]===470000 && array_slice($rows[0],4)===array_slice($rows[1],4),'one_row_per_product'=>count(array_unique(hq113Ids($found)))===count($found),'excluded_products_not_on_page'=>!array_intersect(hq113Ids($found),[103,104,105,106])];
    $details=['selected_category'=>12,'page_limit'=>2,'one_category_per_product'=>true,'filter_before_window_and_limit'=>true,'no_php_filter_or_sort'=>true];
} finally {
    if($db->inTransaction()){$db->rollBack();}
    if($productsCreated){$db->exec('DROP TABLE '.hq113Name($products));}
    if($categoriesCreated){$db->exec('DROP TABLE '.hq113Name($categories));}
}
$remaining=hq113Rows($db,'SELECT COUNT(*) AS total FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA=DATABASE() AND TABLE_NAME IN (?,?)',[$categories,$products]);
$checks['owned_tables_removed']=(int)$remaining[0]['total']===0;
$db=null;
hq113Report('mysql113-hierarchy-subtree-products', ['ID', 'Товар', 'Цена, руб.', 'Остаток', 'Всего товаров', 'Всего единиц', 'Стоимость запасов, руб.'], $rows, $checks, $runtime, $details);
echo 'mysql113-hierarchy-subtree-products: saved result.html and result.json
';

mysql113-hierarchy-subtree-products: saved result.html and result.json

Открыть фактический HTML-результат · Скачать результат JSON

Попробуйте сами. Смените LIMIT 2 на LIMIT 1: останется товар 102, а итог 3 / 6 / 470000 сохранится. Уберите active-фильтр: прототип 105 увеличит число товаров до 4, запас до 7 и стоимость до 590000 руб. Ни PHP-сортировка, ни простое count двух строк страницы этот итог не восстановят.

Распознать цикл и неполную ветку вместо бесконечного обхода

При импорте или переносе разделов можно получить кольцо A → B → C → A. Все ID существуют, поэтому FK не спасает. Нужно закончить чтение управляемо, назвать причину и не показывать обрезанную выдачу как полный каталог.

Исходные данные

Что получится

Главная запись SQL

WITH RECURSIVE walk AS (
    SELECT id,parent_id,0 AS depth,
           CAST(CONCAT('/',id,'/') AS CHAR(512)) AS visited,
           0 AS is_cycle
    FROM categories WHERE id=?
    UNION ALL
    SELECT c.id,c.parent_id,w.depth+1,
           CONCAT(w.visited,c.id,'/'),
           LOCATE(CONCAT('/',c.id,'/'),w.visited)>0
    FROM categories c JOIN walk w ON c.parent_id=w.id
    WHERE w.is_cycle=0 AND w.depth<?
)
SELECT id,parent_id,depth,visited,is_cycle FROM walk ORDER BY depth,id;
-- Параметры: стартовый ID и максимальное число переходов.
-- Полная программа дополнительно помечает обрезанные по глубине ветви.

Этот фрагмент предполагает таблицы categories/products и данные выше. Самостоятельная программа создаёт нужные таблицы с уникальными именами, передаёт значения параметрами и не требует запуска предыдущего архива.

visited — строка посещённых ID именно данного пути. Разделители важны: /9/ не должен совпадать с /90/. LOCATE ищет целый ограниченный разделителями ID. Повторную строку оставляем с is_cycle=1, чтобы увидеть причину, а WHERE w.is_cycle=0 запрещает следующий шаг из неё. Поэтому ошибочный импорт заканчивается четырьмя диагностическими строками 90,91,92,90.

Предел depth защищает от чрезмерной глубины, но отсутствие продолжения бывает двух видов: достигли листа или запретили идти дальше. В полном helper внешний SELECT помечает depth_cut, если на последнем разрешённом уровне ещё есть ребёнок. Для начала 10 и предела 2 такой границей становится 12. Если предел не дал дойти до повторного ID кольца, результат тоже остаётся неполным: наличие продолжения не позволяет объявить complete. Статус depth_exceeded заставляет отвергнуть неполную выдачу. Для нормальной ветки с пределом 16 получается complete.

Проверка переноса сначала получает полноценную ветку 10. ID нового родителя 16 находится среди её потомков, поэтому UPDATE не выполняется, parent_id остаётся 1. Это показывает нужное условие запрета, но не завершённую систему конкурентного редактирования. Между чтением и записью другой клиент может изменить дерево: операции переноса нужно сериализовать согласованным протоколом транзакций/блокировок. См. конкурирующие изменения.

UNION DISTINCT не является универсальной защитой этого запроса: повторный ID с новым depth или visited уже образует другую строку. Также увеличение cte_max_recursion_depth не исправляет цикл. Мы не меняем этот серверный предел и не рассчитываем, что ошибка лимита заменит предметную диагностику.

Границы и ошибки

Этот пример читает только достижимую от выбранного начала часть графа. Обход всех корней не обнаружит отдельное кольцо, у которого корня вообще нет; для проверки всего импорта нужен отдельный охват всех узлов. Ограничение 16 — переходы, не миллисекунды и не максимум строк. Для общего графа с несколькими родителями нужны иная модель связей и правила уникальности результата.

<?php
declare(strict_types=1);
require __DIR__.'/hq113-helper.php';
$db=hq113Connect();
$runtime=['php'=>PHP_VERSION,'client'=>$db->getAttribute(PDO::ATTR_CLIENT_VERSION),'server'=>$db->getAttribute(PDO::ATTR_SERVER_VERSION)];
$prefix='mysql113_hierarchy_'.bin2hex(random_bytes(8));
$categories=$prefix.'_categories'; $products=$prefix.'_products';
$categoriesCreated=false; $productsCreated=false;
$details=[];
try {
    hq113CreateCategories($db,$categories); $categoriesCreated=true;
    hq113Seed($db,$categories);
    $ct=hq113Name($categories);
$insert=$db->prepare("INSERT INTO $ct (id,parent_id,title,sort_order) VALUES (?,?,?,?)");
    foreach([[90,null,'Импорт A',1],[91,90,'Импорт B',1],[92,91,'Импорт C',1]] as $item){$insert->execute($item);}
    $db->prepare("UPDATE $ct SET parent_id=? WHERE id=?")->execute([92,90]);
    $cycle=hq113GuardedWalk($db,$categories,90,16);
    $cycleAtDepthBoundary=hq113GuardedWalk($db,$categories,90,2);
    $shallow=hq113GuardedWalk($db,$categories,10,2);
    $complete=hq113GuardedWalk($db,$categories,10,16);
    $moveRefused=hq113WalkStatus($complete)!=='complete' || in_array(16,hq113Ids($complete),true);
    if(!$moveRefused){throw new RuntimeException('Expected refusal: target is inside the source subtree');}
    // No UPDATE moves category 10 under 16: the preliminary check rejected it.
    $parentAfter=(int)hq113Rows($db,"SELECT parent_id FROM $ct WHERE id=?",[10])[0]['parent_id'];
    $rows=[
        ['Цикл после импорта',implode(', ',hq113Ids($cycle)),hq113WalkStatus($cycle)],
        ['Предел 2 перехода',implode(', ',hq113Ids($shallow)),hq113WalkStatus($shallow)],
        ['Полная учебная ветка',implode(', ',hq113Ids($complete)),hq113WalkStatus($complete)],
        ['Перенос 10 под его потомка 16','parent_id категории 10 = '.$parentAfter,$moveRefused?'refused':'accepted']
    ];
    $checks=['fk_allows_cycle'=>(int)hq113Rows($db,"SELECT parent_id FROM $ct WHERE id=90")[0]['parent_id']===92,'cycle_detected_and_bounded'=>hq113WalkStatus($cycle)==='cycle_detected' && hq113Ids($cycle)===[90,91,92,90],'cycle_at_boundary_not_complete'=>hq113WalkStatus($cycleAtDepthBoundary)==='depth_exceeded','depth_cut_not_success'=>hq113WalkStatus($shallow)==='depth_exceeded' && hq113Ids($shallow)===[10,11,15,12],'healthy_branch_complete'=>hq113WalkStatus($complete)==='complete' && hq113Ids($complete)===[10,11,15,12,13,14,16],'unsafe_move_not_executed'=>$moveRefused && $parentAfter===1];
    $details=['cycle_rows'=>$cycle,'cycle_at_depth_boundary'=>$cycleAtDepthBoundary,'depth_limited_rows'=>$shallow,'complete_rows'=>$complete,'max_allowed_depth'=>16,'global_recursion_limit_unchanged'=>true];
} finally {
    if($db->inTransaction()){$db->rollBack();}
    if($productsCreated){$db->exec('DROP TABLE '.hq113Name($products));}
    if($categoriesCreated){$db->exec('DROP TABLE '.hq113Name($categories));}
}
$remaining=hq113Rows($db,'SELECT COUNT(*) AS total FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA=DATABASE() AND TABLE_NAME IN (?,?)',[$categories,$products]);
$checks['owned_tables_removed']=(int)$remaining[0]['total']===0;
$db=null;
hq113Report('mysql113-hierarchy-cycle-and-depth', ['Проверка', 'Посещённые ID / состояние', 'Результат'], $rows, $checks, $runtime, $details);
echo 'mysql113-hierarchy-cycle-and-depth: saved result.html and result.json
';

mysql113-hierarchy-cycle-and-depth: saved result.html and result.json

Открыть фактический HTML-результат · Скачать результат JSON

Попробуйте сами. Верните parent_id категории 90 в NULL: обход 90,91,92 должен получить complete. Для ветки 10 увеличьте предел с 2 до 4: появятся все семь категорий и complete. Не принимайте status=not_found за complete, если выбранного ID нет в таблице.

Практический вопрос: Как не допустить круговую связь между родителями и детьми. Существующие внешние ключи не устраняют кольцо связей. Оригинальный пример отдельно проверяет обнаружение цикла при чтении, неполноту из-за глубины и предварительный отказ переноса узла под своего потомка; ограничения конкурентного редактирования объясняются явно. Обсуждение показывает потребность; правила сверены по официальным источникам, код и данные написаны отдельно.

Выбрать форму хранения и запроса под экран

Для обычных категорий начните с id/parent_id, а тип запроса выбирайте по экрану. Не извлекайте весь каталог и все товары ради одной ветки. Сначала задайте связь, доступ, фильтры, порядок и объём результата. Связанные экраны объясняют границы JOIN и агрегации; запрос под нужную страницу — форму выдачи. JSON-документы MySQL подходят для иных свойств данных и не заменяют внешние ключи автоматически.