Re: Recursive SQL Queries: Web Directory Categories

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 <81A3043681E6824EBE672F7421C30E7E2A1A86@SRVTORONTO.RAND.COM>, Krznaric Michael <mkrznaric@rand.com> 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: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 > >-- >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 >