(for filter: query,sql,mysql,table)
I've done this a few times, I mostly do category systems and there
is no reason to list all the categories at one time, so I usually
have a "parent id" to work with in order to get all the sub
categoreis from the DB. However, I have done one where I did want
something like a "Windows Explorer" view.
The following is the table structure and the PHP function I use
to display the information.
The output is something like this:
Folder One
-Sub Folder One
--Sub Folder One-One
Folder Two
-Sub Folder Two
My mail client will wrap the php function, so it may be malformed by
the time you get it.
CREATE TABLE `folders` (
`id` int(10) unsigned NOT NULL auto_increment,
`owner_id` int(10) unsigned NOT NULL default '0',
`parent_id` int(10) unsigned NOT NULL default '0',
`root_id` int(10) unsigned NOT NULL default '0',
`name` varchar(254) default NULL,
PRIMARY KEY (`id`),
KEY `owner_key`(`owner_id`),
KEY `parent_key`(`parent_id`),
KEY `root_key`(`root_id`)
) TYPE=MyISAM
function listFolders($aslink=0,$userid) {
$query="SELECT DISTINCTROW t.id,t.name "
."FROM folders AS t, folders AS r "
."WHERE ((t.id=r.root_id) AND (t.owner_id=".$userid."))
"
."ORDER BY t.name ASC;"
;
if($result=mysql_query($query)) {
if(mysql_num_rows($result)) {
while($row=mysql_fetch_row($result)) {
list($id,$name)=$row;
if($aslink) {
printf("<a class=\"folder\"
href=\"inbox.php?fid=%s\">%s</a><br>\n",$id,$name);
} else {
printf("<option
value=\"%s\">%s</option>\n",$id,$name);
}
$query="SELECT t.id,t.parent_id,t.name "
."FROM folders AS t,folders AS
p, folders AS r "
."WHERE ((t.parent_id=p.id) AND
(t.root_id=r.id) AND (t.root_id=$id) AND (t.owner_id=".$userid.")) "
."ORDER BY t.parent_id
ASC,t.name ASC;"
;
if($sub_result=mysql_query($query)) {
if(mysql_num_rows($sub_result))
{
while($row=mysql_fetch_row($sub_result)) {
list($sub_id,$sub_pid,$sub_name)=$row;
if($tmp_id!=$sub_pid) {
$tmp_id=$sub_pid;
$leadin.="-";
}
if($aslink) {
printf("%s<a class=\"folder\"
href=\"inbox.php?fid=%s\">%s</a><br>\n",$leadin,$sub_id,$sub_name);
} else {
printf("<option
value=\"%s\">%s%s</option>\n",$sub_id,$leadin,$sub_name);
}
}
$leadin="";
mysql_free_result($sub_result);
}
}
}
}
}
}
---------------------
Johnny Withers
[EMAIL PROTECTED]
p. 601.853.0211
c. 601.209.4985
-----Original Message-----
From: Wakan [mailto:[EMAIL PROTECTED]]
Sent: Monday, February 18, 2002 10:44 AM
To: [EMAIL PROTECTED]
Subject: how to retrieve a hierarchical structure
Hi,
can someone suggest the best way to perform a query that
extract from a category table all the structure, like a filesystem?
For example, I've a table of categorie like this:
ID name descr ID_parent
and I've assigned -1 to the root category.
I'd like to draw a directory-like structure (like windows explorer), but
I don't know how to extract a directory name, and all sub-directory
recursively.
Thaks.
Carlo
---------------------------------------------------------------------
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
