I have 2 tables tl_docs_categories which are the parent table for tl_docs. Structure of tables is:
我有2个表tl_docs_categories,它们是tl_docs的父表。表的结构是:
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)
]
So I have something like this in my database:
所以我的数据库中有这样的东西:
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.
我想要的是当我选择红色类别时返回所有结果,我想返回所有文档和子类别名称,在我的视图中,我使用选择表单以红色返回所选父类别的ID。
Here is my php function :
这是我的php函数:
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()->query($sqlDoc);
if ($objDocs->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()->query($sqlChild);
return $objDocs;
}
}
else
{
return $objDocs;
}
}
}
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.
我有点迷失了,我没有解决方案来递归地执行它并返回每个类别,子类别,子子类别等的数据。
Thank you for your help in advance.
提前谢谢你的帮助。
1 个解决方案
#1
0
I'm not sure about the syntax, but the idea is to call checkSQL
on each level, if you want to return all results, then you have to adapt the $objDocs
var to be an array.
我不确定语法,但想法是在每个级别调用checkSQL,如果要返回所有结果,则必须使$ objDocs var适应为数组。
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()->query($sqlDoc);
if ($objDocs->numRows > 0) {
foreach ($objDocs->fetchColumn() as $row) {
$objDocs = $this->checkSQL($row->id);
}
}
return $objDocs;
}
return null;
}
#1
0
I'm not sure about the syntax, but the idea is to call checkSQL
on each level, if you want to return all results, then you have to adapt the $objDocs
var to be an array.
我不确定语法,但想法是在每个级别调用checkSQL,如果要返回所有结果,则必须使$ objDocs var适应为数组。
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()->query($sqlDoc);
if ($objDocs->numRows > 0) {
foreach ($objDocs->fetchColumn() as $row) {
$objDocs = $this->checkSQL($row->id);
}
}
return $objDocs;
}
return null;
}