Getting problem in showing 20,000 aprox. items in hierarchy lying in a mysql table. I have tried recursive looping; it worked but really very slow. Please tell me any other way to show items in hierarchy.
The Table Structure is as follow.
Col#
1 Item ID int(11) auto increment
2 Item Name varchar(255)
3 Hierarchy No int(11)
4 Parent Item ID int(11)
Now we have items in table in this arrangement.
Item ID Item Name Hierarchy No Parent Item ID
1 Parent Item 1 1 0
2 Parent Item 2 1 0
3 Parent Item 3 1 0
4 Sub Item 1 1 2 1
5 Sub Item 1 2 2 1
6 Sub Item 2 1 2 2
7 Sub Item 2 2 2 2
8 Sub Item 1 1 1 3 4
9 Sub Item 3 1 2 3
10 Sub Item 3 1 1 3 9
11 Sub Item 3 1 2 3 9
Up to 20,000 items. Up to “6” Hier no
Now I want the above items in the following arrangement.
Parent Item 1
----Sub Item 1 1
--------Sub Item 1 1 1
----Sub Item 1 2
Parent Item 2
----Sub Item 2 1
----Sub Item 2 2
Parent Item 3
----Sub Item 3 1
--------Sub Item 3 1 1
--------Sub Item 3 1 2
I have got this arrangement by using this recursive code.
function show_hier($id,$level){
$font_size=2;
$query="select item_id,itemname,parent_item_id,hierno from items where parent_item_id=".$id;
$result=mysql_query($query);
while ($row=mysql_fetch_row($result)){
$count1=0;
$id=$row[0];
$stn="";
for ($i=3; $i<=$row[3]; $i++) {
for ($j=3;$j<=$row[3]; $j++) $stn.=" ";
$count1++;
}
if ($count1>=4) echo "<i>";
if ($count1>=4) $font_size=1;
%>
<%=$stn%><%=ucfirst($row[1])%>
<br>
<%
if ($count1>=3) echo "</i>";
if ($level=="") $level=1000;
if ($id!="" and $count1<$level) show_hier($id,$level);
}//end of while loop
}// end of function
The code is working fine for 600 – 700 items, but gradually becomes very very slow when items exceed more then 2000. Please help me find out some faster solution.
Wajhe
