RE: [PHP] Recursive SQL Queries: Web Directory Categories

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:james@vbweb.co.uk] Sent: Monday, May 07, 2001 1:45 PM To: php-general@lists.php.net 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 - james@vbweb.co.uk ICQ# - 60612011 ================== > -----Original Message----- > From: php-general-digest-help@lists.php.net > [mailto:php-general-digest-help@lists.php.net] > Sent: 06 May 2001 20:11 > To: php-general@lists.php.net > 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: > php-general-digest-subscribe@lists.php.net > > To unsubscribe from the digest, e-mail: > php-general-digest-unsubscribe@lists.php.net > > To post to the list, e-mail: > php-general@lists.php.net > > > ---------------------------------------------------------------------- > -- PHP General Mailing List (http://www.php.net/) To unsubscribe, e-mail: php-general-unsubscribe@lists.php.net For additional commands, e-mail: php-general-help@lists.php.net To contact the list administrators, e-mail: php-list-admin@lists.php.net