[eluser]omerorhan[/eluser]
Because i didn't get the logic well.
I thought its better to store details of the tree in another table on a multiple language supported site.
or ,should I add a lang_id field to tree and filter it?
I modified tree2array() to make ul list.
Code: function tree2array($root = 1){
$node = $this->MPTtree->get_node($root);
if($node == false)
return false;
// query
$query = $this->db->query('SELECT node.id, node.lft, node.rgt , detail.title
FROM table1 as node,
table1 as parent ,
table1_details AS detail
where node.lft BETWEEN '.$node['lft'].' AND '.$node['rgt'].'
and node.id = detail.page_id
Group by node.id
ORDER BY node.lft');
$right = array();
$result = array();
$current =& $result;
$stack = array();
$stack[0] =& $result;
$lastlevel = 0;
foreach($query->result_array() as $row){
// go more shallow, if needed
if(count($right)){
while($right[count($right)-1] < $row['rgt']){
array_pop($right);
}
}
// Go one level deeper?
if(count($right) > $lastlevel){
end($current);
$current[key($current)]['children'] = array();
$stack[count($right)] =& $current[key($current)]['children'];
}
// the stack contains all parents, current and maybe next level
$current =& $stack[count($right)];
// add the data
$current[] = $row;
// go one level deeper with the index
$lastlevel = count($right);
$right[] = $row['rgt'];
}
return $result[0]['children'];
}
Now i had to make a select list for admin panel.
Code: $levels = $this->getLevels();
$data['tree'] = $this->cat_select($this->tree2array(),$levels);
function cat_select($array , $levels, $lft = 1){
if($lft == 1)
{
echo "<select name=\"where\">";
}
foreach($array as $data)
{
$indent = (str_repeat(" ", $this->getLevel($levels,$data['lft'])*2));
echo '<option value="'.$data['id'].'">'.$indent.$data['title'].'</option>';
if(isset($data['children'])){
echo $this->cat_select($data['children'], $levels , 0);
}
}
echo "</select>";
}
function getLevels()
{
$sql = "select node.id, node.lft, node.title, (COUNT(parent.title) -1) as depth
From deneme as node,
deneme as parent
Where node.lft BETWEEN parent.lft AND parent.rgt
GROUP BY node.id
Order BY node.lft
";
$query = $this->db->query($sql);
$result = $query->result_array();
return $result;
}
function getLevel($array, $lft)
{
foreach($array as $data)
{
if($data['lft'] == $lft)
{
$depth = $data['depth'];
}
}
if(isset($depth))
{
return $depth;
}
else
{
return;
}
}
What is wrong in cat_select() function?
It doesn't add options in select box after first leaf ends.
[eluser]omerorhan[/eluser]
Ok i've done it with sql.
Code: function index()
{
$data['tree'] = $this->cat_select();
$this->load->view('deneme_form', $data);
}
function cat_select()
{
$str = "<select name=\"page_id\" size='15' style='width:300px'>";
foreach($this->options() as $data)
{
$str .= '<option value="'.$data['lft'].'">'.$data['name'].'</option>';
}
$str .= "</select>";
return $str;
}
function options()
{
$sql = "SELECT CONCAT( REPEAT(' ', COUNT(detail.id) - 1), detay.title) AS name, node.id, node.lft
FROM table AS node,
table AS parent,
table_detail AS detail
WHERE node.lft BETWEEN parent.lft AND parent.rgt
AND node.id = detail.page_id
GROUP BY node.id
ORDER BY node.lft";
$query = $this->db->query($sql);
$row = $query->result_array();
return $row;
}
[eluser]omerorhan[/eluser]
Hi again,
How could i add "depth colomn" to "get_descendants_where" method?
like that:
Code: $data_array = $this->MPTtree->get_descendants_where($node['lft'],$node['rgt'],array('lang_id' => '1'),$with_level_col = TRUE);
Thanks
[eluser]TheFuzzy0ne[/eluser]
Martin, I'd really like to see two new methods added to the library - promote_node and demote_node, which could allow us to move a node to the specified position within the parent node. If no argument is specified, the methods could move the node right to the top, or right to the bottom, depending on which method was called.
Perhaps this isn't needed, but I think it would be really handy.
Thanks for such a great library. 
[eluser]CtheB[/eluser]
Hi Martin & others,
Is MPPTREE a completed library or is it just me who thinks the most important methods are missing in this library?
I think the most important and basic thing the library must do is:
- Building navigations
- Detecting the active navigations id.
A menu is a nested list in the database like this:
Quote:1. Menu [ 1 - 22]
2. Main Menu [2 - 19]
3. Home [3 - 14]
4. About us [4 - 5]
5. Credits [6 - 11]
6.Technical [7 - 8]
7.Design [9 - 10]
8. Content [12 - 13]
9.News [15 - 18]
10.Archive [16 - 17]
11.Sub Menu [20 - 21]
Must be parsed like this to the browser (if we want to get the main menu):
Quote:<ul>
<li><a href="http://www.example.com/">Home</a> // Home must be the site root url, this is for SEO optimalisation.
<ul>
<li><a href="http://www.example.com/home/about-us">About us</a></li>
<li><a href="http://www.example.com/home/credits">Credits</a>
<ul>
<li>
<li><a href="http://www.example.com/home/credits/technical">Technical</a></li>
<li><a href="http://www.example.com/home/credits/design">Design</a></li>
</ul>
</li>
<li><a href="http://www.example.com/home/content">Content</a></li>
</ul>
</li>
<li><a href="http://www.example.com/news">News</a>
<ul>
<li><a href="http://www.example.com/news/archive">Archive</a></li>
</ul>
</li>
</ul>
Oke getting this full navigations like this is not easy, but i've worked something out for this, but i missed it in MPPTREE
2nd thing: Detecting the active navigations id.
If we are on this page: http://www.example.com/home/credits/design
An query must get the active menu id on base of the url 'home/credits/design';
(and not only on the 'design' part, because there could be a menu item with this url: 'portfolio/design')
Can somebody help me on building this query???
Ofcourse i tried doing it myself. I get as far as this:
Table navigations in the database:
Quote:CREATE TABLE `navigations` (
`navigationsId` int(11) NOT NULL auto_increment,
`navigationsLeft` int(11) default NULL,
`navigationsRight` int(11) default NULL,
`navigationsActive` tinyint(1) NOT NULL,
`navigationsWeight` int(11) NOT NULL,
`navigationsTitle` varchar(100) NOT NULL,
`navigationsLink` varchar(100) NOT NULL default '',
`navigations_FK_pagesId` int(11) default NULL,
`navigations_FK_templateId` int(11) default NULL,
PRIMARY KEY (`navigationsId`),
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
PHP method for retrieving the active navigations id:
Quote:public function get_menu_id()
{
if ($this->uri->segment(1))
{
$i = 1;
$url = '';
while($segment = $this->uri->segment($i))
{
$url .= $segment.'/';
$i++;
}
$new_length = strlen($url) - 1;
$url = substr($url, 0, $new_length);
}
else
{
$url = 'home';
}
$query = $this->db->query("
SELECT node.navigationsId, sub_tree.depth,
(SELECT GROUP_CONCAT(parent2.navigationsLink ORDER BY parent2.navigationsLeft SEPARATOR '/')
FROM navigations AS node2, navigations AS parent2
WHERE node2.navigationsLeft BETWEEN parent2.navigationsLeft AND parent2.navigationsRight
AND node.navigationsLink = node2.navigationsLink
AND parent2.navigationsShowLink IS NULL
GROUP BY node2.navigationsTitle
ORDER BY node2.navigationsLeft) as link,
node.navigationsLeft,
(COUNT(parent.navigationsTitle) - (sub_tree.depth + 1)) AS depth
FROM navigations AS node,
navigations AS parent,
navigations AS sub_parent,
(
SELECT node.navigationsTitle, (COUNT(parent.navigationsTitle) - 1) AS depth
FROM navigations AS node, navigations AS parent
WHERE node.navigationsLeft BETWEEN parent.navigationsLeft AND parent.navigationsRight
GROUP BY node.navigationsTitle
ORDER BY node.navigationsLeft
)AS sub_tree
WHERE node.navigationsLeft BETWEEN parent.navigationsLeft AND parent.navigationsRight
AND node.navigationsLeft BETWEEN sub_parent.navigationsLeft AND sub_parent.navigationsRight
AND sub_parent.navigationsTitle = sub_tree.navigationsTitle
GROUP BY node.navigationsTitle
HAVING depth > 0
ORDER BY node.navigationsLeft;
");
if($result = $query->result())
{
foreach($result as $key)
{
if($key->link == $url)
{
$this->menu_id = $key->navigationsId;
$query->free_result();
return $this->menu_id;
}
}
}
else
{
return false;
}
OKE this is working, but i want to retrieve the active navigationsId in once from the query.
And not with a foreach loop in PHP. (because this is slowing things down)
Can somebody help me with this?
For more info, ofcourse, look at this page: http://dev.mysql.com/tech-resources/arti...-data.html
[eluser]pistolPete[/eluser]
[quote author="CtheB" date="1239550128"]Is MPPTREE a completed library or is it just me who thinks the most important methods are missing in this library?[/quote]
This library serves one purpose: Storing "Hieararchical trees in a (relational) database table".
Therefore it is not limited to saving navigation data but you can use it for each and every hierarchical data; so I don't think it's missing anything...
[quote author="CtheB" date="1239550128"]Detecting the active navigations id.[/quote]
What you want to do is absolutely possible using MPTtree:
Have a look at the functions xpath and xpath2!
[eluser]CtheB[/eluser]
Oke thank you.
I looked at the function Xpath and Xpath2.
But there are like 3 query's and some foreach loops to archive the goal.
I want it just with 1 query and nothing else, look at my post above... it must be possible with some group_concats in the query.
Some SQL gurus who know how to make this work?
[eluser]m4rw3r[/eluser]
There is also a method called tree2array() which creates a nested array which you can then recursively iterate to create a navigation menu.
[eluser]CtheB[/eluser]
Oke,
But is Tree2array as fast as this query:
Quote:<?php
public function show($depth, $menu)
{
$query = $this->db->query("
SELECT node.navigationsId, node.navigationsTitle, sub_tree.depth,
(SELECT GROUP_CONCAT(parent2.navigationsLink ORDER BY parent2.navigationsLeft SEPARATOR '/')
FROM navigations AS node2, navigations AS parent2
WHERE node2.navigationsLeft BETWEEN parent2.navigationsLeft AND parent2.navigationsRight
AND node.navigationsLink = node2.navigationsLink
AND parent2.navigationsShowLink IS NULL
GROUP BY node2.navigationsTitle
ORDER BY node2.navigationsLeft) as navigationsLink,
node.navigationsLeft,
(COUNT(parent.navigationsTitle) - (sub_tree.depth + 1)) AS depth
FROM navigations AS node,
navigations AS parent,
navigations AS sub_parent,
(
SELECT node.navigationsTitle, (COUNT(parent.navigationsTitle) - 1) AS depth
FROM navigations AS node, navigations AS parent
WHERE node.navigationsLeft BETWEEN parent.navigationsLeft AND parent.navigationsRight
AND node.navigationsLink = '$menu'
GROUP BY node.navigationsTitle
ORDER BY node.navigationsLeft
)AS sub_tree
WHERE node.navigationsLeft BETWEEN parent.navigationsLeft AND parent.navigationsRight
AND node.navigationsLeft BETWEEN sub_parent.navigationsLeft AND sub_parent.navigationsRight
AND sub_parent.navigationsTitle = sub_tree.navigationsTitle
GROUP BY node.navigationsTitle
HAVING depth $depth
ORDER BY node.navigationsLeft;
");
return $query->result();
}
?>
And then using this view:
Code: <?php
if(!empty($menu))
{
?> <ul>
<?php
$i = 1;
$menu_items = count($menu);
foreach($menu as $value)
{
if(isset($j) && $value->depth == $j)
{
echo "</li>";
}
elseif(isset($j) && $value->depth > $j)
{
echo "<ul>";
}
elseif(isset($j) && $value->depth < $j - 2)
{
echo "</li>";
echo "</ul>";
echo "</li>";
echo "</ul>";
echo "</li>";
echo "</ul>";
echo "</li>";
}
elseif(isset($j) && $value->depth < $j - 1)
{
echo "</li>";
echo "</ul>";
echo "</li>";
echo "</ul>";
echo "</li>";
}
elseif(isset($j) && $value->depth < $j)
{
echo "</li>";
echo "</ul>";
echo "</li>";
}
if($value->navigationsId == $menuId)
{
$active = "active";
}
else
{
$active = "";
}
if((isset($k) && $value->navigationsLeft - 1 == $k) || !isset($k))
{
$first = "first ";
}
else
{
$first = "";
}
echo '<li class="'.$first.$active.'">';
echo anchor($value->navigationsLink, $value->navigationsTitle);
if($i == $menu_items)
{
if($value->depth == 4)
{
echo "</li>";
echo "</ul>";
echo "</li>";
echo "</ul>";
echo "</li>";
echo "</ul>";
echo "</li>";
}
elseif($value->depth == 3)
{
echo "</li>";
echo "</ul>";
echo "</li>";
echo "</ul>";
echo "</li>";
}
elseif($value->depth == 2)
{
echo "</li>";
echo "</ul>";
echo "</li>";
}
elseif($value->depth == 1)
{
echo "</li>";
}
}
$i++;
$j = $value->depth;
$k = $value->navigationsLeft;
}
?> </ul>
<?php
}
?>
[eluser]m4rw3r[/eluser]
You can use something like this function to generate the menu. I don't know which one is faster, but yours put more of the load on the database.
It is only one solution, benchmark!
(I don't have time for it, working)
|
