I should have explained it like this....

[Bob Barrows]

Darren Heinrich wrote:

> Maybe I should have explained it like this:
> In one record in my db I have:
>
> field1 = "the dog"
> field2 = "the cat"
> field3 = "the bird"
>
> I need a query that will find the above record when the user enters
> the keywords of "dog cat bird"
> so far my current page works well but it looks for "dog cat bird" in
> one field, then the next fieldetc.
>
> Have a look: [url]http://www.dazzjazz.com/tmc/form.asp[/url]
>
> Here is a example of the current dynamically built query
> --------------------------------------------------------------------------

--

> -----------------------------------
> Select tblProducts.productID, tblProducts.productName,
> tblProducts.longDescription, tblProducts.description,
> tblManufacturers.manufacturerName FROM tblManufacturers INNER JOIN
> tblProducts ON tblManufacturers.manufacturerId =
> tblProducts.manufacturerId WHERE tblProducts.productName Like
> '%digital%24bit%' OR tblProducts.longDescription Like
> '%digital%24bit%' OR tblProducts.description Like '%digital%24bit%' OR
> tblManufacturers.manufacturerName Like '%digital%24bit%' ORDER BY
> productID
> --------------------------------------------------------------------------

--

> -----------------------------------
> thanks for your help.
>
> Darren

See my reply to David in .asp.general in the topic "boolean search
expressions"

Bob Barrows.


--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"

[Darren Heinrich]

Maybe I should have explained it like this:
In one record in my db I have:

field1 = "the dog"
field2 = "the cat"
field3 = "the bird"

I need a query that will find the above record when the user enters the
keywords of "dog cat bird"
so far my current page works well but it looks for "dog cat bird" in one
field, then the next fieldetc.

Have a look: [url]http://www.dazzjazz.com/tmc/form.asp[/url]

Here is a example of the current dynamically built query
----------------------------------------------------------------------------
-----------------------------------
Select tblProducts.productID, tblProducts.productName,
tblProducts.longDescription, tblProducts.description,
tblManufacturers.manufacturerName FROM tblManufacturers INNER JOIN
tblProducts ON tblManufacturers.manufacturerId = tblProducts.manufacturerId
WHERE tblProducts.productName Like '%digital%24bit%' OR
tblProducts.longDescription Like '%digital%24bit%' OR
tblProducts.description Like '%digital%24bit%' OR
tblManufacturers.manufacturerName Like '%digital%24bit%' ORDER BY productID
----------------------------------------------------------------------------
-----------------------------------
thanks for your help.

Darren

[Aaron Bertrand [MVP]]

Why don't you make them choose whether they're interested in a product name,
or a manufacturer name, etc.?

Anyway, rather than treat the whole set of words as one word, you can do
something like this. (Given that I have no idea what you called your form
field name or which collection it comes from.)

<%
words = split(request.form("words"))
for i = 0 to ubound(words)
j = " OR "
if i = 0 then j = ""
w = replace(words(i), "'", "''")
sql = sql & j & " p.productName LIKE '%" & w & "%'" & _
" OR p.longDescription LIKE '%" & w & "%'" & _
" OR p.description LIKE '%" & w & "%'" & _
" OR m.manufacturerName LIKE '%" & w & "%'"
next

' I find your naming scheme funny and inconsistent. why
' not p.productLongDescription? and what purpose does
' the tbl prefix serve?

sql = "SELECT p.productID, p.productName, p.description, " & _
" m.manufacturerName, p.longDescription " & _
" FROM tblManufactuerers m INNER JOIN " & _
" tblProducts p ON m.manufacturerID = p.manufacturerID " & _
" WHERE " & sql

response.write(sql)
%>

And, as mentioned before, you might consider full text search. This query
is very likely to have absolutely horrible performance, unless your tables
have 3 or 4 rows and users are always entering the same search phrases.

--
Aaron Bertrand
SQL Server MVP
[url]http://www.aspfaq.com/[/url]




"Darren Heinrich" <dazzjazz@ozemail.com.au> wrote in message
news:J8zVb.11$cW2.589@nnrp1.ozemail.com.au...

> Maybe I should have explained it like this:
> In one record in my db I have:
>
> field1 = "the dog"
> field2 = "the cat"
> field3 = "the bird"
>
> I need a query that will find the above record when the user enters the
> keywords of "dog cat bird"
> so far my current page works well but it looks for "dog cat bird" in one
> field, then the next fieldetc.
>
> Have a look: [url]http://www.dazzjazz.com/tmc/form.asp[/url]
>
> Here is a example of the current dynamically built query
> --------------------------------------------------------------------------

--

> -----------------------------------
> Select tblProducts.productID, tblProducts.productName,
> tblProducts.longDescription, tblProducts.description,
> tblManufacturers.manufacturerName FROM tblManufacturers INNER JOIN
> tblProducts ON tblManufacturers.manufacturerId =

tblProducts.manufacturerId

> WHERE tblProducts.productName Like '%digital%24bit%' OR
> tblProducts.longDescription Like '%digital%24bit%' OR
> tblProducts.description Like '%digital%24bit%' OR
> tblManufacturers.manufacturerName Like '%digital%24bit%' ORDER BY

productID

> --------------------------------------------------------------------------

--

> -----------------------------------
> thanks for your help.
>
> Darren
>
>

[Aaron Bertrand [MVP]]

> See my reply to David in .asp.general in the topic "boolean search

> expressions"

Sorry Bob, I hadn't seen that reply until just now. I think we're on the
same channel. :-)

[Bob Barrows]

Aaron Bertrand [MVP] wrote:

>> See my reply to David in .asp.general in the topic "boolean search
>> expressions"

>
> Sorry Bob, I hadn't seen that reply until just now. I think we're on
> the same channel. :-)

And I replied before seeing your reply ... :-)

Bob


--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"