递归循环创建一个家谱？ （PHP / MySQL的/ HTML）

Question

Currently creating my own breeding website, but I am struggling with retrieving, processing and showing the pedigree of a given dog. I can do it if I pretty much hard-code it but not dynamically by retrieving parents (and parents of parents, etc...) until parents aren't found anymore.

MYSQL is basic:

tbl_dogs (table) - dog_id - dog_name - dog_sex - mother_id - father_id

The idea is to retrieve parents of one dogs, then parents of each dog in the tree, until mother_id/father_id aren't specified.

I am completely lost, so far I have been doing it the hard-coded way:

    $mother["id"] = get_post_meta(get_the_ID(), "_mother_id", true);
    $mother["name"] = get_animal_name($mother["id"]);
    $mother["permalink"] = get_the_permalink($mother["id"]);

        $mother_mother["id"] = get_mother_id($mother["id"]);
        $mother_mother["name"] = get_animal_name($mother_mother["id"]);
        $mother_mother["permalink"] = get_the_permalink($mother_mother["id"]);

        $mother_father["id"] = get_father_id($mother["id"]);
        $mother_father["name"] = get_animal_name($mother_father["id"]);
        $mother_father["permalink"] = get_the_permalink($mother_father["id"]);

    $father["id"] = get_post_meta(get_the_ID(), "_father_id", true);
    $father["name"] = get_animal_name($father["id"]);
    $father["permalink"] = get_the_permalink($father["id"]);

        $father_mother["id"] = get_mother_id($father["id"]);
        $father_mother["name"] = get_animal_name($father_mother["id"]);
        $father_mother["permalink"] = get_the_permalink($father_mother["id"]);

        $father_father["id"] = get_father_id($father["id"]);
        $father_father["name"] = get_animal_name($father_father["id"]);
        $father_father["permalink"] = get_the_permalink($father_father["id"]);

And I then would just use my family tree HTML5 structure to display this data.

<div class="pedigree-tree">
  <ul>
    <li><span><a href="<?php echo $mother["permalink"] ?>"><?php echo $mother["name"]; ?></a></span>
      <ul>
        <li><span><a href="<?php echo $mother_mother["permalink"] ?>"><?php echo $mother_mother["name"] ?></a></span>
          <ul>
            <li>
                <span>Grand-Grandmother</span>
                <ul>
                    <li><span><a href="<?php echo $mother_mother["permalink"] ?>"><?php echo $mother_mother["name"] ?></a></span></li>
                    <li><span><a href="<?php echo $mother_mother["permalink"] ?>"><?php echo $mother_mother["name"] ?></a></span></li>
                </ul>
            </li>
            <li><span>Grand-Grandfather</span></li>
          </ul>
        </li>
        <li><span><a href="<?php echo $mother_father["permalink"] ?>"><?php echo $mother_father["name"] ?></a></span>
          <ul>
            <li><span>Entry-1-2-1</span></li>
            <li><span>Entry-1-2-1</span></li>
          </ul>
        </li>
      </ul>
    </li>
    <li><span><a href="<?php echo $father["permalink"] ?>"><?php echo $father["name"]; ?></a></span>
      <ul>
        <li><span><a href="<?php echo $father_mother["permalink"] ?>"><?php echo $father_mother["name"] ?></a></span>
          <ul>
            <li><span>Entry-1-1-1</span></li>
            <li><span>Entry-1-1-1</span></li>
          </ul>
        </li>
        <li><span><a href="<?php echo $father_father["permalink"] ?>"><?php echo $father_father["name"] ?></a></span>
          <ul>
            <li><span>Entry-1-2-1</span></li>
            <li><span>Entry-1-2-1</span></li>
          </ul>
        </li>
      </ul>
    </li>
  </ul>
</div>

But this solution, although working, is not at all suited to my needs since it is not flexible at all. I want to build a function that will go through as many generations as I will ask for (if they exist) and put that into an Array that I will then output through nested HTML lists as done above.

This "logical loop" to retrieve all parents is driving me insane, any help would be appreciated.. Thanks a million!

Answer 1

It's not that big deal. So basically you check if a dog has a mother and/or a father and execute the function again for each dog.

$stmnt = $pdo->prepare("
    SELECT dog_id, dog_name, dog_sex, mother_id, father_id
    FROM tbl_dogs
    WHERE dog_id = ?
");

fetchDogRecursive($yourDogId);

function fetchDogRecursive($dogId)
{
    $stmnt->execute(array($dogId));

    $dogData = $stmnt->fetchAll(PDO::FETCH_ASSOC)[0];

    $dog = array(
        'id' => $dogData['dog_id'],
        'name' => $dogData['dog_name'],
        'mother' => null,
        'father' => null
    );

    if($dogData['mother_id'] !== null) {
        $dog['mother'] = fetchDogRecursive($dogData['mother_id']);
    }

    if($dogData['father_id'] !== null) {
        $dog['father'] = fetchDogRecursive($dogData['father_id']);
    }

    return $dog;
}