问题描述
首先，我对此发布了另一个问题，但是没有得到答案，因此我需要发布一个新问题.
First of all, I posted another question about this, but didn't get answer so I needed to post a new one.
我有一个按层次列出类别的函数，我通过foreach循环创建了另一个函数(在同一函数上)，并且也将其用于下拉列表中，因此无需使用现有函数创建另一个查询多次.
I have a function which is listing categories hierarchically, I created another function (on same function) with foreach loop in, and using it into dropdowns too, so don't need to create another query, using my existing function multiple times.
我想使用相同的查询来列出所选类别下的子类别.
示例:如果单击了category.php?id=100，那么我要在登录页面的目录100类别下列出子类别.
Example: if category.php?id=100 clicked, then I want to list subcategories under catgeory 100 hierarchicaly in landing page.
这是我的职能:
$allCategories = array();
$categoryMulti = array(
'categories' => array(),
'parent_cats' => array()
);
while ($row = $stmt->fetch()) {
//I created another array to get subcats didnt work
//$categories['categories'][1]
$categoryMulti['categories'][$row['cat_id']] = $row;
$categoryMulti['parent_cats'][$row['parent_id']][] = $row['cat_id'];
$allCategories[] = $row;
}
function listCategoryTree($parent, $category)
{
$html = "";
if (isset($category['parent_cats'][$parent])) {
$html .= "<ul>\n";
foreach
($category['parent_cats'][$parent] as $cat_id) {
if (!isset($category['parent_cats'][$cat_id])) {
$html .= "<li>" . $category['categories'][$cat_id]['cat_name'] . "</li>";
} else {
$html .= "<li>" . $category['categories'][$cat_id]['cat_name'];
$html .= listCategoryTree($cat_id, $category);
$html .= "</li>";
}
}
$html .= "</ul> \n";
}
return $html;
}
用法:echo listCategoryTree(0, $categoryMulti);
这是我在下拉菜单中使用的示例，仅显示一个示例:
And here is how I use in dropdown showing just for an example it works fine:
function selectCategories($categories)
{
foreach ($categories as $category) {
echo '<option value="' . $category['cat_id'] . '">' . $category['cat_name'] . '</option>';
}
}
selectCategories($allCategories);
这是我尝试使其与选定的类别方法一起工作的示例之一，但不幸的是，它没有工作.
And this is one of the examples that i tried to make it work with the selected category method, but unfortunately, it didn't worked.
我在函数中创建了一个新数组:
I created a new array in my function:
$subCategories = array();
$subCategories[] = $row;
并将其作为如下函数调用，但没有用.
And call it as a function like following but didn't work.
function cats($categories)
{
foreach($categories as $category) {
echo '<ul>';
echo '<li>' . $category['cat_name'] . '</li>';
echo '</ul>';
}
}
echo cats(100, $subCategories);
推荐答案
有两种解决方案.首先，我将使用以下数据(categories表)作为示例.
There are a couple of solutions. First of all, I'll use the following data (categories table) as an example.
+----+--------------------------+-----------+
| id | name | parent_id |
+----+--------------------------+-----------+
| 1 | Electronics | NULL |
| 2 | Apparel & Clothing | NULL |
| 3 | Phones & Accessories | 1 |
| 4 | Computer & Office | 1 |
| 5 | Men's Clothing | 2 |
| 6 | Women's Clothing | 2 |
| 7 | Cell Phones | 3 |
| 8 | Cell Phone Accessories | 3 |
| 9 | Phone Parts | 3 |
| 10 | Computers & Accessories | 4 |
| 11 | Tablets & Accessories | 4 |
| 12 | Computer Peripherals | 4 |
| 13 | Computer Components | 4 |
| 14 | Office Electronics | 4 |
+----+--------------------------+-----------+
您可以使用 WITH(公用表表达式)子句(需要MySQL 8.0):
You can easily fetch either all categories or subcategories of a category in a single query using WITH (Common Table Expressions) clause (requires MySQL 8.0):
// Database connection
$options = [
PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
PDO::ATTR_EMULATE_PREPARES => false,
PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
];
$pdo = new PDO('mysql:host=localhost;dbname=<DATABASE_NAME>', '<USERNAME>', '<PASSWORD>', $options);
function getCategories(PDO $db, $parentId = null)
{
$sql = $parentId ? 'WITH RECURSIVE cte (id, name, parent_id) AS (SELECT id, name, parent_id FROM categories WHERE parent_id = ? UNION ALL SELECT c.id, c.name, c.parent_id FROM categories c INNER JOIN cte ON c.parent_id = cte.id) SELECT * FROM cte' : 'SELECT * FROM categories';
$stmt = $db->prepare($sql);
$stmt->execute($parentId ? [$parentId] : null);
return $stmt->fetchAll();
}
如果您使用的是MySQL 5.7，请按如下所示更改该功能:
If you're using MySQL 5.7, change that function like so:
function getCategories(PDO $db, $parentId = null)
{
$sql = $parentId ? 'SELECT id, name, parent_id FROM (SELECT * FROM categories ORDER BY parent_id, id) c, (select @pv := ?) initialisation WHERE find_in_set(parent_id, @pv) AND LENGTH(@pv := concat(@pv, ",", id))' : 'SELECT * FROM categories';
$stmt = $db->prepare($sql);
$stmt->execute($parentId ? [$parentId] : null);
return $stmt->fetchAll();
}
要获取数据库中的所有类别:
To get all categories in your database:
$allCategories = getCategories($pdo);
输出:
+----+--------------------------+-----------+
| id | name | parent_id |
+----+--------------------------+-----------+
| 1 | Electronics | NULL |
| 2 | Apparel & Clothing | NULL |
| 3 | Phones & Accessories | 1 |
| 4 | Computer & Office | 1 |
| 5 | Men's Clothing | 2 |
| 6 | Women's Clothing | 2 |
| 7 | Cell Phones | 3 |
| 8 | Cell Phone Accessories | 3 |
| 9 | Phone Parts | 3 |
| 10 | Computers & Accessories | 4 |
| 11 | Tablets & Accessories | 4 |
| 12 | Computer Peripherals | 4 |
| 13 | Computer Components | 4 |
| 14 | Office Electronics | 4 |
+----+--------------------------+-----------+
要获取类别的子类别:
$subCategories = getCategories($pdo, 1); // 1 is parent_id
输出:
+----+--------------------------+-----------+
| id | name | parent_id |
+----+--------------------------+-----------+
| 3 | Phones & Accessories | 1 |
| 4 | Computer & Office | 1 |
| 7 | Cell Phones | 3 |
| 8 | Cell Phone Accessories | 3 |
| 9 | Phone Parts | 3 |
| 10 | Computers & Accessories | 4 |
| 11 | Tablets & Accessories | 4 |
| 12 | Computer Peripherals | 4 |
| 13 | Computer Components | 4 |
| 14 | Office Electronics | 4 |
+----+--------------------------+-----------+
如果要输出HTML，可以遍历$allCategories/$subCategories(根据您的示例):
If you want an HTML output, you can loop through $allCategories / $subCategories (based on your example):
function prepareCategories(array $categories)
{
$result = [
'all_categories' => [],
'parent_categories' => []
];
foreach ($categories as $category) {
$result['all_categories'][$category['id']] = $category;
$result['parent_categories'][$category['parent_id']][] = $category['id'];
}
return $result;
}
function buildCategories($categories, $parentId = null)
{
if (!isset($categories['parent_categories'][$parentId])) {
return '';
}
$html = '<ul>';
foreach ($categories['parent_categories'][$parentId] as $cat_id) {
if (isset($categories['parent_categories'][$cat_id])) {
$html .= "<li><a href='#'>{$categories['all_categories'][$cat_id]['name']}</a>";
$html .= buildCategories($categories, $cat_id);
$html .= '</li>';
} else {
$html .= "<li><a href='#'>{$categories['all_categories'][$cat_id]['name']}</a></li>";
}
}
$html .= '</ul>';
return $html;
}
echo buildCategories(prepareCategories($allCategories));
输出:
echo buildCategories(prepareCategories($subCategories), 1);
输出:
我们将在表中添加其他列left和right，并在其中添加数字以标识属于父级的组. (请注意，我们不会使用parent_id列.)
We'll add additional columns left and right to our table and put numbers in it which will identify the groups belonging to the parent. (Note that we won't be using parent_id column.)
+----+--------------------------+--------------------------+
| id | name | parent_id | left | right |
+----+--------------------------+--------------------------+
| 1 | Electronics | NULL | 1 | 22 |
| 2 | Apparel & Clothing | NULL | 23 | 28 |
| 3 | Phones & Accessories | 1 | 2 | 9 |
| 4 | Computer & Office | 1 | 10 | 21 |
| 5 | Men's Clothing | 2 | 24 | 25 |
| 6 | Women's Clothing | 2 | 26 | 27 |
| 7 | Cell Phones | 3 | 3 | 4 |
| 8 | Cell Phone Accessories | 3 | 5 | 6 |
| 9 | Phone Parts | 3 | 7 | 8 |
| 10 | Computers & Accessories | 4 | 11 | 12 |
| 11 | Tablets & Accessories | 4 | 13 | 14 |
| 12 | Computer Peripherals | 4 | 15 | 16 |
| 13 | Computer Components | 4 | 17 | 18 |
| 14 | Office Electronics | 4 | 19 | 20 |
+----+--------------------------+--------------------------+
现在，我们需要更改功能:
Now, we need to change our function:
function getCategories(PDO $db, $parentId = null)
{
$sql = $parentId ? 'SELECT children.* FROM categories parent INNER JOIN categories children ON parent.left < children.left AND parent.right > children.left WHERE parent.id = ?' : 'SELECT * FROM categories';
$stmt = $db->prepare($sql);
$stmt->execute($parentId ? [$parentId] : null);
return $stmt->fetchAll();
}
要获取数据库中的所有类别:
To get all categories in your database:
$allCategories = getCategories($pdo);
输出:
+----+--------------------------+--------------------------+
| id | name | parent_id | left | right |
+----+--------------------------+--------------------------+
| 1 | Electronics | NULL | 1 | 22 |
| 2 | Apparel & Clothing | NULL | 23 | 28 |
| 3 | Phones & Accessories | 1 | 2 | 9 |
| 4 | Computer & Office | 1 | 10 | 21 |
| 5 | Men's Clothing | 2 | 24 | 25 |
| 6 | Women's Clothing | 2 | 26 | 27 |
| 7 | Cell Phones | 3 | 3 | 4 |
| 8 | Cell Phone Accessories | 3 | 5 | 6 |
| 9 | Phone Parts | 3 | 7 | 8 |
| 10 | Computers & Accessories | 4 | 11 | 12 |
| 11 | Tablets & Accessories | 4 | 13 | 14 |
| 12 | Computer Peripherals | 4 | 15 | 16 |
| 13 | Computer Components | 4 | 17 | 18 |
| 14 | Office Electronics | 4 | 19 | 20 |
+----+--------------------------+--------------------------+
要获取类别的子类别:
$subCategories = getCategories($pdo, 1); // 1 is parent_id
输出:
+----+--------------------------+--------------------------+
| id | name | parent_id | left | right |
+----+--------------------------+--------------------------+
| 3 | Phones & Accessories | 1 | 2 | 9 |
| 4 | Computer & Office | 1 | 10 | 21 |
| 7 | Cell Phones | 3 | 3 | 4 |
| 8 | Cell Phone Accessories | 3 | 5 | 6 |
| 9 | Phone Parts | 3 | 7 | 8 |
| 10 | Computers & Accessories | 4 | 11 | 12 |
| 11 | Tablets & Accessories | 4 | 13 | 14 |
| 12 | Computer Peripherals | 4 | 15 | 16 |
| 13 | Computer Components | 4 | 17 | 18 |
| 14 | Office Electronics | 4 | 19 | 20 |
+----+--------------------------+--------------------------+
您可以如解决方案1 中所示呈现HTML. 了解更多有关更新和插入新内容的信息嵌套集模型中的数据.
You can render HTML as shown in Solution 1. Read more about updating and inserting new data in nested set model.
来源和读取:
- Managing hierarchical data in MySQL - nested set
- Managing Hierarchical Data in MySQL
- Models for hierarchical data
- Managing Hierarchical Data in MySQL Using the Adjacency List Model
- Adjacency list vs. nested sets: MySQL
- One more Nested Intervals vs. Adjacency List comparison
这篇关于如何使用PHP中的嵌套函数从所选类别中选择子类别?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！