[PHP] Drzewo kategorii Opcje

[sebekzosw]

Witam,

mam taką strukturę bazy:

[SQL] pobierz, plaintext 
CREATE TABLE IF NOT EXISTS `all_cms_categories` (
  `id` mediumint(8) UNSIGNED NOT NULL AUTO_INCREMENT,
  `parent` mediumint(8) UNSIGNED NOT NULL DEFAULT '0',
  `alias` text COLLATE utf8_polish_ci,
  `title` text COLLATE utf8_polish_ci,
  `icon` text COLLATE utf8_polish_ci,
  `cid` mediumint(8) UNSIGNED NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`)
) 
 
INSERT INTO `all_cms_categories` (`id`, `parent`, `alias`, `title`, `icon`, `cid`) VALUES
(1, 0, 'produkty', 'Produkty', NULL, 0),
(2, 1, 'baterie', 'Baterie', 'baterie.png', 0),
(18, 1, 'sedesy', 'Sedesy', NULL, 0),
(4, 2, NULL, 'Baterie wannowo - prysznicowe', NULL, 0),
(5, 2, NULL, 'Baterie kuchenne', NULL, 0),
(6, 2, NULL, 'Baterie prysznicowe', NULL, 0),
(7, 2, NULL, 'Zestawy baterii', NULL, 0),
(8, 2, NULL, 'Baterie wannowo - prysznicowe podtynkowe', NULL, 0),
(10, 1, 'bidety', 'Bidety', NULL, 0),
(11, 10, 'bidety2', 'Bidety', NULL, 0),
(12, 10, 'deski-bidetowe', 'Deski bidetowe', NULL, 0),
(13, 10, 'baterie-bidetowe', 'Baterie bidetowe', NULL, 0),
(14, 10, 'syfony-do-bidetow', 'Syfony do bidetów', NULL, 0),
(15, 10, 'syfony-do-bidetow2', 'Syfony do bidetów', NULL, 0),
(17, 2, 'zawory-katowe', 'Zawory kątowe', NULL, 0);
[SQL] pobierz, plaintext 

oraz funkcje PHP

[PHP] pobierz, plaintext 
function CreateTree($array)
{
	$refs = array();
	$list = array();
 
	foreach($array as $data)
	{
		$thisref = &$refs[$data['id']];
		$thisref = $data;
 
		if($data['parent'] == 0)
			$list[$data['id']] = &$thisref;
		else
			$refs[$data['parent']]['children'][$data['id']] = &$thisref;
	}
 
	return $list;
}
[PHP] pobierz, plaintext 

a dzięki temu mogę wygenerować taką oto strukturę:

Kod

Array
(
    [1] => Array
        (
            [id] => 1
            [parent] => 0
            [alias] => produkty
            [title] => Produkty
            [icon] =>
            [cid] => 0
            [children] => Array
                (
                    [2] => Array
                        (
                            [id] => 2
                            [parent] => 1
                            [alias] => baterie
                            [title] => Baterie
                            [icon] => baterie.png
                            [cid] => 0
                            [children] => Array
                                (
                                    [4] => Array
                                        (
                                            [id] => 4
                                            [parent] => 2
                                            [alias] =>
                                            [title] => Baterie wannowo - prysznicowe
                                            [icon] =>
                                            [cid] => 0
                                            [level] => 2
                                        )

                                    [5] => Array
                                        (
                                            [id] => 5
                                            [parent] => 2
                                            [alias] =>
                                            [title] => Baterie kuchenne
                                            [icon] =>
                                            [cid] => 0
                                            [level] => 2
                                        )

                                    [6] => Array
                                        (
                                            [id] => 6
                                            [parent] => 2
                                            [alias] =>
                                            [title] => Baterie prysznicowe
                                            [icon] =>
                                            [cid] => 0
                                            [level] => 2
                                        )

                                    [7] => Array
                                        (
                                            [id] => 7
                                            [parent] => 2
                                            [alias] =>
                                            [title] => Zestawy baterii
                                            [icon] =>
                                            [cid] => 0
                                            [level] => 2
                                        )

                                    [8] => Array
                                        (
                                            [id] => 8
                                            [parent] => 2
                                            [alias] =>
                                            [title] => Baterie wannowo - prysznicowe podtynkowe
                                            [icon] =>
                                            [cid] => 0
                                            [level] => 2
                                        )

                                    [17] => Array
                                        (
                                            [id] => 17
                                            [parent] => 2
                                            [alias] => zawory-katowe
                                            [title] => Zawory kątowe
                                            [icon] =>
                                            [cid] => 0
                                            [level] => 2
                                        )

                                )

                            [level] => 1
                        )

                    [18] => Array
                        (
                            [id] => 18
                            [parent] => 1
                            [alias] => sedesy
                            [title] => Sedesy
                            [icon] =>
                            [cid] => 0
                            [level] => 1
                        )

                    [10] => Array
                        (
                            [id] => 10
                            [parent] => 1
                            [alias] => bidety
                            [title] => Bidety
                            [icon] =>
                            [cid] => 0
                            [children] => Array
                                (
                                    [11] => Array
                                        (
                                            [id] => 11
                                            [parent] => 10
                                            [alias] => bidety2
                                            [title] => Bidety
                                            [icon] =>
                                            [cid] => 0
                                            [level] => 2
                                        )

                                    [12] => Array
                                        (
                                            [id] => 12
                                            [parent] => 10
                                            [alias] => deski-bidetowe
                                            [title] => Deski bidetowe
                                            [icon] =>
                                            [cid] => 0
                                            [level] => 2
                                        )

                                    [13] => Array
                                        (
                                            [id] => 13
                                            [parent] => 10
                                            [alias] => baterie-bidetowe
                                            [title] => Baterie bidetowe
                                            [icon] =>
                                            [cid] => 0
                                            [level] => 2
                                        )

                                    [14] => Array
                                        (
                                            [id] => 14
                                            [parent] => 10
                                            [alias] => syfony-do-bidetow
                                            [title] => Syfony do bidetów
                                            [icon] =>
                                            [cid] => 0
                                            [level] => 2
                                        )

                                    [15] => Array
                                        (
                                            [id] => 15
                                            [parent] => 10
                                            [alias] => syfony-do-bidetow2
                                            [title] => Syfony do bidetów
                                            [icon] =>
                                            [cid] => 0
                                            [level] => 2
                                        )

                                )

                            [level] => 1
                        )

                )

            [level] => 0
        )

)

i chciałbym się dowiedzieć w jaki sposób mogę pobrać kategorię tylko z gałęzi "Bidety"? Jakiś pomysł? Słyszałem o "The Nested Set" jednak mam za mało czasu na zapoznanie się i przerobieniu tego. Przy następnym projekcie już zastosuje ten nowy model.

[abort]

Przy takim drzewie wygląda mi na to, że to nie problem.
Zapytanie do bazy:

[SQL] pobierz, plaintext 
SELECT * FROM `all_cms_categories` WHERE `alias` = 'bidety' LIMIT 1
[SQL] pobierz, plaintext 

i dostaniesz id i parent, w takim układzie ze zwróconej przez Twoją funkcję tablicy (załóżmy że pod zmienną $artykuly) wybierasz odpowiednie poddrzewo, czyli np. $artykuly[$parent][$id] (zmienne z odpowiednich pól rezultatu z bazy).

WADY:
1. nadmiarowe odwołanie do SQL
2. zadziała tylko przy pierwszym poziomie zagnieżdżenia

Jeśli nie chcesz w taki sposób - musisz przetworzyć indeksy tablicy w PHP.

[sebekzosw]

sposób odpada. Chce pracować tylko na tablicy bez zapytań w pętli. O takim rozwiązaniu akurat wiem.

[abort]

No to jeśli nie pomoże array_search, to zostaje tylko rekurencyjne przeszukiwanie tablicy...

[sebekzosw]

a ktoś ma jakiś pomysł?

[lukaskolista]

Nie chcesz korzystac z bazy, ale z nested set (ktory dziala na bazie) juz tak. Proponuje sie zdecydowac. Majac gotowy modul do obslugi nested sets to jest chwila. Jak bedziesz chcial to zaimplementujesz nested sets szybko, w sieci jest masa gotowcow, wystarczy sciagnac klase i dodac 2 kolumny do tabeli.

[sebekzosw]

jest różnica między 1-3 zapytaniami niż pętlą zapytań pobierająca po kolei podkategorie

[gothye]

Lepszym rozwiązaniem jest drzewo kategorii left/right