Re: Recursive select to produce hierarchical listing
Hi. Maybe I am missing something, but won't SELECT M1.*,M2.* FROM Menu AS M1 JOIN Menu AS M2 ON M1.ID = M2.ParentID WHEREID=0 ORDER BY M1.Index, M2.Index work? Bye, Benjamin. On Tue, Nov 20, 2001 at 06:04:11PM -0800, [EMAIL PROTECTED] wrote: I am trying to display a hierarchically related list of folders via a recursive SQL statement (each row in a table represents a folder). What is needed is a select that will sort the folders and associate the parent folder with their child, sub-folders. The kicker is that the items in the folder need to be maintained in a specific order. An exanple: A] Mailbox 1) Folder A a - A sent b - A store c - A hold [...] Anyone have insight on how this may be accomplished? Here is one attempt at it but no luck so far: SELECT M1.*,M2.* FROM Menu AS M1 JOIN Menu AS M2 ON M1.ID = M2.ParentID WHERE ID=0; Full table definition dump below: CREATE TABLE Menu ( ID smallint(6) NOT NULL auto_increment, Index tinyint(4) NOT NULL default '0', ParentID smallint(6) NOT NULL default '0', FolderName varchar(32) default NULL, Level smallint(6) NOT NULL default '1', PRIMARY KEY (ID) ); Index is the order in which an item appears within a folder. Level is the level at which the folder is located, this makes it easier to indent the items in a folder. [...] -- [EMAIL PROTECTED] - 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: Recursive select to produce hierarchical listing
Not sure if you're using PHP to manipulate MySQL or not, but if so, I had the same problem and found the answer from someone on PHPBuilder, and then I mirrored the answer on my site here: http://php.sitecreative.com/faq.php#3 Hope it helps. - Jonathan -Original Message- From: Benjamin Pflugmann [mailto:[EMAIL PROTECTED]] Sent: Tuesday, November 27, 2001 2:43 PM To: Michael Collins Cc: [EMAIL PROTECTED] Subject: Re: Recursive select to produce hierarchical listing Hi. Maybe I am missing something, but won't SELECT M1.*,M2.* FROM Menu AS M1 JOIN Menu AS M2 ON M1.ID = M2.ParentID WHEREID=0 ORDER BY M1.Index, M2.Index work? Bye, Benjamin. On Tue, Nov 20, 2001 at 06:04:11PM -0800, [EMAIL PROTECTED] wrote: I am trying to display a hierarchically related list of folders via a recursive SQL statement (each row in a table represents a folder). What is needed is a select that will sort the folders and associate the parent folder with their child, sub-folders. The kicker is that the items in the folder need to be maintained in a specific order. An exanple: A] Mailbox 1) Folder A a - A sent b - A store c - A hold [...] Anyone have insight on how this may be accomplished? Here is one attempt at it but no luck so far: SELECT M1.*,M2.* FROM Menu AS M1 JOIN Menu AS M2 ON M1.ID = M2.ParentID WHERE ID=0; Full table definition dump below: CREATE TABLE Menu ( ID smallint(6) NOT NULL auto_increment, Index tinyint(4) NOT NULL default '0', ParentID smallint(6) NOT NULL default '0', FolderName varchar(32) default NULL, Level smallint(6) NOT NULL default '1', PRIMARY KEY (ID) ); Index is the order in which an item appears within a folder. Level is the level at which the folder is located, this makes it easier to indent the items in a folder. [...] -- [EMAIL PROTECTED] - 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: recursive select
Ah recursion. Nothing like it to get your mind tied up in knots... :)
Try this - untested, but this is the basic idea, and it or something
like it should work. Call the function with the node you want to start
from and the array you want the results to land in... Like so:
?
getCategories($startingNodeID,$targetArray);
function getCategories($node,$array) {
global $dbh;
$sql=select child,category from table where parent=$node;
$sth=mysql_query($sql,$dbh);
while ($res=mysql_fetch_assoc($sth)) {
$array[]=$res[category];
getCategories($res[child],$array);
}
return;
}
?
-Original Message-
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED]
m] On Behalf Of Chih-Lii Chen/Trans-EZ/TW
Sent: Tuesday, June 12, 2001 9:24 PM
To: [EMAIL PROTECTED]
Subject: recursive select
Hi,
I'm having a slight problem trying to figure out some logic. I have a
mySQL table that contains 3 columns (child, category, parent).
What I like to do is retrieve all the childrens of the parent
and store
them in an php array.
Any advies on this issue will be very helpful.
Thanks in advance for the help.
Chih-Lii
-
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: recursive select
On 13-Jun-01 Alok K. Dhir wrote:
Ah recursion. Nothing like it to get your mind tied up in knots... :)
Try this - untested, but this is the basic idea, and it or something
like it should work. Call the function with the node you want to start
from and the array you want the results to land in... Like so:
?
getCategories($startingNodeID,$targetArray);
function getCategories($node,$array) {
global $dbh;
$sql=select child,category from table where parent=$node;
$sth=mysql_query($sql,$dbh);
while ($res=mysql_fetch_assoc($sth)) {
$array[]=$res[category];
getCategories($res[child],$array);
}
return;
}
?
... and stomp all over the result-set; getCategories() should open/close
a local connection to the db server.
Regards,
--
Don Read [EMAIL PROTECTED]
-- It's always darkest before the dawn. So if you are going to
steal the neighbor's newspaper, that's the time to do it.
-
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: recursive select
Good catch :)
-Original Message-
From: Don Read [mailto:[EMAIL PROTECTED]]
Sent: Wednesday, June 13, 2001 6:44 AM
To: Alok K. Dhir
Cc: [EMAIL PROTECTED]; Chih-Lii Chen/Trans-EZ/TW
Subject: RE: recursive select
On 13-Jun-01 Alok K. Dhir wrote:
Ah recursion. Nothing like it to get your mind tied up in
knots... :)
Try this - untested, but this is the basic idea, and it or
something
like it should work. Call the function with the node you want to
start from and the array you want the results to land in...
Like so:
?
getCategories($startingNodeID,$targetArray);
function getCategories($node,$array) {
global $dbh;
$sql=select child,category from table where parent=$node;
$sth=mysql_query($sql,$dbh);
while ($res=mysql_fetch_assoc($sth)) {
$array[]=$res[category];
getCategories($res[child],$array);
}
return;
}
?
... and stomp all over the result-set; getCategories()
should open/close a local connection to the db server.
Regards,
--
Don Read [EMAIL PROTECTED]
-- It's always darkest before the dawn. So if you are going to
steal the neighbor's newspaper, that's the time to do it.
-
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: recursive select
Hi, I'm having a slight problem trying to figure out some logic. I have a mySQL table that contains 3 columns (child, category, parent). What I like to do is retrieve all the childrens of the parent and store them in an php array. I assume you want to get all children for all parents (somehow buched together) and not only to get the children for a single (known) parent. I have a very vague idea of how php arrays really work but this might get you started: (I'm assuming there is a fourth id column here. What do you use for references otherwise? Btw. what does the child column contain?) SELECT * FROM the_table a LEFT JOIN the_table b ON a.id = b.parent This will effectively get you the children for each parent and the parent itself (the parent parts will be repetead for each child). You'll have to do some parsing when outputting/putting into the php array but that should be of little problem. The left-join 'technique' can be added on and on and will work as long as you know the maximum depth before the select is done. Eg. SELECT * FROM the_table a LEFT JOIN the_table b ON a.id = b.parent LEFT JOIN the_table c ON b.id = c.parent If the depth is fixed at design time you could add another column which you have to keep close track of. That column would be one which if used to ORDER BY would make certain each child comes after each parent. If the depth is small you could easely use a float to make that happen. -- Aigars - 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: recursive select
Dear, I have an table which like to use recursive join but I wonder how to use it. Please see the table struc below. emp.id,emp name, emp salary, emp head-id 001,mr. a,1,001 002,mr. b,5000,001 003,mr. c,5000,003 004,mr. d,5000,003 Where emp. head-id is id from emp.id. If I'd like to know which head count name of id 002 (it must shown name from id 001). How could I do with mysql? Regards, Sommai Fongnamthip Sir, you are trying to deal with a tree structure. I'm going to do something I've never done before and recommend that you read Joe Celko's book 'SQL For Smarties', which contains two chapters on dealing with tree structures. The table above uses the adjacency list model. The following is modified from Celko's book and returns all boss/subordinate pairs: SELECT e1.name, 'is the boss of', e2.name FROM emp e1, emp e2 WHERE e1.id = e2.head_id; To return the boss 2 levels up, use SELECT e1.name, 'is the boss of the boss of', e3.name FROM emp e1, emp e2, emp e3 WHERE e1.id = e2.head_id AND e2.id = e3.head_id; Good luck Bob Hall Know thyself? Absurd direction! Bubbles bear no introspection. -Khushhal Khan Khatak MySQL list magic words: sql query database - 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
