When i was tasked with creating a website/cms i wanted to present the admin a tree structure where they can see entire structure and edit/add/reorder whatever pages they wanted to (a screen shot at permanent link).
I read a few articles on storing hierarchical data in databases:
Managing Hierarchical Data in MySQL
Storing Hierarchical Data in a Database
Four ways to work with hierarchical data
and read a chapter or two from Joe Celko’s Trees and Hierarchies in SQL for Smarties
I chose modified preorder tree traversal which is basically the nested set model with a parent, so it’s kind of a hybrid between nested and adjacency model…
I am not going to spend my time going into any details about this structure, i am just not smart enough to do it, nor can i say i am 100% comfortable, so read the articles … it’s really a thing of beauty when you fully understand it …
So now i use mostly SQL, to retrieve data instead of any server side code… i kinda cheated, and run rebuild script when user deletes or adds a new node… it really works out well for me, but i must say it might be the lazy way out…
When user adds a new node, i make sure the parent is correct and set lft/rgt to some really high value that should never clash… then i run rebuild (again i said i am “lazy”) ... and that worked out really well…
Because I set lft/rgt to a high value, anything that gets added us always last, which is less than ideal… so what to do
I wrote article way back when … and why re-invent the wheel:
Maintain a sortorder column with a HTML/Javascript GUI … so how to re-use with minimal effort?
I found that I did not need to change the gui, except to store the parent id. ... my first run was to swap around the lfts + rgts , and then do children if it had children… but that seemed to be difficult to do … then it dawned on me … just give new lfts then run rebuild … and i went to code below …
SO first i grab the lft+rgt of parent id.. I then loop over the array and update lft + right based on order passed in … then i run rebuild
.
//GRAB PARENT LFT + RGT
//NOTE: I HAVE SQL WRAPPER THAT DOES THIS
//"SELECT lft,rgt from MY_PAGE_TABLE
//WHERE PARENT_ID=$pid"
$p_lft = LFT OF PARENT
$offset = 1; /
sql_query("LOCK TABLES MY_PAGE_TABLE WRITE");
foreach ($page_order_array as $page_id)
{
//ADD SLASHES AROUND PARENT ID
$page = addslashes($page_id);
//--
//GRAB MY CURRENT LFT + RGT
//NOTE: I HAVE SQL WRAPPER THAT DOES THIS
//"SELECT lft as m_lft,rgt as m_rgt
//FROM MY_PAGE_TABLE
//WHERE id='$page_id'";
//FIND MY WIDTH
$diff = intval($m_rgt) - intval($m_lft);
//CALCULATE MY NEW LFT + RIGHT
$new_left = $p_lft+$offset;
$new_right = $p_lft+$offset+$diff;
//UDATE LFT + RGT
//NOTE: I HAVE SQL WRAPPER THAT DOES THIS
//"UPDATE MY_PAGE_TABLE
//SET "lft= '$new_left',rgt= '$new_right'
//WHERE id='$page_id';
//CALCULATE OFFSET FOR NEXT LFT
$offset = $offset + $diff + 1;
}
//REBUILD PARENT
rebuild_tree($pid, $p_lft);
You don’t really need to set RGT ... if you run rebuild on parent, but it’s good knowledge to see how to do WIDTH ... you really just need to SET lft,lft+1, etc …
After I did this … i realized my original code could of been reused exactly, because I ran the rebuild…
.
//page_order is submitted valules
UPDATE TABLE MY_PAGE_TABLE
SET LFT = FIND\_IN_SET(id, $page_order)
//REBUILD PARENT
rebuild_tree($pid, $p_lft);
I am sorry I thought this was going to be simple to explain … ugh ... oh well it’s already written… and it was a great lesson learned for me.