Re: recursion
At 7:27 AM + 2/27/08, Thufir wrote: I'm reading "SQL for dummies" and one of the more interesting sections was on recursion. The example query was something like: WITH RECURSIVE ReachableFrom (Source, Destination) AS (SELECT Source, Destination FROM FLIGHT UNION SELECT in.Source, out.Destination FROM ReachableFrom in, FLIGHT out WHERE in.Destination = out.Source ) SELECT * FROM ReachableFrom WHERE Source = "Portland"; I'm a bit thrown by the union. Can this be simplified to: WITH RECURSIVE ReachableFrom (Source, Destination) AS (SELECT Source, Destination FROM FLIGHT ) SELECT * FROM ReachableFrom WHERE Source = "Portland"; MySQL does not have "WITH RECURSIVE". -- Paul DuBois, MySQL Documentation Team Madison, Wisconsin, USA MySQL AB, www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: recursion
I'm not aware of MySQL supporting this feature. Microsoft does and calls it "common table expression" (CTE). The UNION is necessary as this the part that links the anchor query, Part1 of the UNION to the recursive query, Part2. Part2 of the UNION must reference the produced temporary table called "ReachableFrom" in your example. The Part2 query is put in an internal loop constantly inserting new rows into the CTE and then referencing them in the next loop. Once the loop no longer generates new rows the CTE stops and the final query is executed. Ed -Original Message- From: news [mailto:[EMAIL PROTECTED] On Behalf Of Thufir Sent: Wednesday, February 27, 2008 12:28 AM To: mysql@lists.mysql.com Subject: recursion I'm reading "SQL for dummies" and one of the more interesting sections was on recursion. The example query was something like: WITH RECURSIVE ReachableFrom (Source, Destination) AS (SELECT Source, Destination FROM FLIGHT UNION SELECT in.Source, out.Destination FROM ReachableFrom in, FLIGHT out WHERE in.Destination = out.Source ) SELECT * FROM ReachableFrom WHERE Source = "Portland"; I'm a bit thrown by the union. Can this be simplified to: WITH RECURSIVE ReachableFrom (Source, Destination) AS (SELECT Source, Destination FROM FLIGHT ) SELECT * FROM ReachableFrom WHERE Source = "Portland"; thanks, Thufir -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
recursion
I'm reading "SQL for dummies" and one of the more interesting sections was on recursion. The example query was something like: WITH RECURSIVE ReachableFrom (Source, Destination) AS (SELECT Source, Destination FROM FLIGHT UNION SELECT in.Source, out.Destination FROM ReachableFrom in, FLIGHT out WHERE in.Destination = out.Source ) SELECT * FROM ReachableFrom WHERE Source = "Portland"; I'm a bit thrown by the union. Can this be simplified to: WITH RECURSIVE ReachableFrom (Source, Destination) AS (SELECT Source, Destination FROM FLIGHT ) SELECT * FROM ReachableFrom WHERE Source = "Portland"; thanks, Thufir -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: recursion or something recursion-esque
Mike, >What I'd love to do is pull all children (and grandchildren, etc) per >each, such that I'd end up with the following result set or something See http://www.artfulsoftware.com/mysqlbook/sampler/mysqled1ch20.html for theory & examples. PB Mike Johnson wrote: This one may end up dead in the water, but I figured I'd run it past the group as I've seen some pretty creative solutions in my time here. Let's say I have a table like this: ++---+ | id | parent_id | ++---+ | 1 | 0 | | 2 | 0 | | 3 | 2 | | 4 | 0 | | 5 | 1 | | 6 | 2 | | 7 | 1 | | 8 | 3 | | 9 | 8 | | 10 | 5 | ++---+ id is the primary key and parent_id refers to this table's id. That is, 3 is a child of 2 and 8 is a child of 3. What I'd love to do is pull all children (and grandchildren, etc) per each, such that I'd end up with the following result set or something like it: +++ | id | children | +++ | 1 | 5, 7 | | 2 | 3, 6, 8, 9 | | 3 | 8, 9 | | 4 || | 5 | 10 | | 6 || | 7 || | 8 | 9 | | 9 || | 10 || +++ Say there's more to this table than what you see, and say it's a lookup table to a larger table. If I'm querying on everything in that larger table that's 2 here, I'd like it to be able to actually pull anything that's 2, 3, 6, 8, or 9. The obvious solution is to parse out an array of that ahead of time and use it (1 => (5, 7), 2 => (3, 6, 8, 9), etc), but let's pretend this is an annoyingly complex Perl suite and if I can just manipulate queries I'll be a whole lot happier. Any thoughts? I feel like the solution is either remarkably simple or frustratingly difficult. Thanks in advance if you can help! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
recursion or something recursion-esque
This one may end up dead in the water, but I figured I'd run it past the group as I've seen some pretty creative solutions in my time here. Let's say I have a table like this: ++---+ | id | parent_id | ++---+ | 1 | 0 | | 2 | 0 | | 3 | 2 | | 4 | 0 | | 5 | 1 | | 6 | 2 | | 7 | 1 | | 8 | 3 | | 9 | 8 | | 10 | 5 | ++---+ id is the primary key and parent_id refers to this table's id. That is, 3 is a child of 2 and 8 is a child of 3. What I'd love to do is pull all children (and grandchildren, etc) per each, such that I'd end up with the following result set or something like it: +++ | id | children | +++ | 1 | 5, 7 | | 2 | 3, 6, 8, 9 | | 3 | 8, 9 | | 4 || | 5 | 10 | | 6 || | 7 || | 8 | 9 | | 9 || | 10 || +++ Say there's more to this table than what you see, and say it's a lookup table to a larger table. If I'm querying on everything in that larger table that's 2 here, I'd like it to be able to actually pull anything that's 2, 3, 6, 8, or 9. The obvious solution is to parse out an array of that ahead of time and use it (1 => (5, 7), 2 => (3, 6, 8, 9), etc), but let's pretend this is an annoyingly complex Perl suite and if I can just manipulate queries I'll be a whole lot happier. Any thoughts? I feel like the solution is either remarkably simple or frustratingly difficult. Thanks in advance if you can help! -- Mike Johnson Smarter Travel Media LLC Applications Developer http://www.smartertravel.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Folder recursion explained
This is an excellent example of WHY you need to always CC: the list on all
responses. I cannot specifically answer your response because I do not
use enough PHP to help. In extremely generic terms, what you need to do
is:
a) connect to the database server
b) send to the database server an INSERT statement or sequence of INSERT
statements based on the results of your folder recursions.
c) disconnect from the server.
If you need to do something else with the data within the same script, you
probably want to delay disconnecting from the server. Have you tried
looking at the PHP examples for working with a MySQL database for
guidance?
Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine
"Brian e Boothe" <[EMAIL PROTECTED]> wrote on 11/01/2005 12:20:07 AM:
> I know that…. I know what i want to do and yes ive wrote the PHP
> backend, I just need to get it into mysql tables for later lookup
>
><<< * CODE-
> StartCODE *>>
>
>
> echo '';
> print_r(recrusive_dirlist('/inetpub/wwwroot/Products/'));
> echo '';
> ?>
>
>
> /*
> This function retuns all directory and file names from the given
directory.
> Works recrusive.
> */
> function recrusive_dirlist($base_dir)
> {
> global $getDirList_alldirs,$getDirList_allfiles;
>function getDirList($base)
>{
>global $getDirList_alldirs,$getDirList_allfiles;
>if(is_dir($base))
>{
>$dh = opendir($base);
>while (false !== ($dir = readdir($dh)))
>{
>if (is_dir($base . $dir) && $dir !== '.' && $dir !== '..')
>{
>$subs = $dir;
>$subbase = $base . $dir . '/';
>$getDirList_alldirs[]=$subbase;
>getDirList($subbase);
>}
>elseif(is_file($base . $dir) && $dir !== '.' && $dir !==
'..')
>{
>$getDirList_allfiles[]=$base . $dir;
>}
>}
>closedir($dh);
>}
>}
>
> getDirList($base_dir);
> $retval['dirs']=$getDirList_alldirs;
> $retval['files']=$getDirList_allfiles;
> return $retval;
> }
> ?>
>
>
>
> From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]
> Sent: Tuesday, November 01, 2005 11:09 AM
> To: Brian e Boothe
> Cc: mysql@lists.mysql.com
> Subject: Re: Folder recursion
>
>
>
> "Brian e Boothe" <[EMAIL PROTECTED]> wrote on 10/31/2005 10:21:35 PM:
>
> > I.m needing assistance in get a problem Solved ,
> >
> > What im attempting to do is have MySQL thru asp or PHP look thru
> > Folders and add to a Mysql database the path and folder name and file
> > located within that directory into the database, can anyone help me ??
or
> > give me hints
> >
> >
> > --
> > No virus found in this outgoing message.
> > Checked by AVG Free Edition.
> > Version: 7.1.362 / Virus Database: 267.12.6/152 - Release Date:
10/31/2005
> >
>
> I am mostly confused about you are actually trying to do. I think
> you are trying to crawl a directory tree with MySQL by calling an
> ASP or PHP script.
>
> I think you have chosen the wrong tool to automate a directory scan.
> Either PHP or ASP can do the directory navigation work that you
> describe but MySQL is a database server. Its role in this situation
> would be to store and retrieve results not to control the process.
>
> In ASP, you would instantiate a "Scripting.FileSystemObject" object
> and use its properties and methods to navigate your directory
> structures. You would use the ADODB.xxx objects to interact with a
> MySQL database through a MyODBC driver.
>
> I don't know what you would use in PHP to crawl file system folders
> but many PHP installations have the mysql_xxx functions activated by
> default. You would not need to use any other
> objects/libraries/drivers to interact with the MySQL database if you
> used the native mysql_xxx functions.
>
> Shawn Green
> Database Administrator
> Unimin Corporation - Spruce Pine
> --
> No virus found in this incoming message.
> Checked by AVG Free Edition.
> Version: 7.1.362 / Virus Database: 267.12.6/152 - Release Date:
10/31/2005
>
> --
> No virus found in this outgoing message.
> Checked by AVG Free Edition.
> Version: 7.1.362 / Virus Database: 267.12.6/152 - Release Date:
10/31/2005
Re: Folder recursion
"Brian e Boothe" <[EMAIL PROTECTED]> wrote on 10/31/2005 10:21:35 PM: > I.m needing assistance in get a problem Solved , > > What im attempting to do is have MySQL thru asp or PHP look thru > Folders and add to a Mysql database the path and folder name and file > located within that directory into the database, can anyone help me ?? or > give me hints > > > -- > No virus found in this outgoing message. > Checked by AVG Free Edition. > Version: 7.1.362 / Virus Database: 267.12.6/152 - Release Date: 10/31/2005 > I am mostly confused about you are actually trying to do. I think you are trying to crawl a directory tree with MySQL by calling an ASP or PHP script. I think you have chosen the wrong tool to automate a directory scan. Either PHP or ASP can do the directory navigation work that you describe but MySQL is a database server. Its role in this situation would be to store and retrieve results not to control the process. In ASP, you would instantiate a "Scripting.FileSystemObject" object and use its properties and methods to navigate your directory structures. You would use the ADODB.xxx objects to interact with a MySQL database through a MyODBC driver. I don't know what you would use in PHP to crawl file system folders but many PHP installations have the mysql_xxx functions activated by default. You would not need to use any other objects/libraries/drivers to interact with the MySQL database if you used the native mysql_xxx functions. Shawn Green Database Administrator Unimin Corporation - Spruce Pine
Folder recursion
I.m needing assistance in get a problem Solved , What im attempting to do is have MySQL thru asp or PHP look thru Folders and add to a Mysql database the path and folder name and file located within that directory into the database, can anyone help me ?? or give me hints -- No virus found in this outgoing message. Checked by AVG Free Edition. Version: 7.1.362 / Virus Database: 267.12.6/152 - Release Date: 10/31/2005
RE: Recursion
Amer, It's still worth storing the parentId, because you can easily recreate the fullpath if (when!) your code screws up a set of full paths. You can also write a reliable sanity checker that checks the full path of all the nodes in the table based on the parentids. Also, to locate multiple children of a node, it's a more efficient query to use "where parentid = x", rather than "where fullpath like '1/2/3/4/%'" All the best, Andy > Yes, excellent idea. It's the classic 'linked list' from my old Pascal > days. While playing with it I realized that you only have to save the > ID, Name and the FullPath (parents) data. For example, using Andy's > data: > > ID | Name | FullPath > > 1 | Bob | 0 > 2 | John | 0/1 > 3 | Elm | 0/1 > 4 | Sue | 0/1/2 > 5 | Dave | 0/1/2/4 > 6 | Fred | 0/1/2/4/5 > > The FullPath doesn't need the the 'leaf' or bottom node - it can be > derived (it's the ID). Using a 'split' function on the FullPath data you > can pull out the individual parents. > -- > /* All outgoing email scanned by Norton Antivirus 2002 */ > Amer Neely, Softouch Information Services > W: www.softouch.on.ca > E: [EMAIL PROTECTED] > V: 519.438.5887 > Perl | PHP | MySQL | CGI programming for all data entry forms. > "We make web sites work!" > > > - > Before posting, please check: >http://www.mysql.com/manual.php (the manual) >http://lists.mysql.com/ (the list archive) > > To request this thread, e-mail <[EMAIL PROTECTED]> > To unsubscribe, e-mail > <[EMAIL PROTECTED]> > Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php > > - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: Recursion
> Subject: RE: Recursion >Date: Wed, 12 Feb 2003 09:36:11 - >From: "Andy Eastham" <[EMAIL PROTECTED]> > To: "Mysql@Lists. Mysql. Com" <[EMAIL PROTECTED]> > > Rob, > > This is a common problem in document management, where I have a reasonable > amount of experience. > > Unfortunately, the short answer is, that to be completely generic, efficient > and elegant, it's a bit of an impossible problem. > > What we have always done in this situation is to maintain an additional > denormalised column called "FullPath" so, expanding your sample data a bit: > > ID | Name | ParentID | FullPath > > 1 | Bob | 0| 1 > 2 | John | 1| 1/2 > 3 | Elm | 1| 3/1 > 4 | Sue | 2| 1/2/4 > 5 | Dave | 4| 1/2/4/5 > 6 | Fred | 5| 1/2/4/5/6 > etc. > > This initially seems like a horrible solution, raddled with problems. > However it's actually quite efficient. > > The application has to manage the Full Path on updates (although it's easy > to rebuild it and check integrity if you screw it up). > > It's also easy to find anything at any level under an object using string > comparisons. > > If you move a folder (parent) to [new path], you have to do an update such > as > > UPDATE table set FullPath = [new path] + substring(oldpath, [new Path > Length]) > WHERE fullpath like '[old path]%' > > Again this is indexed and pretty efficient. > > If you like, you can remove the objects own id from the fullpath and make it > effectively "parent path" > > Hope this helps. > > All the best, > > Andy > > > > -Original Message- > > From: Rob [mailto:[EMAIL PROTECTED]] > > Sent: 12 February 2003 07:18 > > To: [EMAIL PROTECTED] > > Subject: Recursion > > > > > > > > Hi all, > > > > I need some help with recursion in mySql. I have the following table: > > > > ID | Name | ParentID > > > > 1 | Bob | 0 > > 2 | John | 1 > > 3 | Elm | 1 > > > > etc. > > > > For a given ID, I need to recurse up the tree and get all the > > parents. I've > > already read > > about Joe Celko's nested set approach, but it's not a good solution as > > apparently updates are > > a real pain and this table will be modified heavily. Does anyone have any > > good suggestions?? > > Maybe store procs (although, by all accounts store proc functionality > > doesn't come standard with > > mySql)?? > > > > Thanks > > > > > > --- > > Rob > > > > ** > > Rob Cherry > > mailto:[EMAIL PROTECTED] > > +27 21 447 7440 > > Jam Warehouse RSA > > Smart Business Innovation > > http://www.jamwarehouse.com > > ** Yes, excellent idea. It's the classic 'linked list' from my old Pascal days. While playing with it I realized that you only have to save the ID, Name and the FullPath (parents) data. For example, using Andy's data: ID | Name | FullPath 1 | Bob | 0 2 | John | 0/1 3 | Elm | 0/1 4 | Sue | 0/1/2 5 | Dave | 0/1/2/4 6 | Fred | 0/1/2/4/5 The FullPath doesn't need the the 'leaf' or bottom node - it can be derived (it's the ID). Using a 'split' function on the FullPath data you can pull out the individual parents. -- /* All outgoing email scanned by Norton Antivirus 2002 */ Amer Neely, Softouch Information Services W: www.softouch.on.ca E: [EMAIL PROTECTED] V: 519.438.5887 Perl | PHP | MySQL | CGI programming for all data entry forms. "We make web sites work!" - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: Recursion
Thanks. Awesome idea. We'll probably use that :-) --- Rob ** Rob Cherry mailto:[EMAIL PROTECTED] +27 21 447 7440 Jam Warehouse RSA Smart Business Innovation http://www.jamwarehouse.com ** -Original Message- From: Andy Eastham [mailto:[EMAIL PROTECTED]] Sent: Wednesday, February 12, 2003 11:36 AM To: Mysql@Lists. Mysql. Com Subject: RE: Recursion Rob, This is a common problem in document management, where I have a reasonable amount of experience. Unfortunately, the short answer is, that to be completely generic, efficient and elegant, it's a bit of an impossible problem. What we have always done in this situation is to maintain an additional denormalised column called "FullPath" so, expanding your sample data a bit: ID | Name | ParentID | FullPath 1 | Bob | 0| 1 2 | John | 1| 1/2 3 | Elm | 1| 3/1 4 | Sue | 2| 1/2/4 5 | Dave | 4| 1/2/4/5 6 | Fred | 5| 1/2/4/5/6 etc. This initially seems like a horrible solution, raddled with problems. However it's actually quite efficient. The application has to manage the Full Path on updates (although it's easy to rebuild it and check integrity if you screw it up). It's also easy to find anything at any level under an object using string comparisons. If you move a folder (parent) to [new path], you have to do an update such as UPDATE table set FullPath = [new path] + substring(oldpath, [new Path Length]) WHERE fullpath like '[old path]%' Again this is indexed and pretty efficient. If you like, you can remove the objects own id from the fullpath and make it effectively "parent path" Hope this helps. All the best, Andy > -Original Message- > From: Rob [mailto:[EMAIL PROTECTED]] > Sent: 12 February 2003 07:18 > To: [EMAIL PROTECTED] > Subject: Recursion > > > > Hi all, > > I need some help with recursion in mySql. I have the following table: > > ID | Name | ParentID > > 1 | Bob | 0 > 2 | John | 1 > 3 | Elm | 1 > > etc. > > For a given ID, I need to recurse up the tree and get all the > parents. I've > already read > about Joe Celko's nested set approach, but it's not a good solution as > apparently updates are > a real pain and this table will be modified heavily. Does anyone have any > good suggestions?? > Maybe store procs (although, by all accounts store proc functionality > doesn't come standard with > mySql)?? > > Thanks > > > --- > Rob > > ** > Rob Cherry > mailto:[EMAIL PROTECTED] > +27 21 447 7440 > Jam Warehouse RSA > Smart Business Innovation > http://www.jamwarehouse.com > ** > > > > - > Before posting, please check: >http://www.mysql.com/manual.php (the manual) >http://lists.mysql.com/ (the list archive) > > To request this thread, e-mail <[EMAIL PROTECTED]> > To unsubscribe, e-mail > <[EMAIL PROTECTED]> > Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php > > - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: Recursion
Rob, This is a common problem in document management, where I have a reasonable amount of experience. Unfortunately, the short answer is, that to be completely generic, efficient and elegant, it's a bit of an impossible problem. What we have always done in this situation is to maintain an additional denormalised column called "FullPath" so, expanding your sample data a bit: ID | Name | ParentID | FullPath 1 | Bob | 0| 1 2 | John | 1| 1/2 3 | Elm | 1| 3/1 4 | Sue | 2| 1/2/4 5 | Dave | 4| 1/2/4/5 6 | Fred | 5| 1/2/4/5/6 etc. This initially seems like a horrible solution, raddled with problems. However it's actually quite efficient. The application has to manage the Full Path on updates (although it's easy to rebuild it and check integrity if you screw it up). It's also easy to find anything at any level under an object using string comparisons. If you move a folder (parent) to [new path], you have to do an update such as UPDATE table set FullPath = [new path] + substring(oldpath, [new Path Length]) WHERE fullpath like '[old path]%' Again this is indexed and pretty efficient. If you like, you can remove the objects own id from the fullpath and make it effectively "parent path" Hope this helps. All the best, Andy > -Original Message- > From: Rob [mailto:[EMAIL PROTECTED]] > Sent: 12 February 2003 07:18 > To: [EMAIL PROTECTED] > Subject: Recursion > > > > Hi all, > > I need some help with recursion in mySql. I have the following table: > > ID | Name | ParentID > > 1 | Bob | 0 > 2 | John | 1 > 3 | Elm | 1 > > etc. > > For a given ID, I need to recurse up the tree and get all the > parents. I've > already read > about Joe Celko's nested set approach, but it's not a good solution as > apparently updates are > a real pain and this table will be modified heavily. Does anyone have any > good suggestions?? > Maybe store procs (although, by all accounts store proc functionality > doesn't come standard with > mySql)?? > > Thanks > > > --- > Rob > > ** > Rob Cherry > mailto:[EMAIL PROTECTED] > +27 21 447 7440 > Jam Warehouse RSA > Smart Business Innovation > http://www.jamwarehouse.com > ** > > > > - > Before posting, please check: >http://www.mysql.com/manual.php (the manual) >http://lists.mysql.com/ (the list archive) > > To request this thread, e-mail <[EMAIL PROTECTED]> > To unsubscribe, e-mail > <[EMAIL PROTECTED]> > Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php > > - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Recursion
Hi all, I need some help with recursion in mySql. I have the following table: ID | Name | ParentID 1 | Bob | 0 2 | John | 1 3 | Elm | 1 etc. For a given ID, I need to recurse up the tree and get all the parents. I've already read about Joe Celko's nested set approach, but it's not a good solution as apparently updates are a real pain and this table will be modified heavily. Does anyone have any good suggestions?? Maybe store procs (although, by all accounts store proc functionality doesn't come standard with mySql)?? Thanks --- Rob ** Rob Cherry mailto:[EMAIL PROTECTED] +27 21 447 7440 Jam Warehouse RSA Smart Business Innovation http://www.jamwarehouse.com ** - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Recursion
Hi all, I need some help with recursion in mySql. I have the following table: ID | Name | ParentID 1 | Bob | 0 2 | John | 1 3 | Elm | 1 etc. For a given ID, I need to recurse up the tree and get all the parents. I've already read about Joe Celko's nested set approach, but it's not a good solution as apparently updates are a real pain and this table will be modified heavily. Does anyone have any good suggestions?? Maybe store procs (although, by all accounts store proc functionality doesn't come standard with mySql)?? Thanks --- Rob ** Rob Cherry mailto:[EMAIL PROTECTED] +27 21 447 7440 Jam Warehouse RSA Smart Business Innovation http://www.jamwarehouse.com ** - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Database (Recursion and Factorials)
On Mon, May 14, 2001 at 09:32:05AM -0400, lkeeton wrote: [snip] > how does it take place. For instance in this example I pass 5 it then gets to > the line below and evaluates value = 5 * factorial(5-1)-> right there that > tells me to call the function again and pass it 4 then when I get to the line > right below me $value = 4 * factorial(4-1). I don't see where the actual > calculation takes place 5 * 4 * 3 * 2 = 120. Everytime a number gets passed I > feel that somehow what I have done before is gone. Can somebody walk me > through this very carefully so I can see what is really going on. This is far from a MySQL question. Try a perl forum, or any algorithm's manual. You need to understand the basics of recursion before you can understand that bit of code. Greetz, Peter. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Database (Recursion and Factorials)
I am currently working on a factorial procedure that figures out a factorial
for a number and eventually I will store it in the mysql database.
I am kind of getting caught up with this program below. I am confused how the
program is executed with all the recursive direct calls to itself. Can
somebody step me through this one slowly so I can see the flow of where a
number gets passed all the way to the end where it returns the final number.
I am getting hung up here the most where the actual recursion takes place
I guess where I am getting lossed is where does the calculation take place and
how does it take place. For instance in this example I pass 5 it then gets to
the line below and evaluates value = 5 * factorial(5-1)-> right there that
tells me to call the function again and pass it 4 then when I get to the line
right below me $value = 4 * factorial(4-1). I don't see where the actual
calculation takes place 5 * 4 * 3 * 2 = 120. Everytime a number gets passed I
feel that somehow what I have done before is gone. Can somebody walk me
through this very carefully so I can see what is really going on.
$value *= factorial($value - 1);
Thanks
#!/usr/bin/perl
sub factorial
{
my $value = shift (@_);
if ($value == 1) {
return $value;
}
else{
$value *= factorial($value - 1);
return $value;
}
}
$result = factorial(5);
print $result;
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
