[plug] PHP / mySQL - recursive query problem

[Shayne O'Neill]

Problem is , the problems not really a nice one to solve in any discrete
number of dimensions, since we dont know how deep it goes, thus any
functional sql query is likely to be positively satanic. And dreamweaver
mx *aint* gunna do it (sorry. I really love dreamweaver, but its out of
its league in this one).

Generally this sort of thing is best represented as an n-tree ( a tree
with n number of branches ).

If you will;-
You'd define your tuples as follows;-
{idfield | name  | parentfield}

(I'd honestly recomend hanging extra details of another table with a 1:1
relationship to it.. Just to keep stuff tight. Even the name should
probably go there, but practically its handy in there for easyness sake)

So in the case of your top one you'd have

{1 | australia | null }

Then for WA and a bunch of states youd would maybe have

{2 | Wa | 1 } {3 | SA | 1} {4 | NT 1} {5 | NSW | 1} etc

You might even have some WA cities

{6 | Perth | 2} {7 | Melville | 2} {8 | Fremantle | 2} etc

And so forth. You can see the third term points to the id of the wider
area.

Now We can see that if we know that we want to find out all cities in Wa,
we'd (1st) grab the tuple for WA and then get its ID. (2)

Then we'd go

SELELECT * FROM LOCATION_TREE WHERE (PARENTID = 2);

and here are our cities.

Going backwards is easier. Just look at field 3.

The difficulty arises where we have to go from back of tree up....

Theres two ways. Recursion, or flipping backwards thru a loop.

Recursion;- (Pseudo code, cos I'm too tired to think php)

DEF GET_PARENT ($CURRENTID):
	NEWTUPLE =QUERY ( SELECT * FROM LOCATION_TREE WHERE (PARENTID = $CURRENTID)
	PROCESS NEWTUPLE.DATA //Or whatever you want to do with it.
	IF NEWTUPLE.PARENTID NOT NULL:
		GET_PARENT (NEWTUPLE.PARENTID)
	ELSE:
		FINISH

In a kinda scrambled way you can see whats happening.

Just roll that into a loop (it doesnt branch for a more linear way of
solving this The parent - > id relationship is many -> 1 , so you wont
fail just loopin thru instead of recursing thru.

But either way, a simple sql query wont cut it. It needs back up with real
code.

Cheers,
Shayne.


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

On Mon, 29 Mar 2004, Ady Gould wrote:

> Anyone got good PHP / mySQL skills?
>
> I've got a problem with a recursive table definition that I am having
> hassles with and cannot get to comply (it seems it has anti-Assimilation
> technology).
>
> The basis is storage of locations around the globe, with the ability to get
> more and more specific.
>
> AU -> WA -> Perth -> Bayswater -> XX Street
> AU -> WA -> Kalgoorlie -> YY Street
> AU -> WA -> Kalgoorlie -> YZ Street
> AU -> WA -> Kalgoorlie -> YX Street
> AU -> NSW -> Sydney -> Surry Hills -> ZZ Street
> AU -> NSW -> Sydney -> Surry Hills -> ZY Street
> AU -> WA -> Perth -> Como -> XY Street
> AU -> WA -> Perth -> XZ Street
> AU -> WA -> Perth -> XA Street
>
> as you can see the depth varies, and there will be specific detail at each
> depth.
>
> I have been battling using ADOdb / PHAkt (for Dreamweaver MX/MX 2004) with
> infinite loops happening :(
>
> Anyone give me a solution that shows the above list, preferably something
> like:
>
> AU
>   -> NSW
>     -> Sydney
>       -> Surry Hills
>         -> ZZ Street
>           -> ZY Street
>  -> WA
>    -> Kalgoorlie
>      -> YY Street
>        -> YZ Street
>          -> YX Street
>    -> Perth
>      -> Bayswater
>        -> XX Street
>      -> Como
>        -> XY Street
>      -> XZ Street
>      -> XA Street
>
> The solution will have lots of possible uses including navigation,
> breadcrumbs and more, so it could be useful for many people.
>
> Thanks for the help
>
>
> Ady
>
>
>
> Ady "KiltOtter" Gould
> Web Designer & Administrator, Educator, Otter and Kilt Wearer
> Blogged @: Kilted  <http://www.livejournal.com/users/kiltotter> Kunundrums &
> The  <http://www.bdu.org/%7Eotter/> Otter's Pool
> Useful sites:[Bears Down Under <http://bdu.org/> ] [TechniX.org
> <http://technix.org/> ] [West Oz Leather <http://westozleather.com/> ]
> [BDU.org <http://bdu.org/> ]
>
> Cookie Monster: A lie is a terminological inexactitude. - Winston Churchill
> Cookies are generated automatically and do not necessarily reflect the
> opinion of the sender.
>
>
>
>