0

I have one table in MariaDB with persons:

enter image description here

I have one PHP to show a tree of a person of interest. It shows parents upwards, and children downwards, both recursivley.

My plan was to add a PHP to show common ancestor. But I don't know what my approach should be. I found some SQL queries to find common ancestor (didn't get it to work though), but how would I present the data?

My plan was to try to use my recursive functions, more precisley the children function.

So it would be something like:

<div class="tree mx-auto d-flex flex-nowrap">
        <ul>
            <li>Common ancestor
                <ul>
                    <li>
                        Child 1
                        <ul>
                            <li>Child 11</li>
                            <li>Child 12
                                <ul>
                                    <li>Child 121</li>
                                    <li>Relative 1</li>
                                </ul>
                            </li>
                        </ul>
                    </li>
                    <li>Child 2
                        <ul>
                            <li>
                                Child 21
                                <ul>
                                    <li>
                                        Child 211
                                        <ul>
                                            <li>Relative 2</li>
                                        </ul>
                                    </li>
                                    <li>Child 212</li>
                                    <li>Child 213</li>
                                </ul>
                            </li>
                            <li>Child 22</li>
                        </ul>
                    </li>
                </ul>
            </li>
        </ul>
    </div>

Which would give: enter image description here

Some thoughts. If I find a common ancestor when two IDs are inputed. How would I direct or guide my recursive children function towards the two relatives? How would I tell the recursive function on what child to keep digging?

Or should the SQL query return the entire trace from relative to common ancestor? Should I have three queries?

  1. Input ID#1, ID#2, return ID of common ancestor (CA)
  2. Input ID#1, ID#CA, return trace from bottom to top, branch 1
  3. Input ID#2, ID#CA,return trace from bottom to top, branch 2

Then build the list/tree with PHP based on the two branches? Or could I do this with one query? Perhaps this overall is a bad approach?

EDIT: the common ancestor will most like always be two persons, the mom and dad. How could I handle that? Perhaps place mom and dad in a single <li></li> and place children under?

As I said, I need some help with my concept or approach to this. The SQL/PHP I probably could manage. I think (:

Robbas
  • 39
  • 1
  • 9
  • 1
    Your description says "concept" which indicates to me that you want the general pattern. The data structure is Tree which has a starting point for you at: https://www.sitepoint.com/data-structures-2/. You could also review this SO post - https://stackoverflow.com/questions/3304670/family-tree-data-structure – hppycoder Mar 11 '21 at 20:29
  • Thank you.. Seems like I should start from scratch -_- already have 70 ppl in the databse.. well well – Robbas Mar 11 '21 at 21:08

0 Answers0