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
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
