List:General Discussion« Previous MessageNext Message »
From:hsv Date:May 28 2012 9:09pm
Subject:Re: category with parentid
View as plain text  
>>>> 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.
**************************************************************


Thread
category with parentidHaidarPesebe25 May
  • Re: category with parentidhsv25 May
    • Re: category with parentidAndrĂ©s Tello25 May
      • Re: category with parentidhsv26 May
    • Re: category with parentidPeter Brawley25 May
  • Re: category with parentidHaidarPesebe28 May
    • Re: category with parentidTsubasa Tanaka28 May
    • Re: category with parentidhsv28 May
    • Re: category with parentidhsv28 May
Re: category with parentidHaidarPesebe30 May
Re: category with parentidHaidarPesebe30 May
  • Re: category with parentidTsubasa Tanaka30 May