Struktura drzewa php mysql

Pokażę jak najlepiej przechowywać strukturę drzewiastą w bazie danych oraz jak później odwoływać się do niej za pomocą php. Załóżmy, że mamy następującą strukturę. tree structure Struktura w bazie jest następująca. struktura w bazie danych

Pobieranie dzieci określonego elementu

Teraz zobaczymy jak łatwo dzięki lft i rght pobierzemy potrzebne informacje z bazy danych. Na przykład jeśli chcemy pobrać wszystkie kategorie podrzędne kategorii Tatry wraz z nią samą wystarczy poniższe zapytanie.

SELECT * FROM tree WHERE lft >= 2 AND rght <= 13;

Jeśli chcemy znaleźć przodków kategorii Makowski.

SELECT * FROM tree WHERE lft < 19 AND rght > 20;

W przypadku poszukiwania bezpośredniego rodzica.

SELECT * FROM tree WHERE (lft < 19 AND lft > 1) AND (rght > 20 AND rght < 22);

Przebudowa drzewa

Poniżej przykładowa funkcja przebudowująca strukturę drzewa. Przebudowę rozpoczynamy od wartości licznika $lft oraz elementów głównych.

function rebuildTree($parent_id, $lft) {
    $rght = $lft+1;   

    $result = mysql_query('SELECT id_category FROM categories WHERE id_parent="'.$parent_id.'";');   

    while ($row = mysql_fetch_array($result)) {
        $rght = rebuildTree($row['id_category'], $rght);   
    }     
    mysql_query('UPDATE categories SET lft='.$lft.', rght='.$rght.' WHERE id_category="'.$parent_id.'"');   
   
    return $rght+1;   
}   
rebuildTree(0,0);

Algorytm przebudowy można opisać w kilku krokach:

Zaczynamy od przejścia przez elementy główne (nie mające rodzica) i licznika lft = 0.
Zwiększamy licznik o 1
Pobieramy kategorie, dla których id_parent jest równe przekazanemu parent.
1. Jeśli znaleziono dzieci wywyłujemy rekurencyjnie rebuildTree z nowymi parametrami (teraz jako parent ustawiamy id_category znalezionego węzła).
2. Jeśli nie znaleziono dzieci aktualizujemy bazę odpowiednimi wartościami lft i rght.
Zwracamy wartość rght powiększoną o 1.

Może się też okazać, że będziemy chcieli obliczyć głębokość zagnieżdżenia dla danego elementu. Realizuje to funkcja na poniższym listingu. Oprócz ustalenia głebokości (depth) aktualizuje wiersze w bazie na odpowiedni level_depth.

function depths() {
	$q = 'SELECT node.id_category, (COUNT(parent.id_category) - 1) AS depth';
	$q .= ' FROM categories AS node';
	$q .= ' CROSS JOIN categories AS parent';
	$q .= ' WHERE node.lft BETWEEN parent.lft AND parent.rght';
	$q .= ' GROUP BY node.id_category';
	$q .= ' ORDER BY node.lft';

	$result = mysql_query($q);
	if(!$result) {
		die('Invalid query: '. mysql_error());
	}

	$tree = array();
	while ($row = mysql_fetch_assoc($result)) {
	  $tree[] = $row;
	  $r = mysql_query('UPDATE categories SET level_depth='.$row['depth'].' WHERE id_category="'.$row['id_category'].'"');  
		if(!$r) {
			die('Invalid query: '. mysql_error());
		}
	}
	return $tree;
}