Posted by Jerry Stuckle on 04/05/06 23:58

jfanaian@gmail.com wrote:
> 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
>

Since this is a MySQL problem, I'd recommend asking in a MySQL newsgroup - such
as comp.databases.mysql.


--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstucklex@attglobal.net
==================

[Back to original message]