I have a somewhat complex recursive postgresql query that pulls in something (I have simplified it for the purposes of this question) like this:
id depth path has_children
1 1 1 true
2 2 1.2 true
3 3 1.2.3 true
4 4 1.2.3.4 true
5 5 1.2.3.4.5 false
6 1 6 true
7 2 6.7 true
8 3 6.7.8 false
9 1 9 false
10 1 10 true
11 2 10.11 false
This is the result as fetched (for those wondering why I parsed some arrays as objects it is because the rows are fetched as objects and I am just duplicating the result):
$tree = array
(
(object) array
(
"id" => 1,
"depth" => 1,
"path" => "1",
"has_children" => true
),
(object) array
(
"id" => 2,
"depth" => 2,
"path" => "1.2",
"has_children" => true
),
(object) array
(
"id" => 3,
"depth" => 3,
"path" => "1.2.3",
"has_children" => true
),
(object) array
(
"id" => 4,
"depth" => 4,
"path" => "1.2.3.4",
"has_children" => true
),
(object) array
(
"id" => 5,
"depth" => 5,
"path" => "1.2.3.4.5",
"has_children" => false
),
(object) array
(
"id" => 6,
"depth" => 1,
"path" => "6",
"has_children" => true
),
(object) array
(
"id" => 7,
"depth" => 2,
"path" => "6.7",
"has_children" => true
),
(object) array
(
"id" => 8,
"depth" => 3,
"path" => "6.7.8",
"has_children" => false
),
(object) array
(
"id" => 9,
"depth" => 1,
"path" => "9",
"has_children" => false
),
(object) array
(
"id" => 10,
"depth" => 1,
"path" => "10",
"has_children" => true
),
(object) array
(
"id" => 11,
"depth" => 2,
"path" => "10.11",
"has_children" => false
)
);
I want to turn the result into this (with the given class names):
<div id="foo">
<div class="bar">
<div class="qux">
<p>1</p>
</div>
<div class="baz">
<div class="qux">
<p>1.2</p>
</div>
<div class="baz">
<div class="qux">
<p>1.2.3</p>
</div>
<div class="baz">
<div class="qux">
<p>1.2.3.4</p>
</div>
<div class="baz">
<div class="qux">
<p>1.2.3.4.5</p>
</div>
</div>
</div>
</div>
</div>
</div>
<div class="bar">
<div class="qux">
<p>6</p>
</div>
<div class="baz">
<div class="qux">
<p>6.7</p>
</div>
<div class="baz">
<div class="qux">
<p>6.7.8</p>
</div>
</div>
</div>
</div>
<div class="bar">
<div class="qux">
<p>9</p>
</div>
</div>
<div class="bar">
<div class="qux">
<p>10</p>
</div>
<div class="baz">
<div class="qux">
<p>10.11</p>
</div>
</div>
</div>
</div>
However, I have gone and confused myself after looking at too many examples involving ul and li. Tweaking such examples for my uses have failed as nested uls and lis are different than nested divs.
I'd like a clean solution that uses a foreach (preferably) or while loop (a recursive function is not necessary). I also do not wish to recreate the result as an multidimensional array.
Probably not the prettiest code, but it outputs exactly as you specified:
<div id="foo">
<?php
$openingElement = true;
$divsOpened = 0;
$indent = 1;
foreach ($tree as $row) {
if ($openingElement === true) {
print str_repeat(' ', $indent * 4) . '<div class="bar">' . PHP_EOL;
} else {
print str_repeat(' ', $indent * 4) . '<div class="baz">' . PHP_EOL;
}
$indent++;
$divsOpened++;
print str_repeat(' ', $indent * 4) . '<div class="qux">' . PHP_EOL;
$indent++;
print str_repeat(' ', $indent * 4) . '<p>' . $row->path . '</p>' . PHP_EOL;
$indent--;
print str_repeat(' ', $indent * 4) . '</div>' . PHP_EOL;
if ($row->has_children) {
$openingElement = false;
print PHP_EOL;
} else {
for ($i = $divsOpened; $i > 0; $i--) {
print str_repeat(' ', $i * 4) . '</div>' . PHP_EOL;
$indent--;
$divsOpened--;
}
$openingElement = true;
}
}
?>
</div>
You need to fetch the db data into an array which you can loop through and using both depth and has_children, pass the data to a new multi-dimensional array as:
array(
'1' => array(
'1.2' => array(
'1.2.3' => array(
'1.2.3.4' => array('1.2.3.4.5')
)
)
),
'6' => array(
'6.7' => array('6.7.8')
)
);
etc. Then you can loop it and use a foreach to echo each array as a div.
Imagine you have the fetched data from the example table you posted in the form array('row' => array('key' => 'value', 'etc' => 'more')). For each row, you will need to check the ['has_children'] value. If it evaluates as true, you should then get the depth as an integer and use a for loop such as for ($i=0; $i<$depth; $i++), creating an array of arrays as the one written above.
There's several ways to do this, the simplier is to create the arrays recursively, such as $array = '1.2.3.4.5' then $array = array('1.2.3.4' => $array), until you have a multi-dimensional array as the written above.
Having that array, it's as simple as:
function display($multiarray) {
foreach ($multiarray as $name=>$path) {
if (is_array($path)) {
// Check if it's the first.
$class = (strpos('.', $name)) ? 'baz' : 'bar';
echo "<div class='{$class}'>
\t";
echo "<div class='qux'>
\t<p>{$name}</p>
</div>
";
display($path);
echo "</div>";
} else {
echo "<div class='baz'>
\t";
echo "<div class='qux'>
\t<p>{$path}</p>
</div>";
echo "</div>";
}
}
}
Made it on the fly, but I hope you get the main idea.
Your data being flat makes it more difficult to write a recursive function. A recursive function works most naturally with nested data. It took a while, but I was able to create a partially recursive solution that works with your flat data.
<?php
$tree = array(array("id"=>1,"depth"=>1,"path"=>"1","has_children"=>true),array("id"=>2,"depth"=>2,"path"=>"1.2","has_children"=>true),array("id"=>3,"depth"=>3,"path"=>"1.2.3","has_children"=>true),array("id"=>4,"depth"=>4,"path"=>"1.2.3.4","has_children"=>true),array("id"=>5,"depth"=>5,"path"=>"1.2.3.4.5","has_children"=>false),array("id"=>6,"depth"=>1,"path"=>"6","has_children"=>true),array("id"=>7,"depth"=>2,"path"=>"6.7","has_children"=>true),array("id"=>8,"depth"=>3,"path"=>"6.7.8","has_children"=>false),array("id"=>9,"depth"=>1,"path"=>"9","has_children"=>false),array("id"=>10,"depth"=>1,"path"=>"10","has_children"=>true),array("id"=>11,"depth"=>2,"path"=>"10.11","has_children"=>false));
header('Content-Type: text/plain');
function print_tree(&$tree, $i, $top_level)
{
$indent = str_repeat(' ', $tree[$i]['depth']);
echo $indent."<div class=\"".($top_level ? 'bar' : 'baz')."\">
";
echo $indent." <div class=\"qux\">
";
echo $indent." <p>".$tree[$i]['path']."</p>
";
echo $indent." </div>
";
if($tree[$i]['has_children'])
{
print_tree($tree, $i+1, false);
}
echo $indent."</div>
";
}
echo "<div id=\"foo\">
";
$count = count($tree);
for($i = 0; $i < $count; ++$i)
{
if($tree[$i]['depth'] == 1)
{
print_tree($tree, $i, true);
}
}
echo "</div>
";
?>
This will output:
<div id="foo">
<div class="bar">
<div class="qux">
<p>1</p>
</div>
<div class="baz">
<div class="qux">
<p>1.2</p>
</div>
<div class="baz">
<div class="qux">
<p>1.2.3</p>
</div>
<div class="baz">
<div class="qux">
<p>1.2.3.4</p>
</div>
<div class="baz">
<div class="qux">
<p>1.2.3.4.5</p>
</div>
</div>
</div>
</div>
</div>
</div>
<div class="bar">
<div class="qux">
<p>6</p>
</div>
<div class="baz">
<div class="qux">
<p>6.7</p>
</div>
<div class="baz">
<div class="qux">
<p>6.7.8</p>
</div>
</div>
</div>
</div>
<div class="bar">
<div class="qux">
<p>9</p>
</div>
</div>
<div class="bar">
<div class="qux">
<p>10</p>
</div>
<div class="baz">
<div class="qux">
<p>10.11</p>
</div>
</div>
</div>
</div>
This shiny piece of pure SQL will generate your string exactly:
WITH x AS (
SELECT *
,(lag(depth) OVER (ORDER BY id) +1) - depth AS end_divs
,CASE WHEN depth = 1 THEN 'bar' ELSE 'baz' END AS class
,E'
' || repeat(' ', depth) AS i -- newline + indent
FROM tbl
ORDER BY id
)
SELECT '<div id="foo">'
|| string_agg(
CASE WHEN end_divs > 0 THEN
(SELECT string_agg(repeat (' ', n), E'</div>
')
FROM generate_series (end_divs,0,-1) n)
ELSE '' END
|| i || '<div class="' || class ||'">'
|| i || ' <div class="qux">'
|| i || ' <p>' || path || '</p>'
|| i || ' </div>'
, E'
' ORDER BY id
)
|| (SELECT E'
' || string_agg(repeat (' ', n-1), E'</div>
')
FROM generate_series((SELECT depth+1 FROM tbl ORDER BY id DESC LIMIT 1)
, 0 , -1) n)
FROM x;
It's not very easy to read, I'll leave documenting the features to you.