Sign in to follow this  
Followers 0
mlloz

array help

10 posts in this topic

#1 ·  Posted (edited)

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 by mlloz

Share this post


Link to post
Share on other sites



#2 ·  Posted (edited)

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 by UEZ

Please don't send me any personal message and ask for support! I will not reply!

Selection of finest graphical examples at Codepen.io

The own fart smells best!
Her 'sikim hıyar' diyene bir avuç tuz alıp koşma!
¯\_(ツ)_/¯  ٩(●̮̮̃•̃)۶ ٩(-̮̮̃-̃)۶ૐ

Share this post


Link to post
Share on other sites

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..

Posted Image

thanks again

Share this post


Link to post
Share on other sites

#4 ·  Posted (edited)

Try this:

#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 by UEZ

Please don't send me any personal message and ask for support! I will not reply!

Selection of finest graphical examples at Codepen.io

The own fart smells best!
Her 'sikim hıyar' diyene bir avuç tuz alıp koşma!
¯\_(ツ)_/¯  ٩(●̮̮̃•̃)۶ ٩(-̮̮̃-̃)۶ૐ

Share this post


Link to post
Share on other sites

#5 ·  Posted (edited)

I get 89 results.

#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 by czardas

Share this post


Link to post
Share on other sites

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..

Share this post


Link to post
Share on other sites

#7 ·  Posted (edited)

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 by czardas

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

any idea?

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!


Register a new account

Sign in

Already have an account? Sign in here.


Sign In Now
Sign in to follow this  
Followers 0