Dendrite

1. Introduction
2. SQL Comment organiser les données ?
3. SQL Quelle requête ?
4. PHP Comment produire une liste numérotée HTML ?

1) Introduction

0, 1 ou 2 parents ?

La généalogie est un bon exemple de récursivité pas artificiel du tout, puisqu'il s'appuie sur la biologie, ou en tout cas, sur la filiation parentale, qui n'a pas toujours à voir avec la filiation génétique. La récursivité d'une arborescence hiérarchique comme dossier / sous-dossier / sous-sous dossier / fichier fonctionne sur 1 enfant qui appartient forcément à 1 et 1 seul parent, en dehors du noeud initial. Idem pour la récursivité de la hiérarchie d'une entreprise. Au contraire, dans le cas de la filiation parentale, un enfant hérite de 0, 1 ou 2 parents... car enfant de parent inconnu, ou enfant de parent solo, ou enfant de 2 parents...

Où poser la récursivité ? dans le SQL ou dans le PHP ?

Si notre technologie SQL est PostgreSQL ou DB2, on peut tout à fait organiser la récursivité dès le SQL, en faisant une vue par exemple qui utilise une requête récursive et je serais ravie que dans les commentaires, quelqu'un nous propose cette requête opérationnelle dans les 2 technologies, ou un lien vers un billet sur le sujet ! Car si SQL gère la récursivité, cela me paraît souhaitable de l'utiliser en priorité. Utiliser la puissance du SGBD au maximum, et ne traiter en PHP que ce qui n'est pas très bien traité par le SGBD me paraît une bonne pratique.

Mais nous sommes en MYSQL, qui ne gère pas les requêtes récursives au jour où j'écris. Nous traiterons donc de la récursivité dans la fonction PHP finale.

2) SQL Comment organiser les données ?

Tous les individus tiennent sur une table, et 2 champs parent1 et parent2 (id de type numérique) qui peuvent donc être nuls, vont devoir, s'ils sont remplis, provenir obligatoirement d'un id de la présente table. On fait donc 2 clés étrangères en auto-jointure.



Je vous propose de créer la table geneal comme suit

Code SQL :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
CREATE TABLE IF NOT EXISTS `geneal` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `genre` enum('H','F','N') NOT NULL,
  `nom_naissance` varchar(100) NOT NULL,
  `nom_usage` varchar(100) DEFAULT NULL,
  `prenom` varchar(100) NOT NULL,
  `naissance` date NOT NULL,
  `deces` date DEFAULT NULL,
  `parent_1` int(11) DEFAULT NULL,
  `parent_2` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `unicite_1` (`nom_naissance`,`prenom`,`naissance`,`parent_1`),
  UNIQUE KEY `unicite` (`nom_naissance`,`prenom`,`naissance`,`parent_2`),
  KEY `genre` (`genre`),
  KEY `prenom` (`prenom`),
  KEY `deces` (`deces`),
  KEY `parent_1` (`parent_1`),
  KEY `parent_2` (`parent_2`),
  KEY `nom_naissance` (`nom_naissance`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=15 DEFAULT CHARSET=utf8;
ALTER TABLE `geneal`
  ADD CONSTRAINT `par1FK` FOREIGN KEY (`parent_1`) REFERENCES `geneal` (`id`),
  ADD CONSTRAINT `par2FK` FOREIGN KEY (`parent_2`) REFERENCES `geneal` (`id`);

Dans cette table, nous saisirons les descendants de Roland MARCI, personnage de fiction de Plus Belle La Vie sur France 3.
C'est un cas intéressant car cette famille a son lot de divorces, d'homo-parentalité, d'adoption, de famille recomposée... Toutes choses qu'on aimerait gérer.
Et puis bon, j'aime bien Plus belle la vie, c'est ainsi. Il paraît d'ailleurs que l'actrice qui joue Blanche Marci regarde en cachette "Demain nous appartient" sur TF1. Je suis choquée !
Au passage, j'ai juste inventé une enfant supplémentaire à Roland, enfant décédée à 14 mois...

Code SQL :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
INSERT INTO `geneal` (`id`, `genre`, `nom_naissance`, `nom_usage`, `prenom`, `naissance`, `deces`, `parent_1`, `parent_2`) VALUES
(1, 'H', 'MARCI', NULL, 'Roland', '1946-04-14', NULL, NULL, NULL),
(2, 'F', 'LENOIR', NULL, 'Iris', '1948-09-18', '1980-12-14', NULL, NULL),
(3, 'H', 'MARCI', 'MARCI-RIVA', 'Thomas', '1970-02-12', NULL, 1, 2),
(4, 'H', 'RIVA', 'MARCI-RIVA', 'Gabriel', '1972-01-10', NULL, NULL, NULL),
(5, 'H', 'SIDIBE', 'MARCI-RIVA', 'Baptiste', '1997-02-07', NULL, 3, 4),
(6, 'F', 'ALASSANE', 'MARCI-RIVA', 'Thérèse', '1999-03-30', NULL, 3, 4),
(7, 'F', 'JIMEZ', NULL, 'Emma', '1998-04-22', NULL, NULL, NULL),
(8, 'H', 'MARCI-JIMEZ', NULL, 'Mathis', '2018-01-07', NULL, 5, 7),
(9, 'F', 'DUPONT', 'MARCI', 'Mireille', '1947-05-12', '1987-06-25', NULL, NULL),
(10, 'F', 'DUBOIS', 'MARCI', 'Blanche', '1968-07-12', NULL, NULL, NULL),
(11, 'F', 'MARCI', NULL, 'Anne', '1967-02-12', '1968-04-12', 1, 9),
(12, 'H', 'MARCI', NULL, 'François', '1965-05-17', NULL, 1, 9),
(13, 'H', 'MARCI', NULL, 'Lucas', '1985-09-12', NULL, 12, 10),
(14, 'F', 'MARCI', 'REVEL', 'Johanna', '1989-08-25', NULL, 12, 10);

Ce qui nous donne



3) SQL Quelle requête ?

Nous parlons de la (relativement) simple requête qui permet de faire remonter le cas échéant les enfants d'un seul parent :

Code SQL :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
select id,
if(genre="F","Mme","M") as civ,
if(nom_usage is NULL,nom_naissance,concat(nom_usage," (",nom_naissance,")")) as nom,
prenom,
date_format(naissance,"%d/%m/%Y") as naissance_fr,
case
  when deces is NULL AND  TIMESTAMPDIFF(YEAR,naissance,CURRENT_DATE)>=2 then concat(TIMESTAMPDIFF(YEAR,naissance,CURRENT_DATE)," ANS")
  when deces is not NULL then NULL
  else concat( TIMESTAMPDIFF(MONTH,naissance,CURRENT_DATE)," MOIS")
end as age,
case
  when deces is NOT NULL AND TIMESTAMPDIFF(YEAR,naissance,deces)>=2 then concat("DCD LE ", date_format(deces,"%d/%m/%Y")," A ",TIMESTAMPDIFF(YEAR,naissance,deces)," ANS")
  when deces is NULL then NULL
  else concat("DCD LE ", date_format(deces,"%d/%m/%Y")," A ", TIMESTAMPDIFF(MONTH,naissance,deces)," MOIS")
end as deces_age
from geneal
where parent_1=? OR parent_2=?
order by naissance, prenom;

Qui nous retournera pour Roland Marci ses 2 enfants adultes et son enfant (ajoutée par moi) décédée en bas âge :



4) PHP Comment produire une liste numérotée HTML ?

C'est ici que nous aurons besoin de gérer la récursivité. Comment gère-t-on cela ?
En faisant une fonction qui... s'appelle elle-même.
Prenons l'image du miroir qui renvoie un miroir etc. etc. etc. C'est une forme de récurrence. Ce qu'il importe de savoir avant de se pencher sur la récursivité, c'est de savoir 1) Quand entre-t-on ? 2) Quand sort-on ? pour ne pas commettre... une récurrence infinie.
Notre fonction récurrente nous retourne les enfants (via une connexion à notre table geneal) d'un parent via son id.

1er tour : on entre au point de l'id parent pour retourner ses enfants.
Dernier tour : on sort quand parmi toute la descendance possible et imaginable, on aboutit à des gens qui n'ont pas d'enfant, cul de sac de tous les chemins, si on veut.

Code PHP :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
function fetch_children_list(PDO $db,$parent_id=0,$ol=''){
  $sql='select id,
  if(genre="F","Mme","M") as civ,
  if(nom_usage is NULL,nom_naissance,concat(nom_usage," (",nom_naissance,")")) as nom,
  prenom,
  date_format(naissance,"%d/%m/%Y") as naissance_fr,
  case
    when deces is NULL AND  TIMESTAMPDIFF(YEAR,naissance,CURRENT_DATE)>=2 then concat(TIMESTAMPDIFF(YEAR,naissance,CURRENT_DATE)," ANS")
    when deces is not NULL then NULL
    else concat( TIMESTAMPDIFF(MONTH,naissance,CURRENT_DATE)," MOIS")
  end as age,
  case
    when deces is NOT NULL AND TIMESTAMPDIFF(YEAR,naissance,deces)>=2 then concat("DCD LE ", date_format(deces,"%d/%m/%Y")," A ",TIMESTAMPDIFF(YEAR,naissance,deces)," ANS")
    when deces is NULL then NULL
    else concat("DCD LE ", date_format(deces,"%d/%m/%Y")," A ", TIMESTAMPDIFF(MONTH,naissance,deces)," MOIS")
  end as deces_age
  from geneal
  where parent_1='.$parent_id.' OR parent_2='.$parent_id.' 
  order by naissance, prenom';
  $stmt = $db->query($sql);
  $data=$stmt->fetchAll();
  if(isset($data[0])){//si enfant(s) seulement
    $ol .= '<ol>';
    foreach($data as $row){
      $ol .='<li>'. $row['civ'].' '.$row['nom'].' '.$row['prenom'].' '.$row['naissance_fr'].' '.$row['age'].' '.$row['deces_age'].'</li>';
      $ol = fetch_children_list($db,$row['id'], $ol);//la fonction s'appelle elle-même à l'intérieur de son premier appel
    }
    $ol .='</ol>';
  }
  return $ol;
}
//pour tester
$eldest_id=1;
$params=array(
  PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
  PDO::ATTR_EMULATE_PREPARES => false
);
$pdo = new PDO('mysql:host=localhost;port=3306;dbname=entrepot;charset=utf8','root', '', $params);
$liste_recursive=fetch_children_list($pdo,$eldest_id);
$pdo=NULL;
echo $liste_recursive;

Générera ce code source :

Code HTML :

Sélectionner tout - Visualiser dans une fenêtre à part

<ol><li>M MARCI François 17/05/1965 53 ANS </li><ol><li>M MARCI Lucas 12/09/1985 32 ANS </li><li>Mme REVEL (MARCI) Johanna 25/08/1989 28 ANS </li></ol><li>Mme MARCI Anne 12/02/1967  DCD LE 12/04/1968 A 14 MOIS</li><li>M MARCI-RIVA (MARCI) Thomas 12/02/1970 48 ANS </li><ol><li>M MARCI-RIVA (SIDIBE) Baptiste 07/02/1997 21 ANS </li><ol><li>M MARCI-JIMEZ Mathis 07/01/2018 5 MOIS </li></ol><li>Mme MARCI-RIVA (ALASSANE) Thérèse 30/03/1999 19 ANS </li></ol></ol>

Ce qui est parfait pour un site en production...

Mais comme vous êtes joueur, vous aimeriez bien savoir s'il est correct.
Alors voici la même fonction en mode dev, qui vous retournera le HTML indenté comme il faut, pour vérifier que tout est parfait.

Code PHP :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
function fetch_children_list_dev(PDO $db,$parent_id=0,$ol='',$tabul=-1){
  $sql='select id,
  if(genre="F","Mme","M") as civ,
  if(nom_usage is NULL,nom_naissance,concat(nom_usage," (",nom_naissance,")")) as nom,
  prenom,
  date_format(naissance,"%d/%m/%Y") as naissance_fr,
  case
    when deces is NULL AND  TIMESTAMPDIFF(YEAR,naissance,CURRENT_DATE)>=2 then concat(TIMESTAMPDIFF(YEAR,naissance,CURRENT_DATE)," ANS")
    when deces is not NULL then NULL
    else concat( TIMESTAMPDIFF(MONTH,naissance,CURRENT_DATE)," MOIS")
  end as age,
  case
    when deces is NOT NULL AND TIMESTAMPDIFF(YEAR,naissance,deces)>=2 then concat("DCD LE ", date_format(deces,"%d/%m/%Y")," A ",TIMESTAMPDIFF(YEAR,naissance,deces)," ANS")
    when deces is NULL then NULL
    else concat("DCD LE ", date_format(deces,"%d/%m/%Y")," A ", TIMESTAMPDIFF(MONTH,naissance,deces)," MOIS")
  end as deces_age
  from geneal
  where parent_1='.$parent_id.' OR parent_2='.$parent_id.' 
  order by naissance, prenom';
  $stmt = $db->query($sql);
  $data=$stmt->fetchAll();
  if(isset($data[0])){//si enfant(s) seulement
    $tabul++;
    $indentation='';
    for($i=0;$i<$tabul;$i++){
      $indentation .="\t";
    }
    $ol .= $indentation."<ol>\n";
    $indentation_li =$indentation."\t";
    foreach($data as $row){
       $ol .=$indentation_li.'<li>'. $row['civ'].' '.$row['nom'].' '.$row['prenom'].' '.$row['naissance_fr'].' '.$row['age'].' '.$row['deces_age'].'</li>'."\n";
      $ol = fetch_children_list_dev($db,$row['id'], $ol,$tabul);//la fonction s'appelle elle-même à l'intérieur de son premier appel
    }
    $ol .= $indentation."</ol>\n";
  }
  return $ol;
}
$eldest_id=1;
$params=array(
  PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
  PDO::ATTR_EMULATE_PREPARES => false
);
$pdo = new PDO('mysql:host=localhost;port=3306;dbname=entrepot;charset=utf8','root', '', $params);
$liste_recursive=fetch_children_list_dev($pdo,$eldest_id);
$pdo=NULL;
echo $liste_recursive;

générera

Code HTML :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
 
<ol>
	<li>M MARCI François 17/05/1965 53 ANS </li>
	<ol>
		<li>M MARCI Lucas 12/09/1985 32 ANS </li>
		<li>Mme REVEL (MARCI) Johanna 25/08/1989 28 ANS </li>
	</ol>
	<li>Mme MARCI Anne 12/02/1967  DCD LE 12/04/1968 A 14 MOIS</li>
	<li>M MARCI-RIVA (MARCI) Thomas 12/02/1970 48 ANS </li>
	<ol>
		<li>M MARCI-RIVA (SIDIBE) Baptiste 07/02/1997 21 ANS </li>
		<ol>
			<li>M MARCI-JIMEZ Mathis 07/01/2018 5 MOIS </li>
		</ol>
		<li>Mme MARCI-RIVA (ALASSANE) Thérèse 30/03/1999 19 ANS </li>
	</ol>
</ol>