Get the ancestral data from database dynamically

I would like to get the ancestral or child data when I pass the value in a function.

This is my data from MySQL table.

If I pass the value of Document2 in my function i want to get the ancestral data, for example, if I pass Document2 it will get the Book2 then the Book2 will get the Document1 and Document1 will get the Book1 and so on, then same with the Profile1 it will get the Document0 dynamically.

Here’s my code.

$binded  = array('Document2');
$sources = [];
foreach ($binded as $document) {
    $check = $this->db->where('binded',$document)
                      ->get('binded_document');

    $results = $check->result();
    foreach($results as $key => $result){
         array_push($sources, $result->source);

         $ancestral = $this->db->where('binded',$result->source)
                               ->get('binded_document');

         $ancestrals = $ancestral->result();
         foreach($ancestrals as $k => $r){
              array_push($sources, $r->source);
         }
    }
}

The problem with my code is it will not dynamically get ancestral data if the passed value in the function has more than 2 ancestral.

Answer

DBFIDDLE

• I did choose to separate the recursive parts with a -.

WITH RECURSIVE cte AS (
   SELECT 
      source as s, 
      CAST(Binded AS CHAR(1024)) as b,
      1 as c
   FROM table1 WHERE Binded='Document2'
   UNION ALL
   SELECT table1.source, CONCAT(cte.s,'-',cte.b), c+1
   FROM cte
   LEFT JOIN table1 ON table1.Binded = cte.s
   WHERE c<10 AND NOT cte.s IS NULL
)
select b
from cte 
where cte.s is null and NOT b is null
group by b
;

output:

b
Document0-Profile1-Document2
Book1-Document1-Book2-Document2