List:General Discussion« Previous MessageNext Message »
From:Bart Goormans Date:February 7 2003 1:15am
Subject:kind of recursive SQLstr
View as plain text  
Dear listers,

can anyone shine a light on this one...

I have some HTML page-titles which i'd like to put in a dropdown menu. Least
load for server is when just using a server-cursor. And going trough the
results just once.

So best thing is to order the titles in the right way when recieving them
from
the database:
-------------
pagesetup
-------------
pgID
pgMemberOf
pgName
hasMembers
-------------

like this:
------------------------------------------------
1.HomePage (hasMembers > 0, pgMemberOf = 0)
  >> 1.1. Products
       >> 1.1.1 Toys & Tools (hasMembers = 0)
       >> 1.1.2 Shoestrings
  >> 1.2. Services
       >> 1.2.1 Pinball repair
            >> 1.2.1.1 New Balls
            >> 1.2.1.2 Empty your machine
       >> 1.2.2 We tie your shoes
            >> 1.2.2.2 Left Feet Ties
                 >> 1.2.2.2.1 Untying A Knot
            >> 1.2.2.2 Right Feet Ties
  >> 1.3. Links
  >> 1.4.. you get the picture ..
-------------------------------------------------


So far, I've made a recursive function which returns
all data as a string HTML:

-pseudo-CODE-----------------------------

buildTree(HomepageID)

function buildTree(myPgID)

  mySQL = getSQL(myPgID)
  myObjRS = getRecordset(mySQL)

  Loop Trough records (myObjRS)

    If this page(pgID) has Members

      strReturn = ...

    *!Recursive!-----------------!*
      strReturn += buildTree(pgID)

    else

      strReturn = ...

    end if

  Loop

  close recordset
  return strReturn

end function

-END-pseudo-CODE---------------------------


Bad thing about this function is that it opens
up lots of recordsets (for each sub of a sub,...)
Positive: it works !!

Now, I was wondering if it couldn't be done in the
mySQL-database itself. ???
( MyISAM tables / MySQL 3.23.54 )


I tried out this one ...
-SQLstring----------------------------------

SELECT
	ps2.pgID As parentID,
	ps2.pgName AS parentPage,
	ps1.pgID,
	ps1.pgName,
	CONCAT_WS(  '.' ,
		LPAD(  ps5.pgNr ,2,  '0'   ),
		LPAD(  ps4.pgNr ,2 , '0'   ),
		LPAD(  ps3.pgNr ,2 , '0'   ),
		LPAD(  ps2.pgNr ,2 , '0'   ),
		LPAD(  ps1.pgNr ,2 , '0'   )
	)AS myOrder
FROM
pagesetup AS ps1
LEFT OUTER JOIN pagesetup AS ps2
  ON ps1.pgMemberOf = ps2.pgID
LEFT OUTER JOIN pagesetup AS ps3
  ON ps2.pgMemberOf = ps3.pgID
LEFT OUTER JOIN pagesetup AS ps4
  ON ps3.pgMemberOf = ps4.pgID
LEFT OUTER JOIN pagesetup AS ps5
  ON ps4.pgMemberOf = ps5.pgID
ORDER BY myOrder;

>>like in:
http://lists.hampshire.edu/pipermail/computerscience/2001-September/003304.h
tml
--------------------------------------------

As you can see, this is limited to just 5 sub-levels (ps5)
and 99 pages on each level(LPAD-2). Again, it works ;) but
couldn't there perhaps be a more elegant and 'open' method
to use? What way would you guys tackle this recursiveness
in MySQL?


cheers,
and Tx for staying this far down  ;)


Bart



Thread
kind of recursive SQLstrBart Goormans7 Feb