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.
CREATE TABLE SORTORDER
PATH varchar(512) /*must be big enough for longest path*/
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.
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
-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
-you may want to make DIR_TABLE.SECTION lower case before you insert it into
-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.
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.
> About Us
> Contact Us
> 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