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
RE: [PHP-DB] arranging folders hiearchy
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
[PHP-DB] 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
