Creating A Menu With ASP

[Yahweh]

I've created menus with subcategories before, but not very efficently. Basically, I want to take a table that looks like this:

ID      ParentID        Name1       0               Menu12       1               Menu23       1               Menu34       0               Menu45       1               Menu56       4               Menu67       6               Menu78       6               Menu89       0               Menu910      6               Menu10

And make it output something like this:

Menu1     Menu2     Menu3     Menu5Menu4     Menu6          Menu7          Menu7          Menu10Menu9

Is there a painless way to do that without requerying the database a dozen times?

[justsomeguy]

Probably the most clear way is to have a recursive function that queries the database for each item. Like this:

function write_menu(parent, level){  level = parseInt(level);  var sqlcon = Server.CreateObject("adodb.recordset");  sqlcon.ActiveConnection = sql_dbcon_string;    sqlcon.open("SELECT * FROM menu WHERE ParentID=" + parent + " ORDER BY name");  while (!sqlcon.eof)  {    for (i = 0; i < level; i++)      Response.Write("  "); //indent    Response.Write(sqlcon.fields.item("name").value);    write_menu(sqlcon.fields.item("id").value, level + 1);        sqlcon.movenext();  }  sqlcon.close();}write_menu(0, 0);

I haven't tested that, but it should work. But, it will query the database a lot. If you want to avoid that, I guess you could get everything all at once and load it into an array, and then keep looping through the array to write everything out. A little more confusing in the code though. Maybe something like this:

var menu_items = new array();var idx_id = 0;var idx_parent = 1;var idx_name = 2;var sqlcon = Server.CreateObject("adodb.recordset");sqlcon.ActiveConnection = sql_dbcon_string;  sqlcon.open("SELECT * FROM menu ORDER BY Name");while (!sqlcon.eof){  len = menu_items.push(new array());  menu_items[len - 1][idx_id] = sqlcon.fields.item("id").value;  menu_items[len - 1][idx_parent] = sqlcon.fields.item("parentid").value;  menu_items[len - 1][idx_name] = sqlcon.fields.item("name").value;  sqlcon.movenext();}sqlcon.close();for (i = 0; i < menu_items.length; i++){  if (menu_items[i][idx_parent] == 0) // root level first  {    Response.Write(menu_items[i][idx_name]);    show_children(menu_items[i][idx_id], 1);  }}function show_children(parent, level){  level = parseInt(level);  for (var i = 0; i < menu_items.length; i++)  {    if (menu_items[i][idx_parent] == parent)    {      for (var j = 0; j < level; j++)        Response.Write("  "); //indent      Response.Write(menu_items[i][idx_name]);      show_children(menu_items[i][idx_id], level + 1);    }  }}

Again, untested. Maybe it works, who knows.