Database Hierarchical Structure problem

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
joefazee
Forum Newbie
Posts: 11
Joined: Thu May 29, 2008 7:54 am

Database Hierarchical Structure problem

Post by joefazee »

Hello,

Sorry for my bad English.

I need some help from you on Database Hierarchical Structure, if you have heard about it, or have done any development with it before.

This how the story goes

First, I need a clear understanding of how Database Hierarchical Structure with PHP/MySql.

But before a learn it I have some urgency need of it, I used a class from phpclasses.org that works, but it remain to do something.

The Application is a network marketing application where you have someone bring somebody and somebody bring another person

Something like

Joseph has three children
Those three children (from Joseph) also has three children or even more each
Then, the last three three children also has another three three,
Just like the way files and folder are arranged in our OS (operating system)

So the class I got from phpclasses.org work out the generation tree, but what remain is
Joseph as a parent always get 1% (one percent) of each payment made by his children (Note: not grand children) then again, he(Joseph) get 2% of each payment made by his grand children which are in level 2. (Note: Joseph is in level 0, the children after Joseph are in level 1, then after the children again we have level 2, so always the level is +1, that apply to all parents)

Calculating the percentage from the array returned by the users object is just the problem.

This the structure of the generation tree table

Code: Select all

CREATE TABLE `member_sections` (
  `section_id` bigint(20) NOT NULL default '0',
  `section_left` bigint(20) NOT NULL default '0',
  `section_right` bigint(20) NOT NULL default '0',
  `section_level` int(11) default NULL,
  `section_name` varchar(255) NOT NULL default '',
  PRIMARY KEY  (`section_id`),
  UNIQUE KEY `section_id` (`section_id`)
) TYPE=MyISAM;
This the structure of the member table
(I referenced the id from the generation tree table as the member section id)

Code: Select all

CREATE TABLE `club_profile` (
  `cID` int(11) NOT NULL auto_increment,
  `csectionID` int(11) NOT NULL default '0',
  `cPWD` varchar(65) NOT NULL default '',
  `cmentorID` varchar(16) NOT NULL default '',
  `careaCode` varchar(16) NOT NULL default '',
  `cname` varchar(150) default NULL,
  `cpastPresident` varchar(15) NOT NULL default '',
  `cpresident` varchar(15) NOT NULL default '',
  `cvpEducation` varchar(15) NOT NULL default '',
  `cvpMarketing` varchar(15) NOT NULL default '',
  `cvpMemberShip` varchar(15) NOT NULL default '',
  `csecretary` varchar(15) NOT NULL default '',
  `ctresurer` varchar(15) NOT NULL default '',
  `cvpPublicsity` varchar(15) NOT NULL default '',
  `cvpInvestiment` varchar(15) NOT NULL default '',
  `cvpProperty` varchar(15) NOT NULL default '',
  `ceagentATARM` varchar(15) NOT NULL default '',
  `cstate` varchar(25) NOT NULL default '',
  `cdistric` varchar(30) NOT NULL default '',
  `cactive` enum('Y','N') NOT NULL default 'N',
  `csuspened` enum('Y','N') NOT NULL default 'Y',
  `cdateCreated` datetime default NULL,
  PRIMARY KEY  (`cID`)
) TYPE=MyISAM AUTO_INCREMENT=22 ;
The returned array is as

Code: Select all

$people = array(array("name"=> "Joseph", "level" => 0,
                          array("name" => "Mikel", "level" => 1,
           array("name" => "John", "level" => 1,
                          array("name" => "Oko", "level" => 2,
                          array("name" => "James", "level" => 1,
);
Please the array continue just like that,
Parent take profit at some level



Please if there is anything you can do, i need your advice.

Thank you.
Joe
User avatar
Kieran Huggins
DevNet Master
Posts: 3635
Joined: Wed Dec 06, 2006 4:14 pm
Location: Toronto, Canada
Contact:

Re: Database Hierarchical Structure problem

Post by Kieran Huggins »

you only need to reference the parent in each row. you can select the children by looking for the parent id:

Code: Select all

people
=========
id (int)
name (str)
favourite_colour (str)
parent_id (int)

Code: Select all

/* selects Bob */
SELECT * FROM `people` WHERE `name` LIKE "Bob" 
 
/* selects Bob's kids, assuming Bob has an id of 1 */
SELECT * FROM `people` WHERE `parent_id` = "1"
Does that help?
joefazee
Forum Newbie
Posts: 11
Joined: Thu May 29, 2008 7:54 am

Re: Database Hierarchical Structure problem

Post by joefazee »

Kieran Huggins wrote:you only need to reference the parent in each row. you can select the children by looking for the parent id:

Code: Select all

people
=========
id (int)
name (str)
favourite_colour (str)
parent_id (int)

Code: Select all

/* selects Bob */
SELECT * FROM `people` WHERE `name` LIKE "Bob" 
 
/* selects Bob's kids, assuming Bob has an id of 1 */
SELECT * FROM `people` WHERE `parent_id` = "1"
Does that help?
I have gotten that working, the only thing remaing is, i want each parent to take some profit if another child signup under him, e.g the people that comes under you direct, earn you 8% of their payment each and the people that come under those who are under you earn you like 3% of their payment.

A member may person $10, $20 or even more.
User avatar
Kieran Huggins
DevNet Master
Posts: 3635
Joined: Wed Dec 06, 2006 4:14 pm
Location: Toronto, Canada
Contact:

Re: Database Hierarchical Structure problem

Post by Kieran Huggins »

You just have to run a second query to find the parent's parent.
Post Reply