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]
