PHP - Get data from SQL database recursively
<p>I have 2 tables tl_docs_categories which are the parent table for tl_docs.
Structure of tables is:</p>

<pre><code>tl_docs_categories
[
   id,
   category_name,
   pid (give the posibility to create subcategories / pid = tl_docs_categories.id if have subcategory)
]
tl_docs
[
   id,
   doc_name,
   doc_url,
   pid(id for category)
]
</code></pre>

<p>So I have something like this in my database:</p>

<p><a href="https://i.stack.imgur.com/xIZNm.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/xIZNm.png" alt="enter image description here"></a></p>

<p>What I want is to return all result when I select category which is in red, I want to return all documents and subcategories names, in my view I use a select form to return the id of selected parent category in red.</p>

<p>Here is my php function :</p>

<pre><code>public function checkSQL($id)
{
    if(!empty($id))
    {
        /* SQL CHECK IF HAVE DOCS */
        $sqlDoc = "SELECT category_name, doc_name, doc_url FROM tl_docs_categories dc
                   LEFT JOIN tl_docs d ON dc.id = d.pid WHERE d.pid = " . $id;
        $objDocs = Database::getInstance()-&gt;query($sqlDoc);
        if ($objDocs-&gt;numRows == 0)
        {
            /* SQL CHECK IF HAVE CHILD */
            $sqlChild = "SELECT dc.id, category_name, doc_name, doc_url FROM tl_docs_categories dc
                         LEFT JOIN tl_docs d ON dc.id = d.pid WHERE dc.pid =" . $id;
            $objDocs = Database::getInstance()-&gt;query($sqlChild);
                return $objDocs;
            }
        }
        else
        {
            return $objDocs;
        }
    }
}
</code></pre>

<p>I'm a bit lost and I don't have a solution to do it recursively and return datas for each category, subcategory, subsubcategory and more then.</p>

<p>Thank you for your help in advance.</p>