Tidbits @ Kassemi

A collection of opinions, thoughts, tricks and misc. information.

Wednesday, August 24, 2005


Working with parents and children

Here's a quick little puzzle:

You have a database that contains the following:

id | parent | title | description
1 0 blah blah
2 1 blah subcat Subcategory under the blah title.
3 2 blah subcat subc Subcategory under id 2

This must be fairly common with category listings. The categories can be multi-leveled, a lot like the folder system on your hard drive (eg /, /usr, /usr/local, where /usr/local is a second level directory). The problem comes when you want to know what categories are children of the parent category to an unlimited number of levels.

Since I don't know my SQL that well, I decided to use a simple PHP solution... It's obvious that this needs to be recursive. My worry is that when there are thousands of categories, the processing requirements for sorting through them will be too high. If you can think of something better, please post. Here's what I've got:

function get_all_children($category="0"){
$children = array();
$this->db->query(sprintf("select distinct id from categories where parent='%s'",

while($row = $this->db->fetch()){
$children[] = $row['id'];
$temp_array = array();
foreach($children as $child){

$children = array_merge($children, $temp_array);
$children = array_unique($children);
array_unshift($children, $category);
return $children;

There is a nice explaination here that recommends I don't store the data in this method, and instead suggests the Modified Preorder Tree Traversal method. But that really doesn't help someone who's already got a functional method for the simple parent method, and doesn't feel like modifying thousands of lines of code to get their program working faster. Argh. Wish I'd seen this earlier :)


Instead of changing everything right now, I've decided to run a cron job with my update procedure (which I will publish as soon as I'm no longer bound by confidentiality. Soon, I hope, as the project appears to be nearly done.

Take it easy everyone.

What about loading all the entries using one query, storing in an array, and then just going through that array in a recursive function? Nice and simple and only one query. It could be a load on memory with too many items though.
That would definitely work with a low number of items, but for working with a large number you could easily exceed the default memory allotted by PHP, and could end up exceeding even the amount of physical memory on your computer...
Post a Comment

<< Home


August 2005   September 2005   October 2005   November 2005   December 2005   January 2006   February 2006   March 2006   April 2006   June 2006   July 2006   August 2006   September 2006   October 2006   November 2006  

This page is powered by Blogger. Isn't yours?