Building a nested array from DB data

PHP
1

OK I have the following result set…

“name” “category_id” “lft” “rgt” “parent_id” “depth”
“For Sale” “2” “2” “11” “1” “1”
“Cars” “4” “3” “6” “2” “2”
“Parts” “5” “7” “10” “2” “2”
“Wanted” “3” “12” “17” “1” “1”
“Cars” “8” “13” “14” “3” “2”
“Parts” “9” “15” “16” “3” “2”

And from it want to build a nested array. How would I go about doing it either using the lft rgt values or recursively using the parent_id field. Depth is not necessarily limited to 2.

Alternatively If I can format the data into a nested set of html lists that would also achieve the same end result.

2

Also if anybody can modify this query to also return an item count there may be some beer tokens on offer.


SELECT node.name, 
	node.category_id, 
	node.lft,
	node.rgt, 
	node.parent_id, 
	(COUNT(parent.category_id) - (sub_tree.depth + 1)) AS depth
FROM  millnedev.ips_classifieds_categories AS node,
	millnedev.ips_classifieds_categories AS parent,
	millnedev.ips_classifieds_categories AS sub_parent,
	(
		SELECT node.category_id, (COUNT(parent.category_id) - 1) AS depth
		FROM millnedev.ips_classifieds_categories AS node,
		millnedev.ips_classifieds_categories AS parent
		WHERE node.lft BETWEEN parent.lft AND parent.rgt
		AND node.category_id = '1'
		GROUP BY node.category_id
		ORDER BY node.lft
	) AS sub_tree
WHERE node.lft BETWEEN parent.lft AND parent.rgt
	AND node.lft BETWEEN sub_parent.lft AND sub_parent.rgt
	AND sub_parent.category_id = sub_tree.category_id
GROUP BY node.category_id
HAVING depth BETWEEN 1 AND 2
ORDER BY node.lft;

The Items table is “classifieds_items” and is linked to categories using the “category_id” field.

Many thanks.

3

Follow this link:
Implementing An N-Level Nested Tree In PHP And PostgreSQL
Thorough reading may take time but the result will be cent % fruitful (y).
thanks

4

Thanks very much, that is a superb article! I’ve just spent pretty much all day digesting it and together with the sitepoint article I now have a very good understanding of nested sets and also a very useful class to work with.