array help

[mlloz]

hello guys..

i don't know if i'm at the correct subforum, but i need help

im working on small utility to get names from database in sql select statements..

i have 4 fields to get the rows from db, but users may fill 1 or 2 or 3 or 4 fields..

so if the 4 fields are filled, the statement should ends with

WHERE a = field1 and b = field2 and c = field3 and d = field4

if they were 3 then the where statement follow the filled fields e.g a and c and d are filled then the statement should be

WHERE a = field1 and c = field3 and d = field4

all the above i've work on and done..

fields may vary between multiple values, i can use the "IN" statement, but the query takes too long time..

i've thought to split the "IN" statement into "=" statements and then join the results in one result..

my problem is how to do that!!

lets say that i have the following..

2d array that contains

a1,a2

b1

c1,c2

d1,d2,d3,d4

the result should be

a1b1c1d1

a1b1c1d2

a1b1c1d3

a1b1c1d4

a1b1c2d1

a1b1c2d2

a1b1c2d3

a1b1c2d4

a2b1c1d1

a2b1c1d2

a2b1c1d3

a2b1c1d4

a2b1c2d1

a2b1c2d2

a2b1c2d3

a2b1c2d4

i think it is very easy, but i've spent 3 days working and i can't think anymore..

Edited January 22, 2012 by mlloz

[UEZ]

I cannot see the logic how you have created the result array. a1,a2 and c1,c2 are created differently although only two tokens.

Br,

UEZ

Edited January 22, 2012 by UEZ

[mlloz]

UEZ thanks for your reply..

I might haven't clarify my question.l

I'm already have the 2D array and filled with the values like the example in the previous post..

what I want is like the attached picture, but the number of statements may vary according to number of values in each dimension for e.g it may have b1 and b2 with just c1 etc..

thanks again

[UEZ]

Try this:

expandcollapse popup

#include <array.au3>
Global $2D[4][4] = [    ["a1", "a2"], _
                                    ["b1", ""], _
                                    ["c1", "c2"], _
                                    ["d1", "d2", "d3", "d4"]]

$aW = UBound($2D, 2)
$aH = UBound($2D)
Global $aCountElements[$aH]
Global $newArray[$aW * $aH][$aW]
Global $e
For $i = UBound($2D) - 1 To 0 Step - 1
    $e = ""
    $ub = UBound($2D, 2) - 1
    For $j = 0 To $ub
        If $2D[$i][$j] = "" Then ExitLoop
        $e &= $2D[$i][$j] & "|"
    Next
    If $e <> "" Then FillArray($newArray, $e, $i)
Next
_ArrayDisplay($newArray)

Func FillArray(ByRef $array, $tokens, $column)
    Local $aTmp = StringSplit(StringLeft($tokens, StringLen($tokens) - 1), "|", 2)
    Local $z = 0
    Local $ub = UBound($array)
    Local $s = $ub / UBound($aTmp)
    Local $ub2 = UBound($aTmp)

    Local Static $prev = 0
    If $column Then
        If Not $prev Then
            Do
                $array[$z][$column] = $aTmp[Mod($z, $ub2)]
                $z += 1
            Until $z = UBound($array)
            $prev = $ub2
        Else
            Do
                $array[$z][$column] = $aTmp[Mod(($z / $prev), $ub2)]
                $z += 1
            Until $z = UBound($array)
            $prev = $ub2
        EndIf
    Else
        Do
            $array[$z][$column] = $aTmp[Mod($z / $s, $ub2)]
            $z += 1
        Until $z = UBound($array)
    EndIf
EndFunc

I don't know whether this code still works properly creating the binary tree if you vary the 2d array.

Br,

UEZ

Edited January 23, 2012 by UEZ

[czardas]

I get 89 results.

expandcollapse popup

#include <Array.au3>

Local $aArray[4][5] = [[2,"a1","a2"],[1,"b1"],[2,"c1","c2"],[4,"d1","d2","d3","d4"]]

Local $aChoices = _GetChoices($aArray)
;_ArrayDisplay($aChoices)

$aReturn = _GetCombinations($aChoices)
_ArrayDisplay($aReturn)

Func _GetChoices($array)
    Local $aTemp[16], $iCount = 0
    For $i = 1 To $aArray[0][0]
        For $j = 1 To $aArray[1][0]
            For $k = 1 To $aArray[2][0]
                For $l = 1 To $aArray[3][0]
                    $aTemp[$iCount] = $aArray[0][$i] &","& $aArray[1][$j] &","& $aArray[2][$k] &","& $aArray[3][$l]
                    $iCount += 1
                Next
            Next
        Next
    Next
    Return $aTemp
EndFunc

Func _GetCombinations($array)
    Local $aTemp, $aArrayCombo, $aRet[240], $iCount = 0
    For $i = 0 To 15
        $aTemp = StringSplit($array[$i], ",", 2)
        For $j = 1 To UBound($aTemp)
            $aArrayCombo = _ArrayCombinations($aTemp, $j, ",")
            For $k = 1 To $aArrayCombo[0]
                $aRet[$iCount] = $aArrayCombo[$k]
                $iCount += 1
            Next
        Next
    Next
    $aRet = _ArrayUnique($aRet)
    _ArraySort($aRet, 0, 1)
    Return $aRet
EndFunc

Improvements can be made to the above method. I may look at it again later, time permitting.

Edited January 23, 2012 by czardas

[mlloz]

thanks UEZ and czardas

UEZ, my case that the 2D array always vary..

I tried your code and I've got exactly what I want in the given example, is there another way to do that instead of 2D array if it is not possible?

thanks in advance..

[czardas]

I'm not sure if I fully understood your question. The code I posted brings back every possible combination given the choices you mentioned. I think arrays are the best choice for this, however you could store the information in separate one dimensional arrays. If the choices were to follow a direct path always starting with a1 (or a2) followed by b1, c? d? etc... then it could be useful to add more dimensions to the array.

Edit

I just removed an unneeded line of code left over from an earlier edit. I hope these examples are helpful.

Edited January 23, 2012 by czardas

[mlloz]

you know its an sql statements, it can't be any combination of values, it must have all the filled values in the array>

and i can store data in multiple arrays

[czardas]

if they were 3 then the where statement follow the filled fields e.g a and c and d are filled then the statement should be

WHERE a = field1 and c = field3 and d = field4

I figured I was missing some information. It was the above statement that confused me and I haven't done much with sql.

[mlloz]

any idea?