Dynamic TreeView from DB

[biggieuk]

Hi all,

 

Im struggling to figure out the best solution for the following:

 

I have two tables:

 

table1 contains columns called MainID, Company.

table2 contains columns called SubID, SubCompany, MainID.

 

table2 has many records assigned to each MainID.

 

I have an sql query that retrieves each record from table2 and displays which MainID it is assigned to.

 

 

With this data i am trying to dynamically populate an XML treeview but am having trouble thinking up a way to loop through each record and construct something along the lines of:

 

<item id="parent" value="<%= Company %>" >
      <item id="child" value="<%= SubCompany %>" >
      <item id="child" value="<%= SubCompany %>" >
<item id="parent" value="<%= Company %>" >
      <item id="child" value="<%= SubCompany %>" >
      <item id="child" value="<%= SubCompany %>" >
      <item id="child" value="<%= SubCompany %>" >
<item id="parent" value="<%= Company %>" >
      <item id="child" value="<%= SubCompany %>" >
..............

 

Thanks for any help with this, please ask me to try another explanation if that is slightly confusing  :S

 

 

[rivan]

You can do something like this (I usually solved similar problems that way) - SQL first

select t1.MainID, t1.Company, t2.SubID, t2.SubCompany from table1 t1 join table2 t2 on t1.MainID = t2.MainID order by t1.MainID

then in php something like

$last_company_id = -1;
while (some_data_read()) {
      if ($last_company_id != $current_company_id) {
            if ($last_company_id != -1)
                  echo "</item">; // to close that tag of previous main category
            $last_company_id = $current_company_id;
            echo "<item id='parent' value='$current_company'>"; // openning tag for new category
      }
      echo "<item id='child' value='$current_subcompany'/>";
}

// now just finish last open main company if any
if ($last_company_id != -1)
      echo "</item">;

 

I think you got the idea - remember main company you are working with currently, when it changes then create the new tag and do whatever necessary

[biggieuk]

Thanks very much, seems to work well, althought the xml format is slightly wrong for my purpose.

 

It is returning a </item> tag after each SubCompany:

<item id="parent" value="<%= Company %>" >
      <item id="child" value="<%= SubCompany %>" >
</item>
      <item id="child" value="<%= SubCompany %>" >
</item>
<item id="parent" value="<%= Company %>" >
      <item id="child" value="<%= SubCompany %>" >
</item>
      <item id="child" value="<%= SubCompany %>" >
</item>
      <item id="child" value="<%= SubCompany %>" >
</item>
...................

 

Where's i need it so its only closing off the parent after the SubCompanys have been returned:

 

<item id="parent" value="<%= Company %>" >
      <item id="child" value="<%= SubCompany %>" >
      <item id="child" value="<%= SubCompany %>" >
</item>
<item id="parent" value="<%= Company %>" >
      <item id="child" value="<%= SubCompany %>" >
      <item id="child" value="<%= SubCompany %>" >
      <item id="child" value="<%= SubCompany %>" >
</item>
...................

 

Any ideas?

 

Thanks

 

[biggieuk]

Scrap that, i had the If ending in the wrong place.

 

Thanks!