asked on

Multi-dimesional array sort?

please see this question:
https://www.experts-exchange.com/questions/21885888/Dynamic-Drop-Down-Part-Duh.html

what's been built is a drop-down list of a hierarchal category table structured like this:

tableName
icID <-INT(4) IDENTITY
parentID <-INT(4)
invCat <-NVARCHAR(50)

and it builds the drop-down here:http://test.apenloversparadise.com/untitled.asp

the code is:
<%
Sub write(strString)
      response.write(strString)
End Sub
function getparent(parid)
      if parid > 0 then
            set rs=createobject("adodb.recordset")
                  query = "select parentID, invCat from invCat where icID = " & parid & " ORDER BY invCat"
                  rs.open query, cn
                        getparent = getparent(rs("parentID")) & rs("invCat") & " - "
                  rs.close
            set rs=nothing
      end if
end function

set cn=createobject("adodb.connection")
cn.open inrsConn
sRecords = "SELECT * FROM invCat"
       Set r = cn.Execute(sRecords)
              write "<select name='cat'>"
              Do While Not r.EOF
                   Write "<option value=""" & r.Fields("icID") & """>" & getparent(r("parentID")) & r("invCat") & "</option>"
                   r.MoveNext
              Loop
              write "</select>"
       set r=nothing
cn.close
set cn=nothing
%>

now what I need to do is get it to sort by the 'ultimate' parent category name. It was mentioned to me that I should put it into a multi-dimensional array, and use the sorting method in that.....I do not know how to do this though...

fritz_the_blank

I have seen a few options out there, particularly one called QSort. This is what I have come up with (it is still in rough shpae, this is just an idea that I was thinking about):

sub sortArray(byRef arrToSort, strSortIndexes, strSortOrders)
      'created October 2005
      'arrToSort is the 2D array that you want to sort
      'strSortIndexes are the array indexes that you want to sort by, for example 0,2,1
      'strSortOrders is how you want to sort the columns--for example "Asc,Asc,Desc"
      Const adDouble = 5
      Const adVarChar = 200
      Const adDate = 7

      'create recordset to sort array
      dim objRS_ArrSorter
      Set objRS_ArrSorter = Server.CreateObject("ADODB.Recordset")
      for i = 0 to UBound(arrToSort,2)-1
            strFieldName="fldField_" & i
            if isNumeric(arrToSort(0,i)) then
                  objRS_ArrSorter.Fields.Append strFieldName, adDouble
            elseif isDate(arrToSort(0,i)) then
                  objRS_ArrSorter.Fields.Append strFieldName, adDate
            else
                  objRS_ArrSorter.Fields.Append strFieldName, adVarChar, 255
            end if
      next
      objRS_ArrSorter.Open

      'populate recordset from array
      for i=0 to UBound(arrToSort,1)-1
            objRS_ArrSorter.AddNew
            for j=0 to UBound(arrToSort,2)-1
                  if arrToSort(i,j) <>"" and not isNull(arrToSort(i,j)) and not isEmpty(arrToSort(i,j)) then
                        objRS_ArrSorter.Fields(j).value = arrToSort(i,j)
                  else
                        objRS_ArrSorter.Fields(j).value = ""
                  end if
            next
      next

      'sort it
      arrSortIndexes = Split(strSortIndexes, ",")
      arrSortOrders = Split(strSortOrders,",")
      strSortString = ""
      for i=0 to UBound(arrSortIndexes)
            strSortString = strSortString & "fldField_" & arrSortIndexes(i) & " " & arrSortOrders(i) & ", "
      next
      strSortString = Left(strSortString,Len(strSortString)-2)
      objRS_ArrSorter.sort = strSortString

      'put the sorted data back into the array
      objRS_ArrSorter.MoveFirst()
'      arrItemsCount = objRS_ArrSorter.GetRows()
      intRecord = 0
      do while not objRS_ArrSorter.eof
            for i=0 to objRS_ArrSorter.Fields.Count -1
                  arrToSort(intRecord,i) = objRS_ArrSorter.Fields(i).value
            next
            objRS_ArrSorter.MoveNext
            intRecord = intRecord +1
      loop
       'clean up objects
      objRS_ArrSorter.Close()
      Set objRS_ArrSorter = Nothing
end sub

FtB

kevp75

ASKER

nice one fritz...

how can I implement this in the code I posted in the question?

ASKER CERTIFIED SOLUTION

clockwatcher

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

clockwatcher

Reminds me how much I dislike vbscript.

kevp75

ASKER

@clockwatcher

works good until I comment out the array, and un-comment the myCats.BuildFromDB
all I get is a blank drop-down with nothing in it

fritz_the_blank

@Clockwatcher--

I thought about using the JScript option, but I understood that to be for single diminsion arrays only.

@Kevp75--

I would have to know what WMIF had in mind. A few things suggest themselves to me:

1) use the ParentID as a parameter in the SQL Select?
2) Use the RecordSet.Filter() method

I need to know a little more. I'll reread your other questions.

FtB

kevp75

ASKER

@fritz
clockwatchers works great for the array provided, however when I comment out and un-comment (like shown in his code) all I get is a blank drop-down

kk...i'll be here :)

fritz_the_blank

Would you please return to your original code at http://test.apenloversparadise.com/untitled.asp so I can see what you get?

FtB

kevp75

ASKER

sorry bout that...refresh please

fritz_the_blank

Also, does this not work?

strSQL = "SELECT catID. ParentID, CatName FROM tblYourTable ORDER BY ParentID, catID"
set objRS = Server.CreateObject("ADODB.RecordSet")
objRS.Open strSQL,cn,3,3

do while not objRS.EOF
response.write("<option value=""" & objRS("catID") & "">" & objRS("invCat") & objRS("invCat") & "</option">
objRS.MoveNext
loop

FtB

kevp75

ASKER

all that would display is something like this:

<option>CrossCross</option>

and would not display the hierarchy of categories, if you scroll the top drop-down to right around the bottom you'll see what I mean by hierarchy

fritz_the_blank

Sorry, I confused the parent category with the sub category.

tableName
icID <-INT(4) IDENTITY
parentID <-INT(4)
invCat <-NVARCHAR(50)

invCat is the name of the parent category. What is the name of the other field within the category? If the two fields are different, then I have an idea.

FtB

kevp75

ASKER

there is no other fields, there's the records ID, then parent categories ID, and the category name

parentID relates to icID.

please look at the recordset under the drop dows, you'll catch the relationships

clockwatcher

Can you put that particular table in an access database and post it somewhere? The only code that deals with the database in my post is the LoadFromDB method and glancing I don't see anything wrong with it. Can you modify it to this and post the results?

sub LoadFromDB(conn)
dim rs, cat
set rs = server.createobject("ADODB.Recordset")
rs.open "select icID, parentID, invCat from invCat", conn
response.write "recordset opened<BR>"
do while not rs.eof
set cat = new invcat
cat.id = rs("icID")
cat.parentid = rs("parentID")
cat.category = rs("invCat")
cats.add cat.id, cat
response.write "Added: " & cat.id & "|" & cat.parentid & "|" & cat.category & "<BR>"
rs.movenext
loop
rs.close
set rs = nothing
end sub

clockwatcher

Okay... it's a problem with types. Change these two methods:

sub LoadFromDB(conn)
dim rs, cat
set rs = server.createobject("ADODB.Recordset")
rs.open "select icID, parentID, invCat from invCat", conn
do while not rs.eof
set cat = new invcat
cat.id = rs("icID")
cat.parentid = rs("parentID")
cat.category = rs("invCat")
cats.add clng(cat.id), cat
rs.movenext
loop
rs.close
set rs = nothing
end sub

And

function SelectBox
dim i,out,cat
out = "<select name=""cat"">" & vbcrlf
for i = lbound(sortindex) to ubound(sortindex)
if cats.exists(clng(sortindex(i))) then
set cat = cats(clng(sortindex(i)))
out = out & "<option value=""" & cat.id & """>" & cat.description & vbcrlf
end if
next
out = out & "</select>" & vbcrlf
SelectBox = out
end function

And let me know.

kevp75

ASKER

odd...it works like it should on the test link, however on the production page, it gets outta whack

kevp75

ASKER

nevermind...got it
I just had it in the wrong place...thank you very much!

clockwatcher

Cool... sorry about the first post with the wrong types. Didn't feel like creating a database table to test it. Getting lazy in my old age-- and unless you're 100% positive the database will never go wacky, the BuildLabel method should really have an infinite recursion check. If you're worried about that let me know and I'll add the couple of lines to take care of it.

kevp75

ASKER

actually it works out just great, so far (in production I've only got 4 levels, but I don't see it going any further than that)

thanks again clockwatcher

kevp75

ASKER

@clockwatcher

I'm wondering if you may be able to answer this (here, or would you like a new question? :) )

how can I add another value in:
sub LoadFromDB(conn)
dim rs, cat
set rs = server.createobject("ADODB.Recordset")
rs.open "select icID, parentID, invCat from invCat", conn
do while not rs.eof
set cat = new invcat
cat.id = rs("icID")
cat.parentid = rs("parentID")
cat.category = rs("invCat")
cats.add clng(cat.id), cat
rs.movenext
loop
rs.close
set rs = nothing
end sub

basically what I'm trying to do now is have a default selected value based on a querystring....so, say I pass pCat=12 to the page this is implemented in, it'll automatically select the correct category.

I've tried, well....have a look at this question:
https://www.experts-exchange.com/questions/21924766/Multidimensional-Array-Drop-Down.html

fritz_the_blank

That what my code does, and that is why--as clockwatcher points out--I put a lot of work into it.

If you care to understand how it all works, read my article on the subject: www.fairfieldconsulting.com/codecorner.asp

ftB