trying to avoid long code

[gcue]

hello world.

so im trying to build something that searches a SQLite database. there are multiple search fields and i'd like to support the sql wild card "%"

the method im using is making this very very long - probably can be done much shorter/efficient

if $field1 <> "" and $field2 = "" and $field3 = "" and $field4 = "" then
     _SQLite_QuerySingleRow(-1, "SELECT field2,field3,field4 WHERE field1='" & $field1_input & "'", $aRow)
endif

that in itself will take several lines considering all the combinations and even more so when I consider the wildcards which changes the conditions AND sqlquery also (LIKE "%field_input")

there's gotta be an efficient way to do this...

thanks in advance

Edited September 20, 2012 by gcue

[Spiff59]

I'm confused. What you have now is the most efficient query possible.

Where do you want to take it?

Maybe fill in a bit more on what the very long code would look like?

[gcue]

it would look like this:

;first field is selected in all scenarios
if $field1 <> "" and $field2 = "" and $field3 = "" and $field4 = "" and $field5 = "" then
     _SQLite_QuerySingleRow(-1, "SELECT field2,field3,field4 WHERE field1='" & $field1_input & "'", $aRow)
endif
 
if $field1 <> "" and $field2 <> "" and $field3 = "" and $field4 = "" and $field5 = "" then
     _SQLite_QuerySingleRow(-1, "SELECT field2,field3,field4 WHERE field1='" & $field1_input & "'", $aRow)
endif
 
 if $field1 <> "" and $field2 <> "" and $field3 <> "" and $field4 = "" and $field5 = "" then
     _SQLite_QuerySingleRow(-1, "SELECT field2,field3,field4 WHERE field1='" & $field1_input & "'", $aRow)
 endif
 
 if $field1 <> "" and $field2 <> "" and $field3 <> "" and $field4 <> "" and $field5 <> "" then
     _SQLite_QuerySingleRow(-1, "SELECT field2,field3,field4 WHERE field1='" & $field1_input & "'", $aRow)
 endif
 
 ;first field is not selected in all scenarios
 if $field1 = "" and $field2 <> "" and $field3 = "" and $field4 = "" and $field5 = "" then
     _SQLite_QuerySingleRow(-1, "SELECT field2,field3,field4 WHERE field1='" & $field1_input & "'", $aRow)
 endif
 
 if $field1 = "" and $field2 <> "" and $field3 <> "" and $field4 = "" and $field5 = "" then
     _SQLite_QuerySingleRow(-1, "SELECT field2,field3,field4 WHERE field1='" & $field1_input & "'", $aRow)
 endif
 
 if $field1 = "" and $field2 <> "" and $field3 <> "" and $field4 <> "" and $field5 = "" then
     _SQLite_QuerySingleRow(-1, "SELECT field2,field3,field4 WHERE field1='" & $field1_input & "'", $aRow)
 endif
 
 if $field1 = "" and $field2 <> "" and $field3 <> "" and $field4 <> "" and $field5 <> "" then
     _SQLite_QuerySingleRow(-1, "SELECT field2,field3,field4 WHERE field1='" & $field1_input & "'", $aRow)
 endif

after all those are done - i have to consider the wild card scenarios

if $field1 <> "" and StringInStr($field2, "%") <> 0 and $field3 = "" and $field4 = "" and $field5 = "" then
     _SQLite_QuerySingleRow(-1, "SELECT field2,field3,field4 WHERE field1='" & $field1_input & "'", $aRow)
 endif
 
 if $field1 <> "" and StringInStr($field2, "%") <> 0 and StringInStr($field3, "%") <> 0 and $field4 = "" and $field5 = "" then
     _SQLite_QuerySingleRow(-1, "SELECT field2,field3,field4 WHERE field1='" & $field1_input & "'", $aRow)
 endif

sooo many possibilities - hard to even make sure i get them all

[DicatoroftheUSA]

I would load the command into strings

sort of like the following (psuedocode)

Also I would use normal queries, and use limits that return a single row, rather than using the QuerySingleRow function .

$cmd="Select "
for $i=0 to $fieldcount
if field [$i]<>"" then
$sCMD&="field,"&$i
endif
next

$cmd= " where "

for $i=0 to $fieldcount
if field [$i]<>"" then
_convertwilcard($field[$i])
if $i>1 then
$sCMD&=" and "
$sCMD&="field,"&$i&" like "'&$field[$i]'"
endif
next
$sCMD&=";"

consolewrite($sCMD&@lf)
_SQLite_QuerySingleRow(-1, $sCMD, $aRow)

func _convertwildcard(byref $sString)
$sString=stringreplace($sString,"*","%")
endfunc

Edited September 20, 2012 by DicatoroftheUSA

[jdelaney]

why not create an 2d array, and then just loop through it...then you can conditionally look through for any number of fields, and setup the different queries much more easily...something like [[columnname,value],[etc,etc]]

maybe even add in a 3rd property to reduce the logic you need...such as [[columnname,operatortype,value],[etc,etc,etc]] where operatortype is [like|in|=]

Edited September 20, 2012 by jdelaney

[DicatoroftheUSA]

why not create an 2d array, and then just loop through it...then you can conditionally look through for any number of fields, and setup the different queries much more easily...something like [[columnname,value],[etc,etc]]

maybe even add in a 3rd property to reduce the logic you need...such as [[columnname,operatortype,value],[etc,etc,etc]] where operatortype is [like|in|=]

Sqlite, can be persistent without using excessive memory, and faster with large data-sets. It depends on what he is trying to accomplish.

[gcue]

brilliant dictator.. absolutely brilliant - playing with it now.. looking good so far

thank youuuu!

[DicatoroftheUSA]

Your Welcome.

Check out this page to see how to structure your wildcard function

http://sqlite.awardspace.info/syntax/sqlitepg03.htm

Edited September 20, 2012 by DicatoroftheUSA

[guinness]

@OffTopic, did you see my post in your SchTasks thread?

[hamza]

BTW , ur thread is very helpfull.

[gcue]

@OffTopic, did you see my post in your SchTasks thread?

i hadn't but just checked and responded - thanks!!

[gcue]

i am having trouble matching up the query results to the corresponding fields

any ideas?

expandcollapse popup

Local $fields[5][3]

        $fields[0][0] = "field1"
        $fields[0][1] = $field1
        $fields[0][2] = ""

        $fields[1][0] = "field2"
        $fields[1][1] = $field2
        $fields[1][2] = ""

        $fields[2][0] = "field3"
        $fields[2][1] = ""
        $fields[2][2] = "NOT_DEFINED"

        $fields[3][0] = "field4"
        $fields[3][1] = $field4
        $fields[3][2] = ""

        $fields[4][0] = "field5"
        $fields[4][1] = ""
        $fields[4][2] = "NOT_DEFINED"

        $query = "Select "

        For $i = 0 To UBound($fields) - 1
            If $fields[$i][1] <> "" And $fields[$i][1] <> "%" And $fields[$i][2] <> "NOT_DEFINED" Then
                $fields[$i][2] = "DEFINED"
            Else
                $fields[$i][2] = "NOT_DEFINED"
                $query &= $fields[$i][0] & ","
            EndIf
        Next

        $query = StringTrimRight($query, 1)

        $query &= " FROM aTable WHERE "

        For $i = 0 To UBound($fields) - 1
            If $fields[$i][2] = "DEFINED" Then
                If StringInStr($fields[$i][1], "%") <> 0 Then
                    $query &= $fields[$i][0] & " LIKE '" & $fields[$i][1] & "' AND "
                Else
                    $query &= $fields[$i][0] & "='" & $fields[$i][1] & "' AND "
                EndIf
            EndIf
        Next

        $query = StringTrimRight($query, 5)

            _SQLite_Query(-1, $query, $hQuery)
            While _SQLite_FetchData($hQuery, $aRow) = $SQLITE_OK
;??????????
            WEnd

    EndIf

[DicatoroftheUSA]

I don't understand what you are asking for

Are you looking for the following?

_SQlite_Query (-1, $query, $hQuery)

_SQLite_FetchNames ($hQuery, $aNames)

for $i=0 to ubound ($aNames)-1
consolewrite($aNames[$i]&@tab)
next

consolewrite(@lf)


while _SQLite_FetchData($hQuery, $aRow) = $SQLITE_OK
for $i=0 to ubound ($aRow)-1
consolewrite($aRow[$i]&@tab)
next

consolewrite(@lf)

WEnd

Edited September 21, 2012 by DicatoroftheUSA

[gcue]

ahhh forget about fetchnames!

i was trying to match them up somehow

thanks again dictator!