>>>> 2012/05/28 12:54 +0700, HaidarPesebe >>>>
select id,name from TABLE WHERE parentid='0'
and a second call to the same table as this;
select id,name from TABLE WHERE parentid='$id' (this $id is the result of
calling the first call TABLE)
<<<<<<<<
Others have said, and I agree, you have a graph, and some graph algorithm is
needed for the indenting that you seek.
You use some other programming language than MySQL for making up the queries.
In that language keep an array of arrays; your first query,
select id,name from TABLE WHERE parentid='0'
generates more id's. Make an array of them, and that array is the other array s
first element. Make a new query,
select id,name from TABLE WHERE parentid in ($formerIDs),
and so on, each $formerID all the id's in the earlier query returned, until no
ID is returned.
Then use the array-array, and hope that each id only once appears in it:
if parentid=0, indent 0;
if parentid found in first element, indent 1;
if parentid found in second element, indent 2;
...
You can do this with a generated CASE:
CASE
WHEN parentid = 0 THEN 0
WHEN parentid IN (first element) THEN 1
WHEN parentid IN (second element) THEN 2
..
END
. This is in a REPEAT concatenated to your name, or LPAD with your name one of
the arguments, say
SELECT id,
REPEAT(' ', CASE
WHEN parentid = 0 THEN 0
WHEN parentid IN (first element) THEN 1
WHEN parentid IN (second element) THEN 2
..
END) || name, parentid FROM TABLE
.
If it is guaranteed that for each parentid there is at most one id, then all
this becomes much simpler: it is an array of id's; the variable-comparison CASE
is used.
**************************************************************
Scanned by MailScan AntiVirus and Content Security Software.
Visit http://www.escanav.com for more info on eScan and X-Spam.
**************************************************************
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql
