Help with recursive function

Dev Shed Forums Sponsor:

September 26th, 2005, 08:54 PM

wjones8050

Contributing User

Join Date: Jul 2001

Location: USA

Posts: 73

Time spent in forums: 1 Day 40 m 29 sec
Reputation Power: 9

Help with recursive function

Hi,

I have categorys and subcategories in one table with a parent/child relationship.

I want to be able to determine the path UP to the top level category from any sublevel category. And develop an array of subcategories along the way up to top level

I have the code below *sort of* working but not completely, and I wondered if anyone might have a moment to point me in the right direction

thank you

my code:

PHP Code:


		
			
//main recursive function

function getRootCat($subcatID) {

    

$sql = "SELECT subcat.name as subcatName, cat.ID as catID, cat.name as catName

    FROM t_category cat

    INNER JOIN t_category subcat ON subcat.parent_ID = cat.ID

    WHERE subcat.ID = $subcatID

    ";

    

    $result = mysql_query($sql) or die("error getting root category" . mysql_error() );

    

    $data = mysql_fetch_array($result);

    $catID         = $data['catID'];

    $catName     = $data['catName'];

    $subcatName    = $data['subcatName'];



    $rootCheck = checkRootCat($catID);

    

    if($rootCheck) {



        $catArray['ID'][] = $catID;

        $catArray['Name'][] = $catName;

        

    } else {



        $catArray = getRootCat($catID);

        

    }

    

    return $catArray;

}





//checkRootCat queries the db to see if

//the current category is the main category

//it checks by seeing if the ID is equal to the parentID

function checkRootCat($catID) {

            

$sql = "SELECT cat.ID

    FROM t_category cat

    WHERE cat.ID = $catID

    AND cat.parent_ID = $catID

    ";

        

$result = mysql_query($sql) or die("problem checking root cat " . mysql_error() );



$numRows = mysql_num_rows($result);



if($numRows) {

    return true;

} else {

    return false;

}



}





$testArray = getRootCat($catID);

            

echo "<pre>";

print_r($test);

echo "</pre>";

the output from above will display this:

Array
(
[ID] => Array
(
[0] => 1
)

[Name] => Array
(
[0] => Aquatics
)

)

what I would like is to do this for each of the categories as I go up the ladder

September 26th, 2005, 10:02 PM

Skudd

Calvin's Mother.

Join Date: Mar 2004

Location: Houston, TX

Posts: 425

Time spent in forums: 2 Days 12 h 51 m 56 sec
Reputation Power: 30

I could be of more use if I could see the result of the following SQL query.

Code:

SQL

DESCRIBE t_category_cat;

I'm assuming that's the table name. In the code above, you're missing the last underscore.

__________________
PHP Guru -- jQuery Novice -- SQL Master -- RHCE
Skudd.com Blogs -- More fun than a barrel full of monkeys!

September 27th, 2005, 07:52 AM

wjones8050

Contributing User

Join Date: Jul 2001

Location: USA

Posts: 73

Time spent in forums: 1 Day 40 m 29 sec
Reputation Power: 9

Quote:

Originally Posted by Skudd

I could be of more use if I could see the result of the following SQL query. DESCRIBE t_category_cat;

Hi thanks for helping out, here's the way that table is setup:

PHP Code:


		
			
 ID      int(11)   PRI      NULL      auto_increment

parent_ID     int(11)                 0      

name     varchar(75)                        

desc     text                        

display     int(1)                 0      

status     int(1)                 0

basically the structure would look like

Aquatics
....preschool
........instruction
....youth
........instruction
........training
....adult
........advanced
........etc...

September 27th, 2005, 09:47 AM

Bobby_Easland

Contributing User

Join Date: Sep 2005

Location: /usa/kentucky/richmond/

Posts: 66

Time spent in forums: 20 h 15 m 58 sec
Reputation Power: 5

PHP Code:


		
			
/**

 * Example recursion function for cPath

 * 

 * This short example was created for wjones8050 from the DevShed community.

 *

 * @license http://opensource.org/licenses/gpl-license.php GNU Public License

 * @version 1.0

 * @link http://www.oscommerce-freelancers.com/ osCommerce-Freelancers

 * @copyright Copyright 2005, Bobby Easland 

 * @author Bobby Easland 

 */



function categoryPathRecursive($catID, &$cPath){    

    // Sanity check to make sure we don't run the function

    // on the top level

    if ($catID == '0') return; 

    

    // Initialize the SQL

    $sql = "SELECT ID, parent_ID, name 

            FROM t_category 

            WHERE ID = '" . (int)$catID . "' 

            LIMIT 1";

    

    // Execute the query, catch any errors

    $query = mysql_query($sql) or trigger_error(mysql_errno() . ': ' . mysql_error());

    

    // Sanity check to make sure there were rows returned

    if ( mysql_num_rows($query) > 0 ){

        // Fetch the array associatively

        $result = mysql_fetch_array($query, MYSQL_ASSOC);

        // Store the result in the $cPath referenced array

        $cPath[ $result['ID'] ] = $result['name'];

        // If the parent_ID is greater than zero (not top level)

        // send it through the recursion function

        if ( $result['parent_ID'] > 0 ){

            categoryPathRecursive($result['parent_ID'], $cPath);  

        else {

            // The parent_ID === 0 so we should just break the recursion

            return;

        }

    } else {

        // There were no rows returned so break the recursion

        return;

    }    

} # end function

Notice that the cPath parameter is passed by reference!

Usage:

PHP Code:


		
			
 $catID = (from script);

categoryPathRecursive($catID, $container);



print_r($container); // has array with catID's as index and catName as value

Bobby

September 27th, 2005, 05:17 PM

wjones8050

Contributing User

Join Date: Jul 2001

Location: USA

Posts: 73

Time spent in forums: 1 Day 40 m 29 sec
Reputation Power: 9

Wow! that is awesome! thanks so much for the help, really nice piece of scripting! I'm not advanced enough to know about passing by reference, but I need to check that out a bit to understand how you got that to work so well.

thanks again

September 28th, 2005, 09:37 AM

Bobby_Easland

Contributing User

Join Date: Sep 2005

Location: /usa/kentucky/richmond/

Posts: 66

Time spent in forums: 20 h 15 m 58 sec
Reputation Power: 5

It's not advanced nor is it my best work

However, it demonstrates 1 method of implementing a recursion function. Another method would be to return the data instead of using referenced variables. I find that the referenced parameter recursion functions are easier to use but your mileage may vary.

Another very important aspect of creating recursion functions is to make damn sure you don't get into an infinite loop. Use recursion break constructs (return, break, continue, etc) as liberally as possible!

Enjoy!

Bobby

Last edited by Bobby_Easland : September 28th, 2005 at 09:38 AM. Reason: Spelling correction

Viewing: Dev Shed Forums > Programming Languages > PHP Development > Help with recursive function

« Previous Thread | Next Thread »

Thread Tools	Search this Thread
Email this Page	Search this Thread: Advanced Search
Display Modes	Rate This Thread
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode	Rate This Thread:

Posting Rules
You may not post new threads You may not post replies You may not post attachments You may not edit your posts vB code is On Smilies are On [IMG] code is On HTML code is Off

View Your Warnings | New Posts | Latest News | Latest Threads | Shoutbox

Forum Jump

Free IT White Papers!

How to Present Effectively Online
This white paper offers practical and actionable advice on the key steps that any presenter should consider as they plan and execute a Webinar or online meeting.

Open Source Security Myths
Open Source Software (OSS) is computer software whose source code is available to the general public with relaxed or non-existent intellectual property restrictions (or arrangement such as the public domain), and is usually developed with the input of many contributors.

Power and Cooling Capacity Management for Data Centers
This paper describes the principles for achieving power and cooling capacity management.

Scalable, Fault-Tolerant NAS for Oracle - The Next Generation
For several years NAS has been evolving as a storage alternative for Oracle databases, and for good reason: NAS is quite often the simplest, most cost-effective storage approach for Oracle. Learn about the benefits that HP's approach to scalable NAS brings to Oracle environments in this comprehensive white paper.

Understanding Web Application Security Challenges
This white paper discusses many common threats and preventive measures for Web application security, and explains what you can do to help protect your organization.

More Free IT White Papers!