Family Tree Approach

[Pineapple]

Hey everyone!  I've been working on a simple database for my family to keep our family tree organized.  I've written all the pages to handle inputing the data into the database, but am getting stuck on the best approach for pulling it all out into a family tree.  I want to keep things simple, so I only want it to display parents (no siblings).

 

However, I'd like the code to be flexible, so it will go as many generations as the database has data for.  I've looked around at a couple of other posts and came across one for a dog pedigree that is essentially the same thing, though it was limited in it's generations.  I've done a bit of research and came across this http://en.wikipedia.org/wiki/Ahnentafel  but I'm not sure if this would actually help me at all.

 

Right now, my mySql table is set up with an ID field, and then fields for the ID's of that entry's mother and father, along with other relevant data.  I'm basically just not sure on what the best way is to cycle through the database and pull everything out.  I'll worry about the formating later, but right now I'd like to just achieve something like this example http://en.wikipedia.org/wiki/Ahnentafel#Example.

 

Thanks for any help you guys can provide.  This is the biggest PHP/mySQL undertaking I've ever attempted, and I'm really just doing it for the learning experience, so I need all the help I can get!  Thanks again

[AV1611]

I would do something like this:

 

for each name, they are a member of a parent group and a parent of a sub-group

 

Now you have 3 "entities" for each name:

 

parent - name - child

 

that persons offspring have his "child" as their "parent" and so forth... Gives a nice pyramid...

[chronister]

If your interested, I have found a Killer family tree app called TNG. http://lythgoes.net/genealogy/software.php

 

I wanted to create a family tree app too, but eventually found this one, it is cheap $29, and it is way better than anything I could create.

 

If you PM me, I will give ya the link to mine and you can take a look at it for yourself

 

 

Sorry I gave nothing to help your original problem. If your dead set on creating your own then good luck on that project, its a pretty cool undertaking.

 

Nate

[Barand]

I've a done a dog pedigree chart that lets you specify any number of generations.

 

The basic data structure it uses is

 

[pre]

+-----+------------------+----------+----------+

| ID  | Name            | FatherID | MotherID |

+-----+------------------+----------+----------+

| 1  | John            |    2    |    3    |

| 2  | George          |    4    |    5    |

| 3  | Emily            |    8    |    7    |

| 4  | Henry            |    6    |    9    |

| 5  | Jane            |  15    |  16    |

| 6  | Cuthbert        |  12    |  14    |

| 7  | Angela          |  20    |  21    |

| 8  | Albert          |  22    |  23    |

[/pre]

 

If you're interested, it's here

http://www.phpfreaks.com/forums/index.php/topic,154265.msg667837.html#msg667837

[Pineapple]

Thanks for the help everybody.  Barand, that's how I have my data set up now, just one table for everything.  I checked out your function in that other forum post.  The main thing is that I don't want to have to specify the number of generations, I'd like the code to go until it runs out of data, how ever many generations that is.  I really have no idea what the best way of doing that would be.

 

Chronister, I checked out that link and that program seems pretty sweet!  However, I'm more doing this to learn PHP more than anything, so as cool as it is, I really don't wanna use it.  Thanks anyway though.

 

Thanks for your help guys, i'm so lost on this one.

[Barand]

To get number of previous generations available

 

<?php
mysql_connect('localhost');
mysql_select_db('test');

$gens=0;
function genCount($id, $level=0)
{
    global $gens;
    if (!$id) return;
    $sql = "select sire,dam FROM dogtable WHERE id=$id";
    $res = mysql_query($sql);
    if (mysql_num_rows($res) > 0)
    {
        $gens = max($gens, $level);
        genCount(mysql_result($res, 0, 'sire'), $level+1);
        genCount(mysql_result($res, 0, 'dam'), $level+1);
    }
}
genCount(1);
echo $gens;
?>

[Neptunus Maris]

BA ... RANNND!

 

 

what up dude...

 

so what are you guys talking about in this topic even though i read the topic title.

 

 

oh yes just link tables or something

 

these should be the fields in the database:

 

ID - for the actual person

parentID - for the parent ID.

[Barand]

If you do a little research (try Google) you'll find most people have two parents, therefore two parent ids are required.

[Jessica]

I didn't quite read all of the above posts, sorry. But for a "family tree" type database I'd do this as a basic design:

 

Person Table

ID

Name

Birthdate, ETC.

 

Relationships Table

Person1ID

Relationship (EG, how is person1 related to person2: father, mother, sibling). Any other relationships can really be derived from the other relationships given. Person A's mother's brother is their uncle.

Person2ID

[Pineapple]

Yeah, I had originally thought about doing two tables: 1 for all the members, and 1 for all the relationships.  After a lot of back and fourth and other people's input, it seems like there's only need for 1 table... set up like this.

 

+-----+------------------+----------+----------+

| ID  | Name            | FatherID | MotherID |

+-----+------------------+----------+----------+

| 1  | John            |    2    |    3    |

| 2  | George          |    4    |    5    |

| 3  | Emily            |    8    |    7    |

| 4  | Henry            |    6    |    9    |

| 5  | Jane            |  15    |  16    |

| 6  | Cuthbert        |  12    |  14    |

| 7  | Angela          |  20    |  21    |

| 8  | Albert          |  22    |  23    |

 

I'm pretty sure that's the setup I'm sticking with... but my issues aren't with the database setup, it's with the PHP.  I don't know what the best way is too cycle through all this data and pull out all the parents for each generation.  It looks like Barand's solution is gonna work out, so I'm going to give that a shot tomorrow.  If I use his function to find how many generations there are, and then use his other function to cycle through N generations, I should be good.  Just gotta change Sire and Dam to Father and Mother pretty much!