Perl/MySQL: Writing tree of categories to screen

Query on how to write a mysql table containing a category tree to a webpage

[Allen]

Hi all,

I have a table of categories in the following structure:
id, name, isin

'isin' is the id of a category that this category is inside. The others should be self-explanatory.

What's the best way of sorting and writing this to the screen in a nice tree diagram?

EG. Something like:
/
/cat1
/cat2
/cat2/cat3
/cat2/cat4
/cat5
/cat5/cat6
/cat5/cat6/cat7
...and so on...

Regards,

Allen

[Allen]

[WARNING: If you are offended by people answering their own questions, turn back now]

YES!
Took all day, but I've done it!

sub catList {
# Get list of categorys and compile them into a visual list
$dbh = DBI->connect ("DBI:mysql:host=$mysql{hostname};database=$mysql{database}", $mysql{username}, $mysql{password}, {PrintError => 1, RaiseError => 1});

$sth = $dbh->prepare ("SELECT * FROM category ORDER BY id");
$sth->execute ();

while ( ($id, $name, $isin) = $sth->fetchrow_array()) {
push @categories, { id => "$id", name => "$name", isin => "$isin" };
}

$sth->finish ();
$dbh->disconnect ();

print "/ <br>\n";
list_subsections("0","");
}

sub list_subsections {
my $catid = $_[0]; # id of current category
my $tree = $_[1]; # text to put in front of tree

foreach $record (@categories) {

if ($record->{'isin'} == $catid) {

$tree .= "/" . "$record->{'name'}";
print "$tree <BR>\n";

list_subsections ("$record->{'id'}", "$tree");

# Strip the last directory out of it

my $met = 0;
my $leng = length ($tree);
my $count = $leng;

until ($count < 0) {

$strng = substr($tree, $count, 1);

if (substr($tree,$count,1) eq "/") {
$remove = substr($tree, $count);
$tree =~ s/$remove$//;
$count = -1;
} # end if substr

$count = $count - 1;
} # until $count

# END OF STRIPPING

} # end if $record->isin
} # end foreach

} # end sub

Gives the lovely:

/
/Directory 1
/Directory 1/SubDirectory 1
/Directory 1/SubDirectory 1/SubSubDirectory 1
/Directory 1/SubDirectory 2
/Directory 1/SubDirectory 2/SubSubDriectory 2
/Directory 1/SubDirectory 2/SubSubDriectory 2/SubSubSubDirectory 1
/Directory 1/SubDirectory 2/SubSubDriectory 2/SubSubSubDirectory 2
/Directory 2
/Directory 3

From:

id, name, isin
3, Directory 2, 0
2, Directory 1, 0
4, SubDirectory 1, 2
5, SubSubDirectory 1, 4
6, SubDirectory 2, 2
7, SubSubDriectory 2, 6
8, SubSubSubDirectory 1, 7
9, SubSubSubDirectory 2, 7
10, Directory 3, 0

I'm pretty sure this is the best way of doing it (my way usually is :) ), but if you have any suggestions, please say.

Regards,

Allen

[brotherhood of LAN]

You can also do this in DOS

I believe its somethign like

c:/pathtodirs tree > prn (thats probably wrong its something like that) that outputs the same thing.

probably not applicable to your situation but the same end result....somehow using less code ;)

/added i think prn is to send to printer.....there is another command to do it to screen i think...(must read up on old notes :)

[Allen]

Yeah, right!
I'm going to use DOS commands in a CGI script designed for a linux server.

Anyway, the purpose of the code isn't to print a directory listing, it's to print categorys (like google directory type thing). There are much easier ways of doing directory trees.

Allen