My this recursive code not working?

[Angry Coder]

Hello,

I have the following table structure and data:

-- 
-- Table structure for table `category`
-- 

CREATE TABLE `category` (
  `categoryid` int(11) NOT NULL auto_increment,
  `catname` varchar(100) NOT NULL default '',
  `parentcatid` int(11) NOT NULL default '0',
  PRIMARY KEY  (`categoryid`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=5 ;

-- 
-- Dumping data for table `category`
-- 

INSERT INTO `category` (`categoryid`, `catname`, `parentcatid`) VALUES 
(1, 'test', 0),
(2, 'son of test', 1),
(3, 'test2', 0),
(4, 'test3', 0);

And the following PHP code:

<?php

// $parent is the parent of the children we want to see
// $level is increased when we go deeper into the tree,
//        used to display a nice indented tree
function display_children($parent, $level) {
   // retrieve all children of $parent
   $result = mysql_query('SELECT catname as title FROM category '.
                          'WHERE parentcatid="'.$parent.'";');

   // display each child
   while ($row = mysql_fetch_array($result)) {
       // indent and display the title of this child
       echo str_repeat('  ',$level).$row['title']."\n";

       // call this function again to display this
       // child's children
       display_children($row['title'], $level+1);
   }
} 
        
display_children(1,0);

?>

Can anyone tell me why the code is not working?

Please help.

[Jake Arkinstall]

I think it may be that it's running indefinately - you haven't told it to ever stop running display_children.

Try this. It only recurses if the child has children of it's own.

<?php
function display_children($parent, $level){
    $result = mysql_query('SELECT catname as title FROM category'.
                        'WHERE parentcatid="'.$parent.'";');
    while ($row = mysql_fetch_array($result)){
        echo str_repeat('  ',$level).$row['title']."\n";
        $result2 = mysql_query('SELECT catname as title FROM category'.
                        'WHERE parentcatid="'.$parent.'";');
        if(mysql_num_rows($result2)){
            display_children($row['title'], $level+1);
        }
    }
}
display_children(1,0);
?>

[Angry Coder]

Thanks but it doesn't work.

[taliesinnz]

I think it may be that it's running indefinately - you haven't told it to ever stop running display_children.

Try this. It only recurses if the child has children of it's own.

<?php
function display_children($parent, $level){
    $result = mysql_query('SELECT catname as title FROM category'.
                        'WHERE parentcatid="'.$parent.'";');
    while ($row = mysql_fetch_array($result)){
        echo str_repeat('  ',$level).$row['title']."\n";
        $result2 = mysql_query('SELECT catname as title FROM category'.
                        'WHERE parentcatid="'.$parent.'";');
        if(mysql_num_rows($result2)){
            display_children($row['title'], $level+1);
        }
    }
}
display_children(1,0);
?>

If there are no results returned from the query mysql_fetch_array($result)), will return null.

The while will not execute because of this.

[taliesinnz]

Hello,

I have the following table structure and data:

[code]--
-- Table structure for table `category`
--

CREATE TABLE `category` (
`categoryid` int(11) NOT NULL auto_increment,
`catname` varchar(100) NOT NULL default '',
`parentcatid` int(11) NOT NULL default '0',
PRIMARY KEY (`categoryid`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=5 ;

--
-- Dumping data for table `category`
--

And the following PHP code:

<?php

// $parent is the parent of the children we want to see
// $level is increased when we go deeper into the tree,
//        used to display a nice indented tree
function display_children($parent, $level) {
   // retrieve all children of $parent
   $result = mysql_query('SELECT catname as title FROM category '.
                          'WHERE parentcatid="'.$parent.'";');

   // display each child
   while ($row = mysql_fetch_array($result)) {
       // indent and display the title of this child
       echo str_repeat('  ',$level).$row['title']."\n";

       // call this function again to display this
       // child's children
       display_children($row['title'], $level+1);
   }
} 
        
display_children(1,0);

?>

Can anyone tell me why the code is not working?

Please help.

I believe you code should be

function display_children($parent=0, $level=0) {
   // retrieve all children of $parent
   $result = mysql_query('SELECT categoryid,catname as title FROM category '.
                          'WHERE parentcatid="'.$parent.'";');

   // display each child
   while ($row = mysql_fetch_array($result)) {
       // indent and display the title of this child
       echo str_repeat('  ',$level).$row['title']."\n";

       // call this function again to display this
       // child's children
       display_children($row['categoryid'], $level+1);
   }
} 
        
display_children(); 

Also:

I dont know if you realize this but on a HTML page your continous spaces will only show as one space. Try using &nbsp; or put the items in a list <ul>

[Angry Coder]

Thank you so much, taliesinnz. It worked like a charm.

[taliesinnz]

No Problem

[taliesinnz]

May I suggest that the way you are doing is not really efficent at all.

Using the data you have you are quering the Database 5 times for 4 records.

It is usually more efficent to do just one query and sort the data using php, then multi-queries. Or use a different format for storing the data.

It is just something to keep in mind when dealing with datasets

[Angry Coder]

I don't think the difference is noticeable when having 50 rows maximum. What do you think?

[taliesinnz]

It will depend on your web server, and visitor count etc.

But for 50 rows you will get 51 queries per request, and 1 doing it the other way. If you have 100 visitors at a time, thats 5100queries vs 100

just something to think about..

You could do a few benchmarks, take the average time of the queries over a loop of 500 and see the time diference, then write batch script to do multipul request to log, if you are that interested in results

[stereofrog]

I don't think the difference is noticeable when having 50 rows maximum. What do you think?

This article should help you further

http://www.sitepoint.com/article/hie...-data-database