Posted by jfanaian on 04/05/06 20:49

I am having a HUGE problem doing a new hierarchy system for a company.
Basically, the system they currently have is based on a parent
reference. Here is the table structure dump:


CREATE TABLE `associate` (
`id` int(99) NOT NULL auto_increment,
`code` varchar(99) NOT NULL default '',
`date_hired` int(10) NOT NULL default '0',
`last` varchar(32) NOT NULL default '',
`name` varchar(20) NOT NULL default '',
`middle` varchar(20) NOT NULL default '',
`title` varchar(4) NOT NULL default '',
`level` varchar(5) NOT NULL default 'LO',
`street` varchar(250) NOT NULL default '',
`city` varchar(50) NOT NULL default '',
`state` varchar(50) NOT NULL default '',
`zip` int(5) NOT NULL default '0',
`hphone` varchar(15) NOT NULL default '',
`cphone` varchar(15) NOT NULL default '',
`fax` varchar(15) NOT NULL default '',
`email` varchar(250) NOT NULL default '',
`wfmemail` varchar(250) NOT NULL default '',
`dob` int(10) NOT NULL default '0',
`dlid` varchar(20) NOT NULL default '',
`ss` varchar(15) NOT NULL default '',
`sponsor_code` varchar(99) NOT NULL default '',
`wfl` char(1) NOT NULL default '',
`wbs` char(1) NOT NULL default '',
`wis` char(1) NOT NULL default '',
`lft` int(99) NOT NULL,
`rgt` int(99) NOT NULL,
PRIMARY KEY (`id`)
)

Basically, sponsor_code matches the code of the parent for that
associate. The problem is that this system is very inefficient. Now I'm
trying to incorporate a new system following the guide at:
http://dev.mysql.com/tech-resources/articles/hierarchical-data.html

I have changed everything so it has the lft and rgt and the numbers
seem to be correct. I tried doing an upline for any of the associates
and it works perfectly. The problem is doing the downline. When I do
that it seems to skip the first generation and then the generation
numbers mess up on some of the rows.

You can see the query and the downline report from the top of the tree
(ID 820) at https://dev.mywfm.com/tree.php

The fields included are gen, id, code, left, and right.

Also, here is a dump of the downline using the old system so you can
take a look at what the data is supposed to be like:
https://dev.mywfm.com/dl.php

Please let me know if anyone has any suggestions or ideas on how to fix
the problem.
Thanks

[Back to original message]