Google this: oracle sql "Underneath the Mango Tree"
The URL to an excellent article by Stephane Faroult will
be the only hit.
If you click the URL, you will need to be a member of IUOG.
If you click 'View HTML' the article itself will appear, though
not well formatted.
Such is the magic of google.
Jared
On Mon, 2005-06-13 at 12:16, Ing. Branislav Gerzo wrote:
> Hi all,
>
> I have question about storing hierarchical values in db. I found some
> methods, pick up the first easiest, but I ahve question about that -
> have someone coded some methods, or knows some module, which helps me
> with this issue ?
>
> Ok, here is infos:
>
> MYSQL tables:
> CREATE TABLE `cat_name` (
> `id` int(11) unsigned NOT NULL auto_increment,
> `cat_name` varchar(150) NOT NULL default '',
> PRIMARY KEY (`id`),
> UNIQUE KEY `cat_name` (`cat_name`)
> ) ENGINE=MyISAM DEFAULT CHARSET=utf8
>
> CREATE TABLE `cat_tree` (
> `id` int(9) unsigned NOT NULL auto_increment,
> `parent_id` int(11) unsigned NOT NULL default '0',
> `child_id` int(11) unsigned NOT NULL default '0',
> PRIMARY KEY (`id`),
> KEY `parent_id` (`parent_id`),
> KEY `child_id` (`child_id`)
> ) ENGINE=MyISAM DEFAULT CHARSET=utf8
>
> input data:
>
> name = 'some name...'
> category = 'foo/bar/foo/bar'
> name = 'something other...'
> category = 'foo/bar/foo/bar'
>
> Perl snippet:
>
> foreach my $cat ( split '/', $hr->{category} ) {
> next if $hash{$cat}++; #skip duplicates
> $hr->{cat_id} = cat_get($cat) || cat_insert($cat);
> print $hr->{cat_id}, "\n";
> #need other functions to store/get (recursive) values into cat_tree
> }
>
> sub cat_get {
> my ($id) = $dbh->selectrow_array("select id from cat_name where
> cat_name = ?", {}, shift);
> return ($id);
> }
>
> sub cat_insert {
> my $cat_insert = $dbh->prepare("INSERT INTO cat_name (cat_name)
> values (?)");
> $cat_insert->execute( shift );
> return $dbh->{'mysql_insertid'};
> }
>
> I don't know if this problem is solved like my "code", but it it my
> first try. Could anyone helps on this, please ?
>
> thanks.
