[plug] PHP / mySQL - recursive query problem

[Ady Gould]

Ok everyone, you will be glad to know that I basically solved the problem
when at work today.

So you all have the code, I will post it below...

Now I have a few nice little problems to make the code more easily reused -
like how to fill a certain control, or display the list in the manner I have
for the solution, and so on... Oh the fun!

Here is the code... Commented as much as I have for my TAFE students.

Ady


--------------------- SAMPLE CALLS --------------------------
List all the locations, and their 'sub-locations' [location ID = null,
maxDepth = 2]
	echo displayLocations( "" , 2 );

List all the locations, and their 'sub-locations' [location ID = null, no
max depth]
	echo displayLocations( "" );

List all the locations within Western Australia [location ID = 2, max depth
1]
	echo displayLocations( 1, 1 );

--------------------- END SAMPLE CALLS --------------------------


------------------ CODE BELOW --------------------
// // // // // // // // // // // // // // // // // // // // // // // // //
// // // // // //
// Function:   displayLocations
//
// Arguments:  $fromWhere (optional) is the locationID that you want to
start the search from
//             $maxDepth (optional) is the maximum number of levels that
will be traversed
//
// Usage:      In the current form there are two options:
//                  echo displayLocations( );
//             which displays the whole tree of locations.
//                  echo displayLocations( 3 );
//             which displays the tree of locations that are under the
locationID 3.
//                  echo displayLocations( "", 2 );
//             which displays the tree of locations that are from the 1st
level 
//             and for a total of 2 levels (eg Country -> state only)
//
// Author:     Adrian Gould 
//
// Date:       20040330
//
// Notes:      I have to express my thanks to Perth Linux User's Group
members,
//             LiveJournal's PHP and mySQL communities and other people for
their
//             assistance whilst I was bashing my head against a brick wall.
//
// Purpose:    Takes a location and returns all the sub-locations found in
that location,
//             recursively exploring for further sub-locations until the end
of the trail 
//             is reached.
//
// Copyright:  C Adrian Gould - released under GPL - please keep this
copyright notice in place
//             and add your comments below, including modifications and
updates.
//
// Change Log: 
//             20040330 - Added limiting argument to signify how many levels
of the tree
//                        are to be displayed when the function is called.
//
//             20040330 - after a number of hours bashing head against desk
at home, the 
//                        solution was probably something like a bad upload
of some scripts.
//
//
// Define function, setting a default for $fromWhere to be a null string.
//
// // // // // // // // // // // // // // // // // // // // // // // // //
// // // // // // // // //
function displayLocations ( $fromWhere = "" , $maxDepth = -1 )
{

    // make sure that the connection string is globally available, so we 
	// can use it here in this function.
	// also maintains a single database connection and reduces
overheads.
	global $connStationeryQuery;
	
	// set $resultSet to <ul> if it is not set already.
	if (!isset($result)) 
	{ 
		$result="<ul>";
	}
	
	// if $fromWhere is null, 
	//    then find all top level locations
	// else
	//    find all sub-locations that are listed for the 'fromWhere'
location
	if ( $fromWhere =="" )
	{
		$query_rsLocations = "SELECT * FROM tblLocations WHERE
foundWhere IS NULL ORDER BY locationID ASC";
	}
	else
	{
		$query_rsLocations = "SELECT * FROM tblLocations WHERE
foundWhere = '$fromWhere' ORDER BY locationID ASC";
	} // end if $fromWhere...
	
	// retrieve the recordset, or die!
	$rsLocations = $connStationeryQuery -> Execute ( $query_rsLocations
) or die ( $connStationeryQuery -> ErrorMsg() );
	
	// set a 'foundLocation' flag to false (not found)
	$foundLocation = false;
	
	// Check to see if any records exist, 
	// and if maxDepth is greater than zero (for limited levels)(
	//     or if less than zero (for all levels)
	// (note: to get the code to work as if it was called using depth
limited and depth unlimited
	//        recursion, we need to use a flag that says if this is the
case then we can keep traversing
	//        until we have exhausted all the possible entries on the
table. In this case it is -1 for
	//        this to happen. Any positive value will give that number
of levels. Zero gives no results)
	// if any records are found...
	if ( ( $rsLocations-> RecordCount() > 0 ) and ( ( $maxDepth > 0 ) or
( $maxDepth <= -1 ) ) )
	{
		// while not the end of the record set
		while ( ! $rsLocations -> EOF )
		{
			// Set the $foundLocation flag true, so that we know
we have to check for
			// sub-locations of the locations just found
			$foundLocation = true;
			
			// set a temporary variable to the locationID field
			$locID = $rsLocations -> Fields ('locationID');
			
			// Create a list item with the location text, and
the location ID
			// the location ID is formatted so that thousands
have "," separating
			// each group of three digits
			$result .= "<li>[" . number_format($locID) . "] ".
$rsLocations -> Fields ( 'location' ) ;
			
			// Now add to the end of the result any
sub-locations
			// that are found using the locID as the "parent"
and call it with the $maxDepth
			// being reduced by one.
			$result .= displayLocations ( $locID , ( $maxDepth -
1 ) )."</li>";
			
			// retrieve the next record from the result set
			$rsLocations -> MoveNext ();
			
		} // end while $rsLocations...
	}
	else
	{
		// end the current unordered list
		$result .='</ul>';
		
	} //end if Recordcount > 0
	
	// end the unordered list
	$result .= "</ul>";
	
	// close the record set
	$rsLocations -> Close();
	
	// if a location was found then
	if ( $foundLocation ) 
	{
		// return the $result string back to the previous
displayLocation call
		return $result;
	}
	else
	{
		// otherwise return a blank - signifying that there are no
further sub-locations
		// in the tree.
		// note that this is a good spot to put a HREF link to the
details about the
		// location that you hare listing
		return "";
	} // if $foundLocation...		
	
} // end function displayLocations
------------------ END CODE ----------------------