Top

Converting Nested Set Model Data in to Multi-dimensional Arrays in PHP

SEMLabs.co.uk / General  / Converting Nested Set Model Data in to Multi-dimensional Arrays in PHP

Converting Nested Set Model Data in to Multi-dimensional Arrays in PHP

If you are using the Nested Set Model to store hierarchical data in SQL, this PHP function will help you convert that data from a flat array into a multi-dimensional array.

The function requires that you query some data with a value representing it’s hierarchical depth. You can do this using a query like:

	
		SELECT node.name, (COUNT(parent.name) - 1) AS depth
		FROM nested_category AS node,
		nested_category AS parent
		WHERE node.lft BETWEEN parent.lft AND parent.rgt
		GROUP BY node.name
		ORDER BY node.lft;
	

In this query, a value will be returned to signify the row’s depth using the key ‘depth’. Once you have your data in associative array with depth key, pass the array to the function as the first argument and pass the name of the depth key as the second argument. The second argument defaults to ‘depth’. The function will return something like this:

If you’re not familiar with the Nested Set Model, you can read more about it here. I’ve also heard it referred to at the infinite category table thingy.

It’s a simple technique that is good for storing such things as categories or hierarchical site structure in a table. Rather than using multiple queries or multiple joins on the same table using aliases, the Nested Set Model allows you to store hierarchical data in a manner that requires less bloat when it comes to querying.

This function is available under the MIT License.

Share