Hierarchy using MySQL and PHP

**Jerry Stuckle** · Apr 5 '06, 08:15 PM

Re: Hierarchy using MySQL and PHP

jfanaian@gmail. com wrote:[color=blue]
> 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/...ical-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
>[/color]

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@attgl obal.net
=============== ===

**Jonathan** · Apr 5 '06, 08:35 PM

Re: Hierarchy using MySQL and PHP

jfanaian@gmail. com wrote:[color=blue]
> 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[/color]

Although I can not give you any solution, I noticed it took 577 quereis
to get your results... maybe the use of nested sets would be more cost
efficient:

http://www.codeproject.com/database/nestedsets.asp

**onedbguru@firstdbasource.com** · Apr 5 '06, 08:45 PM

Re: Hierarchy using MySQL and PHP

Before I go into a relational model of this a quick comment about your
current table definition.

for 'ss' (I assume this to be a social security number) it should NEVER
have a default value. You cannot have an employee without a SS#.
Period. I would make it NOT NULL but remove the default value. In
fact, I would remove most of the default values as most of this data
must exist for all employees.

Here is a classic example - the "employee" database - shipped with DEC
(now Oracle) Rdb
TABLE EMPLOYEES
Column Name
----------- ---------
EMPLOYEE_ID CHAR(5)
LAST_NAME CHAR(14)
FIRST_NAME CHAR(10)
MIDDLE_INITIAL CHAR(1)
ADDRESS_DATA_1 CHAR(25)
ADDRESS_DATA_2 CHAR(20)
CITY CHAR(20)
STATE CHAR(2)
POSTAL_CODE CHAR(5)
SEX CHAR(1)
BIRTHDAY DATE
STATUS_CODE CHAR(1)

TABLE JOB_HISTORY
Column Name
----------- ---------
EMPLOYEE_ID CHAR(5)
JOB_CODE CHAR(4)
JOB_START DATE
JOB_END DATE
DEPARTMENT_CODE CHAR(4)
SUPERVISOR_ID CHAR(5)

TABLE JOBS
Column Name
----------- ---------
JOB_CODE CHAR(4)
WAGE_CLASS CHAR(1)
JOB_TITLE CHAR(20)
MINIMUM_SALARY INTEGER(2)
MAXIMUM_SALARY INTEGER(2)

TABLE DEPARTMENTS
Column Name
----------- ---------
DEPARTMENT_CODE CHAR(4)
DEPARTMENT_NAME CHAR(30)
MANAGER_ID CHAR(5)
BUDGET_PROJECTE D INTEGER
BUDGET_ACTUAL INTEGER

A view to find your current status:

table current_job
Information for table CURRENT_JOB

Columns for view CURRENT_JOB:
Column Name Data Type Domain
----------- --------- ------
LAST_NAME CHAR(14)
FIRST_NAME CHAR(10)
EMPLOYEE_ID CHAR(5)
JOB_CODE CHAR(4)
DEPARTMENT_CODE CHAR(4)
SUPERVISOR_ID CHAR(5)
JOB_START DATE VMS
Source:
SELECT E.LAST_NAME,
E.FIRST_NAME,
E.EMPLOYEE_ID,
JH.JOB_CODE,
JH.DEPARTMENT_C ODE,
JH.SUPERVISOR_I D,
JH.JOB_START
FROM JOB_HISTORY JH,
EMPLOYEES E
WHERE JH.EMPLOYEE_ID = E.EMPLOYEE_ID
AND JH.JOB_END IS NULL;

As you can see, as long as JOB_END is NULL - you are still working in
that position.

**jfanaian@gmail.com** · Apr 6 '06, 06:15 PM

Re: Hierarchy using MySQL and PHP

That is what I am trying to do. That is what is currently running. I am
trying to move into using a nested set.

**Sandy Pittendrigh** · Apr 17 '06, 12:35 AM

Re: Hierarchy using MySQL and PHP

I didn't study your table definition carefully, so maybe I'm assuming
too much from the subject heading and first paragraph
of your post.

But if you are trying to model hierarchical data,
that's not so easy to do with relations
(see Data on the Web by Bunneman)...not efficiently
anyway.

For truly hierarchical data, think about deep-sixing mysql
and using XML data, SleeplyCat and Xpath.
(SleepyCat or Exist or even Xindice).

Oracle and Mircosoft Sql server can also handle Xpath
now.

Hierarchy using MySQL and PHP

Hierarchy using MySQL and PHP

Comment

Comment

Comment

Comment

Comment