The Logic Escapes Me

[litebearer]

Being an egotistical old fart, I have convinced myself that my version of a family tree is better (for my purposes and audience) than what is available out there in the world. Having said that, reality has come to face me on the next step in my ‘wonderful creation' http://nstoia.com/easy_family/easy_tree/f_view.php) [if you haven’t noticed the huge grin on my face - I am laughing at myself].

 

For the most part, the tree functions as intended.

 

There is one database table which, among some other fields, has id, spouse, father and mother as fields (the data in spouse, father and mother are the repective ID's for those individuals).  Children and siblings are easily determined from those fields.

 

I hope to add a Decendents page, ie

Primary Person

  Child 1

  Grandchild 1

    Great Grandchild 1

      Great Great Grandchild 1

        Great Great Great Grandchild 1

        Great Great Great Grandchild 2

      Great Great Grandchild 2

        Great Great Great Grandchild 3

        Great Great Great Grandchild 4

    Great Grandchild 2

      Great Great Grandchild 3

        Great Great Great Grandchild 5

        Great Great Great Grandchild 6

      Great Great Grandchild 4

        Great Great Great Grandchild 7

        Great Great Great Grandchild 8

  Grandchild 2

    Great Grandchild 3

      Great Great Grandchild 5

        Great Great Great Grandchild 9

        Great Great Great Grandchild 10

      Great Great Grandchild 6

        Great Great Great Grandchild 11

        Great Great Great Grandchild 12

    Great Grandchild 4

      Great Great Grandchild 7

        Great Great Great Grandchild 13

        Great Great Great Grandchild 14

      Great Great Grandchild 8

        Great Great Great Grandchild 15

        Great Great Great Grandchild 16

  Child 2

  Grandchild 3

    Great Grandchild 5

      Great Great Grandchild 9

        Great Great Great Grandchild 17

        Great Great Great Grandchild 18

      Great Great Grandchild 10

        Great Great Great Grandchild 19

        Great Great Great Grandchild 20

    Great Grandchild 6

      Great Great Grandchild 11

        Great Great Great Grandchild 21

        Great Great Great Grandchild 22

      Great Great Grandchild 12

        Great Great Great Grandchild 23

        Great Great Great Grandchild 24

  Grandchild 4

    Great Grandchild 7

      Great Great Grandchild 13

        Great Great Great Grandchild 25

        Great Great Great Grandchild 26

      Great Great Grandchild 14

        Great Great Great Grandchild 27

        Great Great Great Grandchild 28

    Great Grandchild 8

      Great Great Grandchild 15

        Great Great Great Grandchild 29

        Great Great Great Grandchild 30

      Great Great Grandchild 16

        Great Great Great Grandchild 31

        Great Great Great Grandchild 32

The logic escapes me. I am TERRIBLE at mutli-dimensional arrays; however, I strongly suspect that is the most appropriate route to take.

 

Any ideas? (Remember begining of this post - I AM OLD FART - needs VERY simple instructions)

 

Many thanks,

 

Lite...

 

 

[KevinM1]

You'll need a pivot table.  Essentially, something like this:

 

table children_parents

id                child_id                    parent_id

-----------------------------------------------------

1                          23                              42

2                          23                              43

3                          99                              27

.

.

.

 

An OOP approach would help, too.  A Person object could have fields containing their db id, the ids of their parents, siblings, and direct descendants.

[ignace]

CREATE TABLE adjacency (
  parent_id INT NOT NULL,
  child_id INT NOT NULL,
  PRIMARY KEY (parent_id, child_id)
) ENGINE = MyISAM;

CREATE TABLE family (
  id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
  first_name VARCHAR(32) NOT NULL CHARACTER SET utf8 COLLATE utf8_bin,
  last_name VARCHAR(32) NOT NULL CHARACTER SET utf8 COLLATE utf8_bin,
  birth_date DATE NOT NULL,
  eol_date DATE DEFAULT NULL, -- day they died
  foto_filename VARCHAR(32) DEFAULT '',
) ENGINE = MyISAM;

 

Insert all family members into the database table:

 

INSERT INTO family (id, first_name, last_name, birth_date, eol_date, foto_filename) VALUES
(0, 'Adam', 'Eve', '0000-00-00', '1970-09-05', ''),
(1, 'Me', 'Self', '1996-09-05', NULL, '');

 

Specify the parent(s?)/child combination:

 

INSERT INTO adjacency (parent_id, child_id) VALUES
(0,1); -- parent.id 0 is the parent of all parents 

 

The query would look somewhat similar to:

 

SELECT concat(parent.first_name, ' ', parent.last_name) AS parent_name, parent.birth_date AS parent_birth_date, parent.eol_date AS parent_eol_date, parent.foto_filename AS parent_picture
       concat(child.first_name, ' ', child.last_name) AS child_name, child.birth_date AS child_birth_date, child.eol_date AS child_eol_date, child.foto_filename AS child_picture
FROM adjacency
JOIN family AS parent ON family.id = adjacency.parent_id
JOIN family AS child ON family.id = adjacency.child_id
ORDER BY adjacency.parent_id ASC, adjacency.child_id ASC

 

This should put you on your way. If you can give us a better idea as to how you want it to look we can adjust/advice more.

[litebearer]

First, thank you both for taking the time to respond and for ideas and coding.

 

Currently, I am using one table (see attached image).

 

On the "Add new person" form there are 3 select lists generated from the table - Spouse, Father and Mother. Each possible choice on the list has the appropriate ID from the table.

 

When in the 'View' section, a select list of all people in the table is generated, as links. when a person is clicked the page is redisplayed with the chosen person's data in the display area of the screen. The script does several queries - (1) gets Primary person's data, (2) gets Spouse info, (3) gets father info, (4) get mother info, (5) gets all ids and info where primary is the mother/father OR where the spouse is the mother/father [this is so as to list stepchildren], and finally (6) gets all ids where mother/father is listed as mother/father [these are siblings].

 

 

All this works well (see link in first post). I am looking top add a 'lengthier' listing of the Primary's descendants. ie each child , under which each child's children are grouped and so on down the 'tree'.

 

Make sense?

example of desiredd output...

Adam (this is primary person displayed)

Cain (Adam's child)

  Sasquatch (Cain's Child)

  Napoleon (Cain's child)

    FDR

Able (Adam's child) [child to primary]

  Fritz the cat (Able's child) [grandchild to primary]

    TweetyBird (Fritz's child) [great grandchild to primary]

      Road Runner (Tweety's child) [great great grandchild to primary]

       

 

[attachment deleted by admin]

[ignace]

Yup then my advice should do the trick.