display pedigree table fron mysql with php by levels

[logandro]

I am working on this page concept of pedigree. How can I foreach or create a loop that can get 6 levels of parents, for example name= 1 ; nfather=2; nmohter = 3 and the loop continue to get or consider nfather as a name = 2 and get his parents.

this is my table any help plees

CREATE TABLE horsetest01 (
  idg       int(255) AUTO_INCREMENT NOT NULL,
  name      varchar(100),
  n_father  int(100),
  n_mother  int(100),
  gender    enum ('STALION','MARSE') NOT NULL,
  /* Keys */
  PRIMARY KEY (idg)
) ENGINE = InnoDB
  AUTO_INCREMENT = 8;

CREATE INDEX horsetest01_index02
  ON horsetest01
  (name, n_father, n_mother);

 

[gw1500se]

What have you tried and what is not working?

[Barand]

About 15 years ago I wrote a function to produce this

from data very much like yours EG

SELECT * FROM horse;
+----+------+------+------+----------+
| id | name | sire | dam  | gender   |
+----+------+------+------+----------+
| 26 | A    |   27 |   39 | STALLION |
| 27 | B    |   28 |   40 | STALLION |
| 28 | C    |   29 |   40 | STALLION |
| 29 | D    |   30 |   40 | STALLION |
| 30 | E    |   31 |   41 | STALLION |
| 31 | F    |   32 |   41 | STALLION |
| 32 | G    |   33 |   42 | STALLION |
| 33 | H    |   33 |   42 | STALLION |
| 34 | I    |   35 |   43 | STALLION |
| 35 | J    |   36 |   43 | STALLION |
| 36 | K    |   37 |   44 | STALLION |
| 37 | L    |   38 |   44 | STALLION |
| 38 | M    |   34 |   45 | STALLION |
| 39 | N    |   29 |   46 | MARE     |
| 40 | O    |   27 |   51 | MARE     |
| 41 | P    |   28 |   51 | MARE     |
| 42 | Q    |   29 |   50 | MARE     |
| 43 | R    |   30 |   50 | MARE     |
| 44 | S    |   31 |   49 | MARE     |
| 45 | T    |   32 |   49 | MARE     |
| 46 | U    |   33 |   48 | MARE     |
| 47 | V    |   34 |   48 | MARE     |
| 48 | W    |   35 |   46 | MARE     |
| 49 | X    |   36 |   46 | MARE     |
| 50 | Y    |   37 |   47 | MARE     |
| 51 | Z    |   38 |   47 | MARE     |
+----+------+------+------+----------+

It uses a recursive* function which gets the sire and dam for the horse $id and outputs the name. It then calls itself for the sire and the dam which outputs their names and get their parents. This is repeated until the specified level of ancestry is reached.

Is that the sort of thing you are trying to do?

If so, give it a go using the method I described and come back if oyu get stuck

* recursion : see recursion

[logandro]

yes this is exactly what im trying to do , think you so uch , if you can give me the fils pleez , 

Edited November 13, 2019 by logandro

[logandro]

select  idg,
        n_father,
        n_mother 
from    (select * from horsetest
         order by idg, n_father) products_sorted,
        (select @pv := '7') initialisation
where   find_in_set(idg, @pv)
and     length(@pv := concat(@pv, ',', n_father))

 i have truble to get the mother -> idg i try it with this  but it dosn t worck 

and     length(@pv := concat(@pv, ',', n_father, ',',n_mother))

 

[Barand]

The basic processing is (pseudocode)

function tree (id) 
{
    get id, name, sire, dam for id
    if not found
        return
    endif
    output id/name
    tree(sire)
    tree(dam)
}

// output pedigree tree for for horse X
tree(X)

 

[logandro]

hello i really appreciate 
but i still having a problem on query , how can i get the mother and father in the same sql query , at the moment i get it separately like this one 

select  idg, n_mother 
from    (select * from horsetest 
         order by idg, n_mother) products_sorted,
        (select @pv := 'idg') initialisation
where   find_in_set(idg, @pv)
and     length(@pv := concat(@pv, ',', n_mother)) 

any help pleez 

[Barand]

Input data (horsetest)

+----+---------+------+------+----------+------------+
| id | name    | sire | dam  | gender   | dob        |
+----+---------+------+------+----------+------------+
|  1 | Horse A | NULL | NULL | STALLION | 2005-01-15 |
|  2 | Horse B | NULL | NULL | MARE     | 2005-03-19 |
|  3 | Horse C | NULL | NULL | MARE     | 2006-03-11 |
|  4 | Horse D |    1 |    2 | STALLION | 2009-05-10 |
|  5 | Horse E |    1 |    3 | MARE     | 2010-08-25 |
+----+---------+------+------+----------+------------+

then to get mother and father

SELECT h.id
     , h.name
     , h.gender
     , TIMESTAMPDIFF(YEAR, h.dob, CURDATE()) as age
     , h.sire as f_id
     , s.name as father
     , h.dam as m_id
     , d.name as mmother
FROM horsetest h
         JOIN
     horsetest s ON h.sire = s.id
         JOIN
     horsetest d ON h.dam = d.id;     

giving

+----+---------+----------+------+------+---------+------+---------+
| id | name    | gender   | age  | f_id | father  | m_id | mmother |
+----+---------+----------+------+------+---------+------+---------+
|  4 | Horse D | STALLION |   10 |    1 | Horse A |    2 | Horse B |
|  5 | Horse E | MARE     |    9 |    1 | Horse A |    3 | Horse C |
+----+---------+----------+------+------+---------+------+---------+

 

Edited November 14, 2019 by Barand