Query for updating a node in hierarchial tree structure tips on internet dating profiles
some like: https://en.wikipedia.org/wiki/Base36 Suppressing also the slash '/' separator by using fixed length and padding to the encoded id Detailed optimization explanation here: https://bojanz.wordpress.com/2014/04/25/storing-hierarchical-data-materialized-path/ TODO building a function or procedure to split path for retreive ancestors of one item SET @id:= '22'; SELECT Menu_Name, (@id:=Sub_Menu_ID ) as Sub_Menu_ID, Menu_ID FROM ( SELECT Menu_ID, Menu_Name, Sub_Menu_ID FROM menu ORDER BY Sub_Menu_ID DESC ) AS aux_table WHERE Menu_ID = @id ORDER BY Sub_Menu_ID; SELECT id, NAME,'' AS sub Name,'' AS subsub Name,'' AS subsubsub Name FROM Table1 WHERE prent is NULL UNION SELECT b.id,a.name,AS sub Name,'' AS subsub Name,'' AS subsubsub Name FROM Table1 AS a LEFT JOIN Table1 AS b ON b.prent=WHERE a.prent is NULL AND IS NOT NULL UNION SELECT c.id,a.name,AS sub Name,AS subsub Name,'' AS subsubsub Name FROM Table1 AS a LEFT JOIN Table1 AS b ON b.prent=LEFT JOIN Table1 AS c ON c.prent=WHERE a.prent is NULL AND IS NOT NULL UNION SELECT d.id,a.name,AS sub Name,AS subsub Name,AS subsubsub Name FROM Table1 AS a LEFT JOIN Table1 AS b ON b.prent=LEFT JOIN Table1 AS c ON c.prent=LEFT JOIN Table1 AS d ON d.prent=WHERE a.prent is NULL AND IS NOT NULL ORDER BY NAME,sub Name,subsub Name,subsubsub Name Thank you for your interest in this question.Because it has attracted low-quality or spam answers that had to be removed, posting an answer now requires 10 reputation on this site (the association bonus does not count).As with anything, you need to look at your requirements and decide what approach is best for you.
This is know as Modified Preorder Tree Traversal and lets you run a simple query to get all parent values at once. select a.id,if(a.parent = 0,@varw:=concat(a.id,','),@varw:=concat(a.id,',',@varw)) as list from (select * from recursivejoin order by if(parent=0,id,parent) asc) a left join recursivejoin b on (= b.parent),(select @varw:='') as c having list like ',%'; Tree and Tree\Node are PHP classes for handling data that is structured hierarchically using parent ID references.
A typical example is a table in a relational database where each record’s “parent” field references the primary key of another record.
Of course, Tree cannot only use data originating from a database, but anything: you supply the data, and Tree uses it, regardless of where the data came from and how it was processed.
select @pv:=p1as id , p2as parent_name , p1name , p1.parent_id from products p1 join (select @pv:=19)tmp left join products p2 on p2.id=p1.parent_id -- optional join to get parent name where [email protected] You can do it like this in other databases quite easily with a recursive query (YMMV on performance).
The other way to do it is to store two extra bits of data, a left and right value.
read more I found it more easily to : 1) create a function that will check if a item is anywhere in the parent hierarchy of another one.