CREATE TABLE categories (
id INT PRIMARY KEY,
name VARCHAR(255) NOT NULL,
parent INT
);
INSERT INTO categories (id, name, parent) VALUES
(1, 'shoes', 0),
(2, 'women_shoes', 1),
(3, 'man_shoes', 1),
(4, 'sweets', 0),
(5, 'man_sweets', 4),
(6, 'women_sweets', 4),
(7, 'women_red_sweets', 6),
(8, 'women_green_sweets', 6),
(9, 'men_blue_sweets', 5);
<?php
function categories_list($mysqli, $id = 0, $list = []) {
$stmt = $mysqli->prepare("SELECT * FROM categories WHERE parent = ?");
$stmt->bind_param('i', $id);
$stmt->execute();
foreach ($stmt->get_result() as $value) {
$list[] = $value['id'];
//var_export($value);
array_push($list, ...categories_list($mysqli, $value['id'], $list));
}
return array_unique($list);
}
var_export(categories_list($mysqli, 0));