Caculating Nestled Arrays from Mysql DB

[joefazee]

Some nestled calculation.
How did club freedom did the calculation, or how will the database structure looks like?

Please include sample code for me, i`m still learning.

The example is -:

Joseph gave birth to John, James, and Johnson
John gave birth to Peter, Matter, and Potter
James gave birth to Juliana, Justin, and Jane
Johnson gave birth to Jak, Jake and Jacob

Continuously like that,
Peter the son of John also gave birth to another 3 children
And the 3 children also keep giving birth to 3 children each, so
How can I calculate the Total descendants of Joseph?
How can I calculate the total descendants of John or any of the grand children?

Because each of the children also start having grand children, while Joseph grand descendants increases.

I want to use MySql/PHP
The concept is Like http://www.disneytreasures.biz/ or ClubFreedom

All I want to do is to know who bring who?

[nathj]

Hi,

If I understand this correctly it is quite tricky but not impossible.

Stick with me on this and I think we muight have astructure outlined by the end.

It shold be possible to do this iwth two tables, assumuing that you only want parent to child relationships. (I understand from your examples this not a biological thing but the parent child terms hold good)

table: tbl_person
ID
first_name
last_name
dob

table: tbl_relationship
ID
parentID
childID

If john is the very first person in or he jpins of his own accord he has no parent so he exists in tble_person. John introduces James. James now exists in tbl_person and a new record is added to tbl_relationship with John's ID listed under parentId and James' ID listed under childID.

If Mark joins because of James, James is a child and a parent. so our data looks like:

tbl_person
1, John, Johnson, 01/01/1900
2, James, Jameson, 02/02/1902
3, Mark, Markson, 03/03/1903

tbl_relationship
1, 1, 2
2, 2, 3

With this structure it is possible to travers the data for any given person finding out their direct children and then grandchildren or great grandchildren.

I hope this makes sense for you.

Cheers
nathj

[Atli]

Hi.

To get the total number of descendants, you would probably need to use a recursive function.

Consider this:

function count_descendants($parentID) {
  $count = 0;
 
  // Query for all children
  $_sql = "SELECT child_id FROM relationsTable WHERE parent_id = {$parentID}";
  $_result = mysql_query($_sql)
 
  while($_row = mysql_fetch_assoc($_result)) {
    // Add for this child
    $count++; 
 
    // Call this function again for this child
    $count += count_descendants($_row['child_id']);
  }
 
  return $count;
}