Page 1 of 1

problem with recursive function

Posted: Wed Oct 29, 2008 10:36 pm
by ratamaster
Hi
I have a recursive function which lists categories, sub-categories and the products from each categories
The function start to iterate from the root and creates the tree. Next I describe the 2 tables in use.


The tables are:
categories:
CatId---CatParentId--CatName
1--------0-----------------xxxx0
2--------1-----------------xxxx0-1
3--------1-----------------xxxx0-2
4--------3-----------------xxxx1-1
5--------3-----------------xxxx1-2
6--------0-----------------xxxx1

This would be a visual representation produced by the function, actually its a unsorted list (ul-li):

xxxx0 (CatId=1, CatParentId=0)
------xxxx0-1(CatId=2, CatParentId=1)
------xxxx0-2(CatId=3, CatParentId=1)
-----------------xxxxx1-1 (CatId=4, CatParentId=3)
-----------------xxxxx1-2 (CatId=5, CatParentId=3)
xxxx1 (CatId=6, CatParentId=0)


Products table:
imported_data_copy ( products)
ImpDatId----CatId---ProdName
1--------------1--------prod aaa
2--------------1--------prod bbb
3--------------3--------prod cccc
4--------------3--------prod cccc jjjj
5--------------6--------prod cccc hhhh

Because there are around 1000 products, I decided to not list the tree products at initialization, just the categories. Each time the user clicks on a category ( the tree is "closed" this means that only the root categories are shown, CatParentId=0 ), an ajax calling is executed, this one returns the products from the clicked category

At this point all works fine :)

As you can see, categories and products are related with CatId(fk), this field is in both tables. With CatId we know on which category a product belongs.

The problem starts when I need to add a seach input text, the objective of this seach engine ( it seaches on ProdName ), is to ONLY show the categories FROM THE ROOT NODE that are related to the searched product.

Example: the user seaches "cccc", the output should be:

xxxx0 (CatId=1, CatParentId=0, root node)
------xxxx0-2 (CatId=3, CatParentId=1)
xxxx1 (CatId=6, CatParentId=0)


My function:

Code: Select all

 
function recursiveTree($categoryId){
                     $arrStr = array();
                     $sql='SELECT CatId, CatName FROM '.tblCATEGORIES.' WHERE CatParentId ='.$categoryId;
                     $result=mysql_query($sql);
                     if(mysql_num_rows($result)){
                     
                        
                        if($categoryId>0){
                           $arrStr[]='<ul style="display:none;" class="ulCat">'; 
                        }
                        
                         
                        
                        while($row = mysql_fetch_array($result)){
                           $arrStr[]='<li id="idtree_'.$row['CatId'].'" class="catnode">'
                                   .'<div class="divcat">'
                                     .'<div style="float:left">'
                                       .'<img src="./images/elbow-end-plus-nl.gif" style="cursor:pointer" onclick="defineImageNode(this);seekUl(document.getElementById(\'idtree_'.$row['CatId'].'\'));callProductsFromCat('.$row['CatId'].');" />'
                                     .'</div>'
                                     .'<div style="white-space: nowrap;padding-left:3px" id="catname_'.$row['CatId'].'">'
                                       .$row['CatName'].$row['CatId']
                                     .'</div>'
                                     .'<div style="clear:both"></div>'
                                   .'</div>'
                                 .'<ul style="display:none;" id="ulProd_'.$row['CatId'].'"></ul>';                
                           $arrStr[]=$this->recursiveTree($row['CatId']);
                           $arrStr[]='</li>';
                        }
                        
                        $arrStr[]='</ul>';
                     }  
               
                     return implode('',$arrStr); 
          
                  }
 

Ok, I hope someone could help me
Thanks and sorry about my english.

Re: problem with recursive function

Posted: Thu Oct 30, 2008 10:20 am
by pickle
I hate writing my own recursive functions, let alone debugging someone else's ;)

However, let me see if I understand what you have to do.

Basically, you have to search through each product for a certain search string. If the string is found in a particular product, you need to find it's category ancestry. Am I about right?

The first thing I would do is get rid of any display logic in your function - it's only muddling things up. Have functions to get the data, and another to display it - that'll make things much easier to debug & think through.

I don't think the function you currently have can be made to do all of what you want. To be honest, I don't think you should try. Make another function to retrieve the initial category ids based on an the search. Have this function build a multi-dimensional array of properly formatted search results, make a final function to display the results. More functions, but clearer separation of logic.

Re: problem with recursive function

Posted: Thu Oct 30, 2008 10:37 am
by ratamaster
pickle wrote:I hate writing my own recursive functions, let alone debugging someone else's ;)

However, let me see if I understand what you have to do.

Basically, you have to search through each product for a certain search string. If the string is found in a particular product, you need to find it's category ancestry. Am I about right?
Yes, their category ancestries, from the first one till the last one.
pickle wrote: I don't think the function you currently have can be made to do all of what you want. To be honest, I don't think you should try. Make another function to retrieve the initial category ids based on an the search. Have this function build a multi-dimensional array of properly formatted search results, make a final function to display the results. More functions, but clearer separation of logic.
I have this new one:

Code: Select all

 
function get_path($categoryId) {
   // look up the parent of this node
   $sql='SELECT DISTINCT `categories`.CatParentId, `categories`.CatName '
   .'FROM `categories`, imported_data_copy '
   .'WHERE `categories`.CatId ='.$categoryId.' '
   .'AND imported_data_copy.CatId = `categories`.CatId '  
   .'AND imported_data_copy.ProdName LIKE "Industrie-Umlufterhitzer 30 kW" ';
 
   $result=mysql_query($sql)or die(mysql_error());
   $row = mysql_fetch_array($result);
   // save the path in this array
   $path = array();
   // only continue if this $node isn't the root node
   // (that's the node with no parent)
 
   if ($row['CatParentId']!='') {
       $path[] = $row['CatParentId'];
       // we should add the path to the parent of this node
       // to the path
       $path = array_merge(get_path($row['CatParentId']), $path);
   }
   // return the path
   return $path;
}
 
print_r(get_path(2411)); 
 
This outputs
Array ( [0] => 2410)

2410 is the parent of 2411.
The query searches when ProdName is LIKE 'N'
But currently the function doesn't fetch the parent of 2410, which is 0 , because 0 has not products LIKE 'N'
If I change the query to:

Code: Select all

 
$sql='SELECT CatParentId, CatName FROM categories 
WHERE ImpFileId=5 AND CatId ='.$categoryId; 
 
I get: Array ( [0] => 0 [1] => 2410 )
But I'm not treating the product string.
How could I handle the parents and the product string at the same time?

Re: problem with recursive function

Posted: Thu Oct 30, 2008 10:51 am
by pickle
Like I said, you'll need two different functions. Searching for a product name is the first step & isn't repeated, so it can't/shouldn't be in your recursive function. You should do the search once, get the relevant category ids, then pass those ids off to the recursive function.