Jump to content

trying to avoid long code


gcue
 Share

Recommended Posts

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 by gcue
Link to comment
Share on other sites

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

Link to comment
Share on other sites

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 by DicatoroftheUSA
Link to comment
Share on other sites

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 by jdelaney
IEbyXPATH-Grab IE DOM objects by XPATH IEscriptRecord-Makings of an IE script recorder ExcelFromXML-Create Excel docs without excel installed GetAllWindowControls-Output all control data on a given window.
Link to comment
Share on other sites

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.
Link to comment
Share on other sites

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

UDF List:

 
_AdapterConnections() • _AlwaysRun() • _AppMon() • _AppMonEx() • _ArrayFilter/_ArrayReduce • _BinaryBin() • _CheckMsgBox() • _CmdLineRaw() • _ContextMenu() • _ConvertLHWebColor()/_ConvertSHWebColor() • _DesktopDimensions() • _DisplayPassword() • _DotNet_Load()/_DotNet_Unload() • _Fibonacci() • _FileCompare() • _FileCompareContents() • _FileNameByHandle() • _FilePrefix/SRE() • _FindInFile() • _GetBackgroundColor()/_SetBackgroundColor() • _GetConrolID() • _GetCtrlClass() • _GetDirectoryFormat() • _GetDriveMediaType() • _GetFilename()/_GetFilenameExt() • _GetHardwareID() • _GetIP() • _GetIP_Country() • _GetOSLanguage() • _GetSavedSource() • _GetStringSize() • _GetSystemPaths() • _GetURLImage() • _GIFImage() • _GoogleWeather() • _GUICtrlCreateGroup() • _GUICtrlListBox_CreateArray() • _GUICtrlListView_CreateArray() • _GUICtrlListView_SaveCSV() • _GUICtrlListView_SaveHTML() • _GUICtrlListView_SaveTxt() • _GUICtrlListView_SaveXML() • _GUICtrlMenu_Recent() • _GUICtrlMenu_SetItemImage() • _GUICtrlTreeView_CreateArray() • _GUIDisable() • _GUIImageList_SetIconFromHandle() • _GUIRegisterMsg() • _GUISetIcon() • _Icon_Clear()/_Icon_Set() • _IdleTime() • _InetGet() • _InetGetGUI() • _InetGetProgress() • _IPDetails() • _IsFileOlder() • _IsGUID() • _IsHex() • _IsPalindrome() • _IsRegKey() • _IsStringRegExp() • _IsSystemDrive() • _IsUPX() • _IsValidType() • _IsWebColor() • _Language() • _Log() • _MicrosoftInternetConnectivity() • _MSDNDataType() • _PathFull/GetRelative/Split() • _PathSplitEx() • _PrintFromArray() • _ProgressSetMarquee() • _ReDim() • _RockPaperScissors()/_RockPaperScissorsLizardSpock() • _ScrollingCredits • _SelfDelete() • _SelfRename() • _SelfUpdate() • _SendTo() • _ShellAll() • _ShellFile() • _ShellFolder() • _SingletonHWID() • _SingletonPID() • _Startup() • _StringCompact() • _StringIsValid() • _StringRegExpMetaCharacters() • _StringReplaceWholeWord() • _StringStripChars() • _Temperature() • _TrialPeriod() • _UKToUSDate()/_USToUKDate() • _WinAPI_Create_CTL_CODE() • _WinAPI_CreateGUID() • _WMIDateStringToDate()/_DateToWMIDateString() • Au3 script parsing • AutoIt Search • AutoIt3 Portable • AutoIt3WrapperToPragma • AutoItWinGetTitle()/AutoItWinSetTitle() • Coding • DirToHTML5 • FileInstallr • FileReadLastChars() • GeoIP database • GUI - Only Close Button • GUI Examples • GUICtrlDeleteImage() • GUICtrlGetBkColor() • GUICtrlGetStyle() • GUIEvents • GUIGetBkColor() • Int_Parse() & Int_TryParse() • IsISBN() • LockFile() • Mapping CtrlIDs • OOP in AutoIt • ParseHeadersToSciTE() • PasswordValid • PasteBin • Posts Per Day • PreExpand • Protect Globals • Queue() • Resource Update • ResourcesEx • SciTE Jump • Settings INI • SHELLHOOK • Shunting-Yard • Signature Creator • Stack() • Stopwatch() • StringAddLF()/StringStripLF() • StringEOLToCRLF() • VSCROLL • WM_COPYDATA • More Examples...

Updated: 22/04/2018

Link to comment
Share on other sites

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

any ideas?

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
Link to comment
Share on other sites

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 by DicatoroftheUSA
Link to comment
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
 Share

×
×
  • Create New...