[plug] PHP / mySQL - recursive query problem

[Ady Gould]

Ok my fubar for not giving all the details...  

> > I have been battling using ADOdb / PHAkt (for Dreamweaver 
> MX/MX 2004) 
> > with infinite loops happening :(
> 
> Can you work around it using a fixed, fairly large limit on 
> the depth and structuring your data in a way that doesn't 
> require recursion?
> That'd be my first response if I thought it was possible.

I have the following structure, and the following starter code... Not that
it is working :( 

The data structure is simply:

Table definition:


+------------+---------------------------+------+-----+---------+-----------
-----+
| Field      | Type                      | Null | Key | Default | Extra
|
+------------+---------------------------+------+-----+---------+-----------
-----+
| locationID | int(10) unsigned zerofill |      | PRI | NULL    |
auto_increment |
| Location   | varchar(128)              |      | MUL |         |
|
| foundWhere | int(10) unsigned zerofill | YES  |     | NULL    |
|
+------------+---------------------------+------+-----+---------+-----------
-----+
[note the zerofill was just me having a look at the possible things that
mySQL was able to do on the fly, and entirely optional]

Test Data:

+------------+------------------------------+------------+
| locationID | Location                     | foundWhere |
+------------+------------------------------+------------+
| 0000000001 | Australia                    |       NULL |
| 0000000002 | Western Australia            | 0000000001 |
| 0000000003 | New South Wales              | 0000000001 |
| 0000000004 | Australian Capital Territory | 0000000001 |
| 0000000005 | Perth                        | 0000000002 |
| 0000000006 | Canberra                     | 0000000004 |
| 0000000007 | Sydney                       | 0000000003 |
| 0000000008 | Kalgoorlie-Boulder           | 0000000002 |
| 0000000009 | Bayswater                    | 0000000002 |
| 0000000010 | Bassendean                   | 0000000002 |
| 0000000011 | South Australia              | 0000000001 |
| 0000000012 | Britain                      |       NULL |
| 0000000013 | London                       | 0000000012 |
| 0000000014 | Queensland                   | 0000000001 |
| 0000000015 | United States of America     |       NULL |
| 0000000016 | San Francisco                | 0000000015 |
| 0000000017 | Victoria                     | 0000000001 |
| 0000000018 | Melbourne                    | 0000000017 |
+------------+------------------------------+------------+

I found a hint at http;//www.easysoft.com/tech/php/tut_002/main/phtml and
based this on that.

<?php

//Begin Displaythread( ParentMessage )
function displayLocations ( $fromWhere = "")
{
	global $connStationeryQuery;

	// begin Recordset
	if ( $fromWhere == "" )
	{
		$query_rsLocations = "SELECT * FROM tblLocations WHERE
foundWhere = NULL ORDER BY location ASC" ;	
	}
	else
	{
		$query_rsLocations = "SELECT * FROM tblLocations WHERE
foundWhere = '$fromWhere' ORDER BY location ASC" ;	
	}

	echo "<br /> $query_rsLocations  - - ";

	$rsLocations = $connStationeryQuery->Execute($query_rsLocations) or
die($connStationeryQuery->ErrorMsg());

	// end Recordset
	$FoundLocation = false;
	while ( !$rsLocations->EOF )
	{
  		$FoundLocation = true;
		$locID = $rsLocations->Fields['locationID'];
		echo "[$locID]";
		$result = " -> " . $rsLocations->Fields['location'] .
displayLocations ( $locID );
    	$rsLocations->MoveNext();
	} // end while

	$result .= "<br />";
	$rsLocations->Close();	
	
	if ($FoundLocation)
	{
		echo " [$fromWhere]<br />";
		return $result;
	}
	else
	{
		echo " [$fromWhere]<br />";
		return "";
	}
}

?>

<?php
echo displayLocations();

?>