Hi Frank thanks for the reply.  I'm a newbie so I'm not exactly
understanding your solution so I'm trying it out.  I got up to the UPDATE
SORTORDER FROM DIR_TABLE ... but I got an error on the FROM.  I am using
mysql and looked up the UPDATE syntax and it didn't show the FROM syntax
being used.





-----Original Message-----
From: Frank Flynn [mailto:[EMAIL PROTECTED]]
Sent: June 9, 2002 6:42 PM
To: [EMAIL PROTECTED]
Cc: [EMAIL PROTECTED]
Subject: RE: [PHP-DB] arranging folders hiearchy


The problem is you're overloading your "PATH" field.  An SQL engine cannot
tell the different meanings between (other than they are different):
   '       1       3       7' and
   '       1       2       6'

You can do this 'live' with only the ID, PID and the name SECTION and then a
temporary table to do the sort.

Try this:

CREATE TABLE SORTORDER
ID int
TOPOFPATH int
PATH varchar(512) /*must be big enough for longest path*/
LEVELFROMTOP int

Now run:

INSERT INTO SORTORDER (ID, TOPOFPATH, PATH, LEVELFROMTOP )
  SELECT ID, PID,  SECTION, 1
  FROM DIR_TABLE  /* this is your original table whose name I don't know */

OK now run this over and over again until no rows are being affected.

UPDATE SORTORDER
 FROM DIR_TABLE
  SET TOPOFPATH = DIR_TABLE.PID,
      PATH =  DIR_TABLE.SECTION + PATH
      LEVELFROMTOP = LEVELFROMTOP + 1
  WHERE TOPOFPATH = DIR_TABLE.ID
  AND DIR_TABLE.ID != DIR_TABLE.PID
  /* you could have some sanity end condition like AND LEVELFROMTOP < 50 */

Now to get your data in the order you desire:

SELECT DIR_TABLE.SECTION, SORTORDER.LEVELFROMTOP
  FROM DIR_TABLE, SORTORDER
  WHERE DIR_TABLE.ID = SORTORDER.ID
  ORDER BY SORTORDER.PATH

The SORTORDER.LEVELFROMTOP is the number of spaces to put in front of the
SECTION to get that nice nested look

Remember:
-As you go up the the ID -> PID ladder ALL paths must end at a record ID
where ID = PID (or no end condition -- see sanity end condition above)
-SORTORDER.PATH must be big enough to hold the longest path.  This can be a
weakness.
-you may want to make DIR_TABLE.SECTION lower case before you insert it into
SORTORDER.PATH
-You can't do this PATH field in the original table because if I change a
directory name it's hard to rebuild the PATH field in all of the rows.  But
you can leave the SORTORDER table in tact for performance and only rebuild
it after you modify any of the values in the original table.

Feel free to write back if you have more questions.
Good luck,
Frank

On 6/9/02 6:18 AM, "[EMAIL PROTECTED]"
<[EMAIL PROTECTED]> wrote:

> From: "SP" <[EMAIL PROTECTED]>
> Date: Sat, 8 Jun 2002 20:09:22 -0400
> To: <[EMAIL PROTECTED]>
> Subject: arranging folders hiearchy
>
> I am trying to arrange the results by PATH but within each of the same
PATH
> section I want it to be arranged by NAME alphabetically so it looks like
the
> following below.  I've tried a bunch of order by's but not getting it to
work.
> Is this possible to do?  Or do I have to change my PATH data some how.
>
> Home
> About Us
> Contact Us
> Products
>   Cabinets
>   Furnitures
> Services
>   Repairs
>   Waxing
>
> ID PID PATH                       SECTION
> == === ========================== =================
> 1  1   '       1'                 'Home'
> 2  1   '       1       2'         'Products'
> 3  1   '       1       3'         'Services'
> 5  2   '       1       2       5' 'Furniture'
> 6  2   '       1       2       6' 'Cabinets'
> 7  3   '       1       3       7' 'Waxing'
> 10 7   '       1       3      10' 'Repairs'
> 11 1   '       1       b'         'Contact Us'
> 12 1   '       1       c'         'About Us'
>



-- 
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Reply via email to