Function SPLIT2(ByVal AA, ByVal SS)
Dim cc As String
bb = Nothing
bb = Split(AA, SS)
aa2 = Nothing
aa2 = {"newsplittedarray"}
ii = 0
For Each cc In bb
If cc <> "" Then
ReDim Preserve aa2(ii)
aa2(ii) = cc
ii = ii + 1
End If
Next
SPLIT2 = aa2
End Function
then with a few array that contain the column name for every database used in the program:
'=== ssea = search string
'=== tab 0 symptoms
'=== tab 1 components
'=== tab 2 diagnostics
'=== tab 3 causes
'=== ttyp 0 search
'=== ttyp 1 fast results
'=== ttyp 2 detail results
'=== ssufix = order by or something at the end of query - order and %
Function makque( _
ByVal ssea As String, _
ByVal ttab As Integer, _
ByVal sselect As Integer, _
ByVal wwhere As Integer, _
ByVal ssufix As String, _
ByVal ooper As String, _
ByVal wwild As String)
Dim ss As String
Dim aa As String
ssea = Replace(ssea, "'", "''")
ssea = Trim(ssea)
sseaara = Split(ssea, " ") '=== array of all words for search
ss = "select "
ii = 0
'=== columns to search in
For Each aa In allara(ttab, sselect)
ss = ss & aa
If ii <> UBound(allara(ttab, 1), sselect) Then ss = ss & ","
ii = ii + 1
Next
'=== from table
ss = ss & " from [" & alltab(ttab) & "] "
If Len(ssea) <> 0 Then
ss = ss & "where ("
'=== search word(S) in table, AND operator
ii = 0
For Each ss2 In sseaara
ii2 = 0
For Each aa In allara(ttab, wwhere)
If wwild = "" Then
ss = ss & "[" & aa & "] " & ooper & " " & wwild & ss2 & wwild
Else
ss = ss & "[" & aa & "] " & ooper & " '" & wwild & ss2 & wwild & "'"
End If
If ii2 <> UBound(allara(ttab, wwhere), 1) Then ss = ss & " or "
ii2 = ii2 + 1
Next
If ii <> UBound(sseaara, 1) Then ss = ss & ") and ("
ii = ii + 1
Next
'=== order by a column name
ss = ss & ")" & ssufix
Else
ss = ss & ssufix
End If
makque = ss
End Function
This will result in building a query, with all column name, where all words are searched for
a "dumb google" (not searching for most found etc, just the words in all columns we want to search in)
Just before, we call all thoses subs with a value we get from a html box, in form1 web object:
'=== symptom words to search (separated by a space)
TAB = 0 '=== table to seach in (alltab)
sel = 1 '=== columns to list (allara)
whe = 0 '=== columns to search in (Allara)
sql01 = makque(strsea, TAB, sel, whe, " order by [probabilité] DESC", "LIKE", "%")
If logall = 1 Then fil02.writeline(DateValue(Now) & " " & TimeValue(Now) & " before executing query")
tag = exesql(objcon, dummy, sql01)
as you can see (and i saw it too!) i do not split the search string for symptom
because i search for all words, all together
objcon is my sql or mdb connection
dummy is a dummy :P
sql01 is the query text
strsea is the text to search for in all columns
% is the caracter used to start and end a chain to search for the words anywhere in the chain of text in the column
i numbered my array for each table, it look complicated but is so more simple to program
again, theses numbers allow me to add an array containing all columns name anytime
thus, adding a table in the database dynamically in my program, and manually in the database :)
thoses are specials arrays containing only the column names i want to search in
'=== search columns
allara(0, 0) = {"symptôme", "problème"}
'=== fast results (for table)
I am not going into details right now because the goal in this post is to show a dynamic query building
No comments:
Post a Comment