Also take a look at Joe Celko's 'SQL for Smarties'. There's a whole
chapter on this. It gets kind if burly, but it's pretty cool. It's all
done by converting your schema below into a tree structure...
(no, i don't have any code since I've just read about it never done it
:)
-philip
In article <[EMAIL PROTECTED]>,
Krznaric Michael <[EMAIL PROTECTED]> wrote:
>James,
>
> Take a look at hierarchical queries in MS SQl. If I'm not mistaken,
>this is part of the SQL 99 standard and is implemented by many SQL 92
>compliant database vendors. I know ORACLE and DB2 support it. I'm not sure
>if you already have but I would investigate this topic with MS SQL.
>
>Mike
>
>
>-----Original Message-----
>From: James Crowley [mailto:[EMAIL PROTECTED]]
>Sent: Monday, May 07, 2001 1:45 PM
>To: [EMAIL PROTECTED]
>Subject: [PHP] Recursive SQL Queries: Web Directory Categories
>
>
>Hi,
> I have a web directory which allows sub-categories to an unlimited
>level.
>This is done with a categories table, and assigning a parentid to each...
>For example
>
>ID ParentID Name
>1 0 Products
>2 1 Sub-Category of Products (Level 1)
>3 1 Another Sub-Category (Level 1)
>4 2 A sub-sub-category (Level 2)
>
>What I need to do is return a list of an items parent categories. For
>example, if we are in category 4, I want text outputted saying
>
>Products \ Sub-Category\sub-sub Category
>
>However, it does not seem possible to do this with a single SQL query. I
>have succeeded in creating a recursive one below (in MS SQL):
>
>CREATE PROCEDURE dbo.dir_recurse(@parentid INT) AS
> --// List its parent categories
> DECLARE @childid INT
> DECLARE hC CURSOR LOCAL FOR SELECT parent FROM categories c WHERE id =
>@parentid ORDER BY c.name FOR READ ONLY
> OPEN hC
> FETCH NEXT FROM hC INTO @childid
> WHILE @@FETCH_STATUS = 0 AND @childid != 0
> BEGIN
> EXECUTE dir_recurse @childid
> FETCH NEXT FROM hC INTO @childid
> END
> CLOSE hC
> DEALLOCATE hC
> RETURN 0
>GO
>
>However, this returns more than one result set, which neither ASP or PHP (so
>therefore it's probably the ODBC driver...) seem to support. Help!
>
>Regards,
>
>- James
>
>Editor, VB Web
>==================
>Web - http://www.vbweb.co.uk
>Email - [EMAIL PROTECTED]
>ICQ# - 60612011
>==================
>
>> -----Original Message-----
>> From: [EMAIL PROTECTED]
>> [mailto:[EMAIL PROTECTED]]
>> Sent: 06 May 2001 20:11
>> To: [EMAIL PROTECTED]
>> Subject: php-general Digest 6 May 2001 19:11:22 -0000 Issue 670
>>
>>
>>
>> php-general Digest 6 May 2001 19:11:22 -0000 Issue 670
>>
>> Topics (messages 51587 through 51649):
>>
>> IIS instead of PWS!!! what else can i do..
>> 51587 by: Thomas Edison Jr.
>> 51588 by: Zak Greant
>> 51591 by: Thomas Edison Jr.
>> 51592 by: Thomas Edison Jr.
>> 51593 by: Zak Greant
>> 51594 by: Alexander Skwar
>>
>> Re: PHP on PWS not working!
>> 51589 by: Thomas Edison Jr.
>> 51590 by: Thomas Edison Jr.
>> 51600 by: Phil Driscoll
>> 51640 by: Thomas Edison Jr.
>> 51644 by: Phil Driscoll
>>
>> Re: Sorry, what is "PWS"
>> 51595 by: Alexander Skwar
>>
>> Re: "Free" Database Design Program
>> 51596 by: Andrzej Swedrzynski
>>
>> Novedades en Gordos.com
>> 51597 by: Gordos.com
>>
>> PHP as apache module and security.
>> 51598 by: Andrzej Swedrzynski
>> 51607 by: Estelle Martin
>> 51609 by: Andrzej Swedrzynski
>>
>> Re: nslookup function ?
>> 51599 by: Tom Carter
>>
>> Complete Newbie
>> 51601 by: biscut
>>
>> What is msvcr70.dll??
>> 51602 by: Ermanno Iannacci
>>
>> Re: PEAR where should I start?
>> 51603 by: Gyozo Papp
>>
>> Image displaying problem
>> 51604 by: Estelle Martin
>>
>> Re: class (not class instance) variables
>> 51605 by: Gyozo Papp
>>
>> Re: quicker query?
>> 51606 by: Gyozo Papp
>>
>> searching a MySQL database
>> 51608 by: Jamie Saunders
>> 51620 by: bill
>> 51623 by: John Vanderbeck
>>
>> Insert data into mysql table
>> 51610 by: Mohamedou
>>
>> Re: wordwrap
>> 51611 by: Yasuo Ohgaki
>>
>> php4apachi.dll
>> 51612 by: Darren
>>
>> best ide for windows
>> 51613 by: Alexander Bierbrauer
>> 51615 by: Gyozo Papp
>> 51617 by: Geir Eivind Mork
>> 51618 by: John Vanderbeck
>> 51626 by: Henrik Hansen
>> 51628 by: Andrzej Swedrzynski
>> 51629 by: Gyozo Papp
>> 51630 by: Alex Piaz
>> 51633 by: Alexander Skwar
>> 51634 by: Bjorn Sodergren
>> 51635 by: Andrzej Swedrzynski
>> 51637 by: John Vanderbeck
>> 51639 by: Andrzej Swedrzynski
>>
>> php.exe [filename .php] vs. run [filename.php] via webserver
>> 51614 by: Art
>>
>> Install problems with PHP and GD
>> 51616 by: John Vanderbeck
>>
>> if string contains...
>> 51619 by: Jamie Saunders
>> 51621 by: Alvin Tan
>> 51622 by: Gyozo Papp
>>
>> Slightly OT - Backing up mySQL...
>> 51624 by: Nick Terzich
>> 51625 by: Felix Kronlage
>> 51627 by: Henrik Hansen
>>
>> Re: simple database extraction problem :(
>> 51631 by: Manuel Lemos
>>
>> Can php give me row number?
>> 51632 by: Jan Grafstrvm
>> 51636 by: Gyozo Papp
>> 51642 by: Stephan Ahonen
>>
>> Flash/PHP/MySQL
>> 51638 by: FredrikAT
>> 51641 by: Eduardo Pirez Jover
>>
>> Passing variables to another page - newbie
>> 51643 by: Dean Martin
>> 51645 by: Jack Dempsey
>> 51646 by: John Vanderbeck
>> 51647 by: tcuhost.hotmail.com
>>
>> [OT?] permission denied[OT?]
>> 51648 by: Alexander Bierbrauer
>>
>> file() shooting blanks
>> 51649 by: Jay Lepore
>>
>> Administrivia:
>>
>> To subscribe to the digest, e-mail:
>> [EMAIL PROTECTED]
>>
>> To unsubscribe from the digest, e-mail:
>> [EMAIL PROTECTED]
>>
>> To post to the list, e-mail:
>> [EMAIL PROTECTED]
>>
>>
>> ----------------------------------------------------------------------
>>
>
>
>--
>PHP General Mailing List (http://www.php.net/)
>To unsubscribe, e-mail: [EMAIL PROTECTED]
>For additional commands, e-mail: [EMAIL PROTECTED]
>To contact the list administrators, e-mail: [EMAIL PROTECTED]
>
>--
>PHP General Mailing List (http://www.php.net/)
>To unsubscribe, e-mail: [EMAIL PROTECTED]
>For additional commands, e-mail: [EMAIL PROTECTED]
>To contact the list administrators, e-mail: [EMAIL PROTECTED]
>
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
To contact the list administrators, e-mail: [EMAIL PROTECTED]
