动态多维数组覆盖它的自我 [英] Dynamic multidimensional array overrides it self
问题描述
编辑：
我一直在尝试了一段时间现在看来，它可能是SQL只给我每次调用一个行，我可以优化它，或者以另一种方式写？
试图创建/从数据库得到一个动态的多维数组/菜单，但它不会DB打印出我的所有行。例如：当打印出两行我只得到一个与我现在拿到了SQL，使用嵌套阵列时， display_children 我也只得到为每个阵列一行。我试图解释这一phenomen与下面的JSON的例子。
我试图通过函数，所以每次调用添加它自己的级别的值在函数中每个变量发送一个变量，但没有管理的工作。
这是我的结果的一个例子（使它更具可读性）：
[
{
CATEGORY_ID：1，
头衔：第一考，
类别：
{
CATEGORY_ID：2，
头衔：第二类
类别：
{
CATEGORY_ID：3，
头衔：3
类别：
}
]
}
]
}
]
这是我想要的/预期：
[
{
CATEGORY_ID：1，
头衔：第一考，
类别：
{
CATEGORY_ID：2，
头衔：第二类
类别：
{
CATEGORY_ID：3，
头衔：3
类别：
}
]
}，
{
CATEGORY_ID：4，
头衔：四分类，
类别：
}
]
}
]
数据库表：
下面是code：
＆LT; PHP
/ *数据库信息等上面这一行* /
功能display_children（$父）{ 如果（！空（$父））{ 全球$胸径;
$查询=选择类别。*
GROUP_CONCAT（category_hierarchy.category_id分离器'，'）AS小类
从类别
LEFT JOIN category_hierarchy
ON category_hierarchy.category_parent_id = category.category_id
WHERE category.type = 2及; $ queryArr =阵列（）;
$ queryArrValue =阵列（）; $ =父母爆炸（''，$父）; 的foreach（$家长$值）{
$ queryArr [] =（category.category_id =？）;
$ queryArrValue [] = $价值;
} $ queryArr =破灭（'或'，$ queryArr）; 。$查询=（。$ queryArr。）;
$查询=ORDER BY category.sort_order ASC。 // prepare。
$语句= $ dbh-＆GT; prepare（$查询）; // 执行。
$ stmt-＆GT;执行（$ queryArrValue）; //读取结果。
$类= $ stmt-＆GT;使用fetchall（）; $ returnArr =阵列（）; 的foreach（$类别，$类）{ 如果（！空（$类['子']））{
$ parent_arr =阵列（display_children（$类['子']））;
}其他{
$ parent_arr ='';
} $ returnArr [] =数组（
CATEGORY_ID'=＆GT; $类['CATEGORY_ID']，
'标题'=＆GT; $类['标题']，
'塞'=＆GT; $类['塞']，
'URL'=＆GT; $类['URL']，
'型'=＆GT; $类别[型]
SORT_ORDER'=＆GT; $类['SORT_ORDER']，
'类'=＆GT; $ parent_arr
）;
} 返回$ returnArr;
}}$ ARR =阵列（）;//查询。 $查询=选择类别。*
GROUP_CONCAT（category_hierarchy.category_id分离器'，'）AS小类
从类别
LEFT JOIN category_hierarchy
ON category_hierarchy.category_parent_id = category.category_id
WHERE category.type = 1
ORDER BY category.sort_order ASC
// prepare。
$语句= $ dbh-＆GT; prepare（$查询）; // 执行。
$ stmt-＆GT;执行（）; //读取结果。
$类= $ stmt-＆GT;使用fetchall（）; $ countedRows =计数（$类）; 的foreach（$类别，$类）{
$ parent_arr ='';
如果（！空（$类['子']））{
$ parent_arr =阵列（display_children（$类['子']））;
} $常用3 [] =数组（
CATEGORY_ID'=＆GT; $类['CATEGORY_ID']，
'标题'=＆GT; $类['标题']，
'塞'=＆GT; $类['塞']，
'URL'=＆GT; $类['URL']，
'型'=＆GT; $类别[型]
SORT_ORDER'=＆GT; $类['SORT_ORDER']，
'类'=＆GT; $ parent_arr
）;
}/ *输出JSON * /
标题（内容类型：应用程序/ JSON'）;
回声json_en code（$ ARR）;
死（）;
？＆GT;
这是SQL，因为我使用 GROUP_CONCAT 我不得不添加 GROUP BY
SELECT
类别。*，
GROUP_CONCAT（category_hierarchy.category_id分离器'，'）AS小类从类别
LEFT JOIN category_hierarchy ON category.category_id = category_hierarchy.category_parent_id
WHERE category.type =1GROUP BY category.category_id
ORDER BY category.sort_order ASC
<一个href=\"http://stackoverflow.com/questions/29163476/left-join-only-fetches-one-row/29163524#29163524\">See描述这里回答
EDIT: I've been experimenting for a while now and it seems that it could be the SQL that only give me one row per call, could I optimize it or write it in another way?
Trying to create/get a dynamic multidimensional array/menu from DB, but it wont print out all my rows in DB. Example: When printing out two rows I only get one with the SQL I got now, when using nested arrays display_children I also only get one row for each array. I try to explain this phenomen with the JSON examples below.
I've tried to send a variable through the function so each call adds it own "level" value to each variable inside the function, but did not manage that to work.
This is an example of my result(made it more readable):
[
{
"category_id": "1",
"title": "First test",
"categories": [
{
"category_id": "2",
"title": "Second category",
"categories": [
{
"category_id": "3",
"title": "3",
"categories": ""
}
]
}
]
}
]
This is what I want/expect:
[
{
"category_id": "1",
"title": "First test",
"categories": [
{
"category_id": "2",
"title": "Second category",
"categories": [
{
"category_id": "3",
"title": "3",
"categories": ""
}
]
},
{
"category_id": "4",
"title": "Fourth category",
"categories": ""
}
]
}
]
DB tables:
Here is the code:
<?php
/* DB info and so on above this line */
function display_children($parent) {
if(!empty($parent)) {
global $dbh;
$query = "SELECT category.*,
GROUP_CONCAT(category_hierarchy.category_id SEPARATOR ',') AS subcategories
FROM category
LEFT JOIN category_hierarchy
ON category_hierarchy.category_parent_id = category.category_id
WHERE category.type = 2 AND ";
$queryArr = array();
$queryArrValue = array();
$parents = explode(',', $parent);
foreach($parents as $value) {
$queryArr[] = "(category.category_id = ?)";
$queryArrValue[] = $value;
}
$queryArr = implode(' OR ', $queryArr);
$query .= "(".$queryArr.")";
$query .= " ORDER BY category.sort_order ASC";
// Prepare.
$stmt = $dbh->prepare($query);
// Execute.
$stmt->execute($queryArrValue);
// Fetch results.
$categories = $stmt->fetchAll();
$returnArr = array();
foreach($categories as $category) {
if(!empty($category['subcategories'])) {
$parent_arr = array(display_children($category['subcategories']));
} else {
$parent_arr = '';
}
$returnArr[] = array(
'category_id' => $category['category_id'],
'title' => $category['title'],
'slug' => $category['slug'],
'url' => $category['url'],
'type' => $category['type'],
'sort_order' => $category['sort_order'],
'categories' => $parent_arr
);
}
return $returnArr;
}
}
$arr = array();
// Query.
$query = "SELECT category.*,
GROUP_CONCAT(category_hierarchy.category_id SEPARATOR ',') AS subcategories
FROM category
LEFT JOIN category_hierarchy
ON category_hierarchy.category_parent_id = category.category_id
WHERE category.type = 1
ORDER BY category.sort_order ASC
";
// Prepare.
$stmt = $dbh->prepare($query);
// Execute.
$stmt->execute();
// Fetch results.
$categories = $stmt->fetchAll();
$countedRows = count($categories);
foreach($categories as $category) {
$parent_arr = '';
if(!empty($category['subcategories'])) {
$parent_arr = array(display_children($category['subcategories']));
}
$arr[] = array(
'category_id' => $category['category_id'],
'title' => $category['title'],
'slug' => $category['slug'],
'url' => $category['url'],
'type' => $category['type'],
'sort_order' => $category['sort_order'],
'categories' => $parent_arr
);
}
/* Output JSON */
header('Content-type: application/json');
echo json_encode($arr);
die();
?>
It was the SQL, as I'm using GROUP_CONCAT I had to add GROUP BY
SELECT
category.*,
GROUP_CONCAT(category_hierarchy.category_id SEPARATOR ',') AS subcategories
FROM category
LEFT JOIN category_hierarchy ON category.category_id = category_hierarchy.category_parent_id
WHERE category.type = '1'
GROUP BY category.category_id
ORDER BY category.sort_order ASC
See description to answer here
这篇关于动态多维数组覆盖它的自我的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
