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