Search Tools Links Login

Building a SQL Search Query--Easily


This article explains an alternative to the boring string parse/loop method of building a search query for your website.

Original Author: Primoris Software

Code


Building a Better Query


What kind of Query
does this apply to?


 


This
tutorial is meant to cover the type of queries where someone is running a
single or multiple keyword search on your web site (or, I suspect this could be
used in software development as well, though I haven't used it yet).style="mso-spacerun: yes">?


For example, you have a knowledge base, and a customer is
looking up information on a printing bug.?
So they go to your "Search" field and type "print bug
epson".? Sound easy?style="mso-spacerun: yes">? You're not saying, 'Oh, no problem, the query
would look like SELECT * FROM TABLE WHERE field LIKE '%" & textbox
& "%'"', are you?? The
problem is, people many times type their keywords in an order different than
that found in your knowledge base, and certainly there's the possibility of
other words between the keywords.


So
how do you overcome this?? One solution
(widely used) is to use a loop.? Parse
all of the words with a commonly used parsing function.style="mso-spacerun: yes">?
Sure, you can download one from one of the
free code places on the web, but then you have to find it and figure out how to
use it.? You can write it yourself, but
I promise you it is one of the most boring pieces of code you will ever write.


After
all of the keywords are parsed into an array, build your query by looping
through all of them.? Fast?style="mso-spacerun: yes">?
Easy??
Efficient?? Kind of.style="mso-spacerun: yes">? But here's a better, more organized way.


 


The Alternative
Way to Build the Query


 


The
alternative comes in two parts.? Step
one is to remove all extraneous white space from the string.style="mso-spacerun: yes">?
It probably
doesn't have any, but you never know.?
So, do this on the client side with a function that loops something
like,


 


style='font-size:7.0pt;mso-bidi-font-size:10.0pt;font-family:"Courier New";
mso-bidi-font-family:"Times New Roman"'>????????? while
(strObj.search("? ") != -1)


style='font-size:7.0pt;mso-bidi-font-size:10.0pt;font-family:"Courier New";
mso-bidi-font-family:"Times New Roman"'>????????? {


style='font-size:7.0pt;mso-bidi-font-size:10.0pt;font-family:"Courier New";
mso-bidi-font-family:"Times New Roman"'>?????????????????? strObj
= strObj.replace("? ","
");


style='font-size:7.0pt;mso-bidi-font-size:10.0pt;font-family:"Courier New";
mso-bidi-font-family:"Times New Roman"'>????????? }


style='font-size:7.0pt;mso-bidi-font-size:10.0pt;font-family:"Courier New";
mso-bidi-font-family:"Times New Roman"'>????????? return
strObj.toString()


style='font-size:8.0pt;mso-bidi-font-size:10.0pt;font-family:"Courier New";
mso-bidi-font-family:"Times New Roman"'> 


In
case you didn't catch it, in the while condition there is a string with two
spaces, and in the replace function, the string with two spaces is replaced
with one.? Until there is only one space
between (or in front or behind of) every keyword, keep stripping it down.style="mso-spacerun: yes">?


Now
you have a nicely organized string of keywords with one space max between,
before, or after the words.? On the
search page, you may also have conditions like "case sensitive",
matches per page, etc, but we won't go over that here (if you want me to cover
that at some point, simply send me a note and I'll try to do it).


Pass this string to your next active server page.style="mso-spacerun: yes">? For me, did you notice that I returned the
string from a function in the code above??
I stuck that return value back in the original text box.style="mso-spacerun: yes">? It's up to you to decide how you want to
pass the string back.


Either
way, now the ASP has to build the search string.? Here's the good partstep two.?
Now that your string has only single spaces, you can strip the first and
last spaces, and run a replace on all of the remaining.style="mso-spacerun: yes">?
Let's build this step by step (the way I do
on my page).


 


style='font-size:7.0pt;mso-bidi-font-size:10.0pt;font-family:"Courier New";
mso-bidi-font-family:"Times New Roman"'>Function buildSQL( strText )


style='font-size:7.0pt;mso-bidi-font-size:10.0pt;font-family:"Courier New";
mso-bidi-font-family:"Times New Roman"'>????????? Dim
selectClause


style='font-size:7.0pt;mso-bidi-font-size:10.0pt;font-family:"Courier New";
mso-bidi-font-family:"Times New Roman"'>????????? Dim
fromClause


style='font-size:7.0pt;mso-bidi-font-size:10.0pt;font-family:"Courier New";
mso-bidi-font-family:"Times New Roman"'>????????? Dim
whereClause


style='font-size:7.0pt;mso-bidi-font-size:10.0pt;font-family:"Courier New";
mso-bidi-font-family:"Times New Roman"'>??????????????????


style='font-size:7.0pt;mso-bidi-font-size:10.0pt;font-family:"Courier New";
mso-bidi-font-family:"Times New Roman"'>????????? selectClause
= "SELECT? * "


style='font-size:7.0pt;mso-bidi-font-size:10.0pt;font-family:"Courier New";
mso-bidi-font-family:"Times New Roman"'>????????? fromClause
= "FROM table "


style='font-size:7.0pt;mso-bidi-font-size:10.0pt;font-family:"Courier New";
mso-bidi-font-family:"Times New Roman"'>????????? whereClause
= "WHERE "


style='font-size:7.0pt;mso-bidi-font-size:10.0pt;font-family:"Courier New";
mso-bidi-font-family:"Times New Roman"'>????????? Select
Case Request.QueryString("optAll")


style='font-size:7.0pt;mso-bidi-font-size:10.0pt;font-family:"Courier New";
mso-bidi-font-family:"Times New Roman"'>?????????????????? Case
1 ' Match ALL keywords


style='font-size:7.0pt;mso-bidi-font-size:10.0pt;font-family:"Courier New";
mso-bidi-font-family:"Times New Roman"'>?????????????????? style="mso-spacerun: yes">??? whereClause = whereClause & "
(field LIKE '%" & _


style='font-size:7.0pt;mso-bidi-font-size:10.0pt;font-family:"Courier New";
mso-bidi-font-family:"Times New Roman"'>Replace( Trim( strText ), "
", "%' AND field LIKE '%") & "%')"


style='font-size:7.0pt;mso-bidi-font-size:10.0pt;font-family:"Courier New";
mso-bidi-font-family:"Times New Roman"'>?????????????????? style="mso-spacerun: yes">? ??????? Case
2 ' Match ANY keywords


style='font-size:7.0pt;mso-bidi-font-size:10.0pt;font-family:"Courier New";
mso-bidi-font-family:"Times New Roman"'>?????????????????? whereClause
= whereClause & " (field LIKE '%" & _


style='font-size:7.0pt;mso-bidi-font-size:10.0pt;font-family:"Courier New";
mso-bidi-font-family:"Times New Roman"'>Replace( Trim( strText ), "
", "%' OR field LIKE '%") & "%')"


style='font-size:7.0pt;mso-bidi-font-size:10.0pt;font-family:"Courier New";
mso-bidi-font-family:"Times New Roman"'>????????? End
Select


style='font-size:7.0pt;mso-bidi-font-size:10.0pt;font-family:"Courier New";
mso-bidi-font-family:"Times New Roman"'>????????? 'Response.Write(
selectClause & fromClause & whereClause )


style='font-size:7.0pt;mso-bidi-font-size:10.0pt;font-family:"Courier New";
mso-bidi-font-family:"Times New Roman"'>????????? buildSQL
= selectClause & fromClause & whereClause


style='font-size:7.0pt;mso-bidi-font-size:10.0pt;font-family:"Courier New";
mso-bidi-font-family:"Times New Roman"'>End Function


 


style='font-size:8.0pt;mso-bidi-font-size:10.0pt'>Sostyle='font-size:8.0pt;mso-bidi-font-size:10.0pt'> you're replacing all of the
middle spaces with a SQL 'and' statement.?
In plain English, if your search phrase is "print bug", this
now becomes "'%print%' AND field LIKE '%bug%'" when you concatenate
the leading and trailing %'s and quotes (this is for Microsoft Access drivers,
other drivers may use different wildcards)--so just append this phrase to the
"WHERE field LIKE " phrase, and you're in business.style="mso-spacerun: yes">? I've built gigantic search phrases with this
method before with little coding, and little server load.


Voila!style="mso-spacerun: yes">? An instant search query!style="mso-spacerun: yes">? No tiresome string parsing or looping.style="mso-spacerun: yes">? One final question you may have is,
"what if the user separates the keywords with commas or hyphens or...".style="mso-spacerun: yes">? No problem!?
Just put client-side code in to convert all hyphens, commas, etc. to
white space.? Put this style='mso-bidi-font-style:normal'>before the function that strips the
white-space down to one.? String:
Normalized.? So that's how it's
done.? If you have any questions, I
would be happy to explain further--just send me a note on Planet Source Code.


 


 



About this post

Posted: 2002-06-01
By: ArchiveBot
Viewed: 88 times

Categories

ASP/ HTML

Attachments

No attachments for this post


Loading Comments ...

Comments

No comments have been added for this post.

You must be logged in to make a comment.