Sign in to follow this  
Followers 0
gcue

trying to avoid long code

14 posts in this topic

#1 ·  Posted (edited)

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

Share this post


Link to post
Share on other sites



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?

Share this post


Link to post
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

Share this post


Link to post
Share on other sites

#4 ·  Posted (edited)

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

Share this post


Link to post
Share on other sites

#5 ·  Posted (edited)

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.

Share this post


Link to post
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.

Share this post


Link to post
Share on other sites

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

thank youuuu!

Share this post


Link to post
Share on other sites

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


_AdapterConnections()_AlwaysRun()_AppMon()_AppMonEx()_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 parsingAutoIt SearchAutoIt3 PortableAutoIt3WrapperToPragmaAutoItWinGetTitle()/AutoItWinSetTitle()CodingDirToHTML5FileInstallrFileReadLastChars()GeoIP databaseGUI - Only Close ButtonGUI ExamplesGUICtrlDeleteImage()GUICtrlGetBkColor()GUICtrlGetStyle()GUIEventsGUIGetBkColor()Int_Parse() & Int_TryParse()IsISBN()LockFile()Mapping CtrlIDsOOP in AutoItParseHeadersToSciTE()PasswordValidPasteBinPosts Per DayPreExpandProtect GlobalsQueue()Resource UpdateResourcesExSciTE JumpSettings INISHELLHOOKShunting-YardSignature CreatorStack()Stopwatch()StringAddLF()/StringStripLF()StringEOLToCRLF()VSCROLLWM_COPYDATAMore Examples...

Updated: 04/09/2015

Share this post


Link to post
Share on other sites

BTW , ur thread is very helpfull.

Share this post


Link to post
Share on other sites

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

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

Share this post


Link to post
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

Share this post


Link to post
Share on other sites

#13 ·  Posted (edited)

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

Share this post


Link to post
Share on other sites

ahhh forget about fetchnames!

i was trying to match them up somehow

thanks again dictator!

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

  • Similar Content

    • gcue
      By gcue
      i am trying to pull the members of group A. sometimes group A has a nested group B in as a member. this sometimes carries onto several nested groups *bleh*. i am trying to build an array of members and which parent group they belong to.

      i am trying to find the most efficient way of doing this... here's what I have so far but this only handles up to 3 nested groups. problem is im finding some groups have more than a few nested groups!

      hope this makes sense..

      appreciate the help!

      For $y = 0 To UBound($members) - 1 ;a list of memebers from Group A If $members[$y] = "" Then ContinueLoop $member_count += 1 If StringInStr($members[$y], "@") = 0 And StringInStr($members[$y], "CN=") = 0 Then ;this is how i know if the Group A member is also a group (hence referred to as Group B) $members_2 = GetGroup_Members($members[$y]) ;pulls members of nested Group B For $z = 0 To UBound($members_2) - 1 If $members_2[$z] = "" Then ContinueLoop $member_count += 1 If StringInStr($members_2[$z], "@") = 0 And StringInStr($members_2[$z], "CN=") = 0 Then ;again this is how i know if the Group B member is also a group (hence referred to as Group C) $members_3 = GetGroup_Members($members_2[$z]) ;pulls members of nested Group C (member of Group B) For $a = 0 To UBound($members_3) - 1 If $members_3[$a] = "" Then ContinueLoop $member_count += 1 ReDim $contacts[UBound($contacts) + 1][3] $contacts[UBound($contacts) - 1][0] = $group_name $contacts[UBound($contacts) - 1][1] = $members_3[$a] $contacts[UBound($contacts) - 1][2] = "PUBLIC_GROUP" Next Else ReDim $contacts[UBound($contacts) + 1][3] $contacts[UBound($contacts) - 1][0] = $group_name $contacts[UBound($contacts) - 1][1] = $members_2[$z] $contacts[UBound($contacts) - 1][2] = "PUBLIC_GROUP" EndIf Next Else ReDim $contacts[UBound($contacts) + 1][3] $contacts[UBound($contacts) - 1][0] = $group_name $contacts[UBound($contacts) - 1][1] = $members[$y] $contacts[UBound($contacts) - 1][2] = "PUBLIC_GROUP" EndIf Next