[plug] PHP / mySQL - recursive query problem

[Shayne O'Neill]

Your table structure is precisely right :)

Ady: What precisely is the nature of this project if its not a
rude or business secret trashing sorta question?

------------------------------------
"Must not Sleep! Must warn others!"
-Aesop.
Shayne O'Neill. Indymedia. Fun.
http://www.perthimc.asn.au

On Tue, 30 Mar 2004, Ady Gould wrote:

> 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();
>
> ?>
>
>
> _______________________________________________
> PLUG discussion list: plug at plug.linux.org.au
> http://mail.plug.linux.org.au/cgi-bin/mailman/listinfo/plug
> Committee e-mail: committee at plug.linux.org.au
>