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'",
mysql_real_escape_string($category)));
while($row = $this->db->fetch()){
$children[] = $row['id'];
}
$temp_array = array();
foreach($children as $child){
$temp_array=$this->get_all_children($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 :)
http://www.sitepoint.com/article/hierarchical-data-database/1
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.
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'",
mysql_real_escape_string($category)));
while($row = $this->db->fetch()){
$children[] = $row['id'];
}
$temp_array = array();
foreach($children as $child){
$temp_array=$this->get_all_children($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 :)
http://www.sitepoint.com/article/hierarchical-data-database/1
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.
Comments:
<< Home
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.
# posted by 
: 7:38 PM, October 25, 2005
: 7:38 PM, October 25, 2005
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
# posted by : 1:18 PM, October 26, 2005
: 1:18 PM, October 26, 2005<< Home
Archives
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
