> Sure, I'm interested :-)
OK...by "popular" demand is my down-and-dirty script for converting a user-typed
search string into basic SQL. I threw in some very basic comments and modified
the handler so it is more general; give it your own table and column names as
parameters.
I'm sure it would be easy to screw up this handler by tossing it garbage, but it
seems to work OK for basic stuff like:
human and "bone loss"
john or cindy
etc.
-- Parameters:
-- searchString : what the user typed
-- table : the name of the table to search
-- column : the name of the column to search
-- Returns: a list whose first element is the SQL and whose second element is
the first "component" of the
-- search, such as the first word or the first quoted phrase (I use this later
for frequency assessment)
on convertSearchTextToSQL searchString, table, column
-- Remove "or" or "and" if either first or last word
if searchString.word[1] = "or" or searchString.word[1] = "and" then
delete word 1 of searchString
end if
if searchString.word[searchString.words.count] = "or" or
searchString.word[searchString.words.count] = "and" then
delete the last word of searchString
end if
wordCount = searchString.words.count
-- "components" are the items that make up the search, such as words or
phrases
components = []
-- lookForQuote is set to 1 when we encounter a quote and start looking for
its match
lookForQuote = 0
componentBuild = ""
repeat with x = 1 to wordCount
currWord = searchString.word[x]
if lookForQuote = 1 then
-- looking for closing quote
put " " & currWord after componentBuild
if currWord.char[currWord.chars.count] = quote then
-- Found a closing quote
delete the last char of componentBuild
append components, componentBuild
lookForQuote = 0
componentBuild = ""
next repeat
else
-- no closing quote on this one
-- are we at the end?
if x = wordCount then
-- last word
append components, componentBuild
end if
next repeat
end if
else
-- Not looking for closing quote
-- is it a special word?
if currWord = "or" OR currWord = "and" then
append components, currWord
next repeat
end if
if currWord.char[1] = quote then
-- start looking for the next quote, to close the component
-- maybe it's on the same word?
if currWord.char[currWord.chars.count] = quote then
-- just one word was quoted
append components, currWord
next repeat
else
-- the current word doesn't have an end quote
componentBuild = currWord
delete char 1 of componentBuild
lookForQuote = 1
next repeat
end if
end if
-- if here, then just a regular word
append components, currWord
end if
end repeat
compCount = components.count
SQL = ""
-- Go through the components and build the SQL string
repeat with currComp = 1 to compCount
currCompText = components[currComp]
-- the following line requires textCruncher Xtra; could be modified to do
without
currCompText = replaceAll(currCompText, "\'", "'")
case currCompText of
"or":
nextPiece = " OR "
"and":
nextPiece = " AND "
otherwise:
nextPiece = "(" & column & " like '" & currCompText & "')"
end case
-- Add "and" by default between any two pieces of SQL
-- if there is nothing else connecting them
if SQL.char[SQL.chars.count] = ")" AND not(nextPiece = " AND ") and
not(nextPiece = " OR ") then
SQL = SQL & " AND " & nextpiece
else
SQL = SQL & " " & nextPiece
end if
end repeat
if SQL = "" then
SQL = "SELECT * FROM " & table
else
put "SELECT * FROM " & table & " WHERE" before SQL
end if
return list(SQL, components[1])
end
-SR
Scott Runkel
Director of Technology
503-722-2123
_______________________________________________
Valentina mailing list
Valentina (at) lists DO.T macserve.net
http://lists.macserve.net/mailman/listinfo/valentina
©2003 Scott Runkel |
