Re: Recursive SQL Queries: Web Directory Categories

Hello, Joe Celko's 'SQL for Smarties'. Is it a book ( to buy :( ) or is it available on the web (download :) )? If the latter stands please can you give me a link to it? ----- Original Message ----- From: "Philip Hallstrom" <philip@adhesivemedia.com> To: <php-general@lists.php.net> Sent: 2001. május 8. 21:33 Subject: Re: [PHP] 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 > > > > > > -- > 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 >