<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<title>[var..script_name] test</title>
<style type="text/css">
<!--
#sections{
position: relative; float: right; width: 180px; margin: 26 10 0 10; text-align: left; border: medium double rgb(0,0,255); padding: 10 ;
}
body{
background-color: #ffffe0 ;
font-family: Tahoma, Arial, ;
color: Navy;
margin: 0 0 0 0;
}
a {
color: Red;
}
.wrapper{
background-color: #ffffe0 ;
}
.header{
background-color: #ffff99 ;
font-family: "MS Comic Sans" ;
font-size: 20px;
line-height: 55px;
margin-top: 12px;
margin_bottom: 16px;
}
div.logoheader{
background-color: #ffff99 ;
font-family: "Arial" ;
font-size: 18px;
line-height: 55px;
margin-top: 0px;
margin_bottom: 30px;
}
img.logoheader{
margin-top: 0px;
margin_bottom: -30px;
}
table.mainbody{
margin-top: 12px;
margin-left: 20px;
font-size: 14px;
}
td.mainbody{
font-size: 11px;
}
td.mainheader{
font-size: 14px;
}
.timer{
color: red;
font-size: 10px;
}
.menu0000{
background-color: #4682b4;
color: white;
font-size: 11px;
font-weight: bold;
line-height: 16px;
}
.yes{
background-color: #ffffcc;
font-size: 9px;
}
.no{
background-color: #f0f8ff;
font-size: 9px;
}
td{
font-size: 11px;
}
.menutree{
background-color: #f0f8ff;
font-size: 11px;
margin-top: 2px;
margin_bottom: 6px;
line-height: 14px;
}
-->
</style>
</head>
<body>
[var.debug;noerr]
<table width="99%" height="100%" align=left class=wrapper border="0" cellpadding="0" cellspacing="0" summary="">
<!-- header banner -->
<tr><td class=header align=center valign=middle><a href="http://www.tinybutstrong.com/"><img border=1 src="tbs_logo1_94x51.gif" width="94" height="51" vspace=10 alt="TBS home"></a></td><td align=left valign=middle class=header>
<div class=logoheader>       Better websites using the TinyButStrong template class </div>
</th></tr>
<tr><td valign=top width=125>
<table class="menu" height="10%" width="125" bgcolor="#dadada" border="1" cellspacing="0" cellpadding="0">
<tr><td valign=top class=menutree nowrap=nowrap>
<!-- ==================== Here's the TBS code for MENU TREE ======================== -->
<tree>
<div class="menu[m.m_parent;]"> [m.indent;ondata=f_ondata_tree;block=tree;htmlconv=no;noerr] [m.m_title;;noerr]<br>
</div>
</tree>
<div class="menu0000">  </div>
<!-- ==================== ================================= ======================== -->
</td></tr>
</table>
<!-- end left col for menu tree -->
<!-- begin timer output -->
<table width="125" summary="" border="1" bgcolor="#ffffcc" cellpadding="2" cellspacing="0">
<tr><td colspan=2 align=center bgcolor="#dadada">class_timer</td></tr>
<tr><td class=timer align=right>connect=</td><td class=timer align=right>[var.connect_time;frm=0.000000;magnet=tr;noerr;]  </td></tr>
<tr><td class=timer align=right>load=</td><td class=timer align=right>[var.load_time;frm=0.000000;magnet=tr;noerr;]  </td></tr>
<tr><td class=timer align=right>query=</td><td class=timer align=right>[var.query_time;frm=0.000000;magnet=tr;noerr;]  </td></tr>
<tr><td class=timer align=right>merge=</td><td class=timer align=right>[var.merge_time;frm=0.000000;magnet=tr;noerr;]  </td></tr>
<tr><td class=timer align=right>show=</td><td class=timer align=right>[var.show_time;frm=0.000000;magnet=tr;noerr;]  </td></tr>
</table>
</td>
<td width="*%" valign=top>
<!-- begin main body -->
<table width="100%" class="mainbody" border="0" cellpadding="6" cellspacing="0" summary="">
<tr><td class="mainheader">Lineage-based sorting for faster hierarchy menus: simpler can be better!</td></tr>
<tr><td class="mainbody" width="*%">
<div>
<div id="sections">
Implementation:
<li type=circle><a href="add_new_item.php">Add/edit a menu tree item </a>
<li type=circle><a href="lineage_create_php.php">Utility script to recalc all</a>
<li type=circle><a href="skrol_code.php">Original subquery approach</a>
</div>
What I wanted:
<ul>
<li type=circle>Hierarchical menu tree - showing indentation of levels - shown in left column
<li type=circle>Single-query speed - not based on recursive queries /sub-queries
<li type=circle>Self-maintaining tree - automated recalculation after insert/update
<li type=circle>Utility function - to rebuild/recalculate ALL of the lineage values from scratch
</UL>
As usual, Skrol29 - the grand maestro of <a href="http://www.tinybutstrong.com/"> TBS </a>, inspired me to look beyond the current examples that use subqueries to implement a hierarchy of menus items - after some digging I found <a href="http://www.evolt.org/article/Four_ways_to_work_with_hierarchical_data/17/4047/index.html" target="_blank">this article</a> which included a discussion by commenters of the "lineage" method. It was easy to see the logic of defining the lineage (parentage) of each menu item - eureka!
<P>
I decided to implement the lineage model because of it's obvious speed advantages. The only disadvantage being that I would need to write the code to create the lineage value for each menu item.
<P>
For reference, I used Skrol29's implementation from <a href="http://www.tinybutstrong.com/forum.php?msg_id=7900">Tips and Tricks</a> and kept the basic db table layout as well, only adding to it for the new 'lineage' field.<P>
<P>
</div>
Here's where we heading...<br>
<table width=380 border=1 cellspacing=0 cellpadding=2>
<tr><td width=20 align=center>id</td><td width=30 align=center>m_id</td><td align=center>m_parent</td><td width=150 align=left>m_title</td><td bgcolor="#ffffcc" width=170 align=left>m_newlineage (parentage)</td></tr>
<tr><td width=20 align=center>0</td><td align=center>001</td><td align=center>000</td><td align=left>Hello World</td><td bgcolor="#ffffcc" align=left>0</td></tr>
<tr><td width=20 align=center>1</td><td align=center>002</td><td align=center>001</td><td align=left> Goodbye Cruel World</td><td bgcolor="#ffffcc" align=left>0-001</td></tr>
<tr><td width=20 align=center>2</td><td align=center>004</td><td align=center>002</td><td align=left> Why so sad?</td><td align=left bgcolor="#ffffcc">0-001-002</td></tr>
<tr><td width=20 align=center>3</td><td align=center>006</td><td align=center>004</td><td align=left> Skool sukz</td><td align=left bgcolor="#ffffcc">0-001-002-004</td></tr>
<tr><td width=20 align=center>4</td><td align=center>008</td><td align=center>006</td><td align=left> Grow up</td><td align=left bgcolor="#ffffcc">0-001-002-004-006</td></tr>
<tr><td width=20 align=center>5</td><td align=center>007</td><td align=center>004</td><td align=left> Cheer up</td><td align=left bgcolor="#ffffcc">0-001-002-004</td></tr>
<tr><td width=20 align=center>6</td><td align=center>003</td><td align=center>000</td><td align=left>Foo was here</td><td align=left bgcolor="#ffffcc">0</td></tr>
<tr><td width=20 align=center>7</td><td align=center>005</td><td align=center>003</td><td align=left> Kilroy was there</td><td align=left bgcolor="#ffffcc">0-003</td></tr>
</table>
<P>
Because we will need to sort on the 'lineage' field we need to add the new <u>m_newlineage</u> field and change the field definition for <u>m_id</u> to 'integer zerofill auto_increment' and <u>m_parent</u> to 'integer zerofill'.
<P>
If the table key is maintained as autoincrement then, when we make any new additions, the menu structure will always follow after their parent record - this makes the generation of the lineage value a single pass through the database. So we will add <u>id</u> as a 'primary key auto_increment' field, making the <u>m_id</u> the field available for independently sorting peer-level items.
<P>
</td>
</tr>
</table>
</td></tr>
<tr><td > <!-- footer row -->
</td>
<td align=center>
<a href="http://tomhenry.us/tbs3/">Return to TBS apps page</a>
<hr color=silver width="80%">
© [var.year;noerr] <a href="http://tomhenry.us/" target="_blank">Tom Henry / Strategic Business Sytems</a> - all rights reserved.
<br>
TBS v.[var..version] with ezSQL v.[var.ezsql_version]
<br>
<br>
</td></tr>
</td></tr> <!-- end wrapper table -->
</table>
</body>
</html>