Multiple Keyword Search using "Advanced" tab in Recordset Builder

[LearningAndEarning]

Hey Everyone,

I need to allow the users on the web site I am developing to be able to enter multiple words in a single text area in a form. As of now the user can enter a single word and results will be displayed, but if multiple words are used no matching results are found.

I am having issues finding the answer as to how to go about writting the SQL code in the advanced tab in the recordset builder in Dreamweaver MX. I've searched the Access, SQL, and VBScript forums and the answers given are more complicated than I know how to understand.

Any suggestions would be greatly appreciated. Thanks in advance.

 

[traingamer]

I take it you've already seen faq701-4432

traingamer

 

[LearningAndEarning]

Thanks for responding. This isn't quite what I am trying to do though. Basically I have a "search" form with a single text field and I need to set up my results pages SQL code to allow multiple words in the search field.

Example: Spanish, Dictionaries, Youth (It's a foreign language bookstore)

As of now my SQL statement looks like this

SELECT* FROM tblBooks WHERE Language1 LIKE 'mmColParam' OR Title LIKE 'mmColParam' and so on and so on for each column. mmColParam refers to the search form.

I am a novice at this and my educational background is graphic design not web development, which would be why I am using DWMX and not hand coding a lot.

 

[Cheech]

You will need to parse the list of search criterea and then do a select on each of them, using "OR".

eg your select above will actually look like this to the database

SELECT* FROM tblBooks WHERE Language1 LIKE 'Spanish, Dictionaries, Youth' OR Title LIKE 'Spanish, Dictionaries, Youth'

it actually needs to look like

SELECT* FROM tblBooks WHERE Language1 LIKE 'Spanish' OR Language1 LIKE 'Dictionaries' OR Language1 LIKE 'Youth' OR Title LIKE 'Spanish' OR Title LIKE 'Dictionaries' OR Title LIKE 'Youth'

Cheech


[Peace][Pipe]

 

[LearningAndEarning]

Thanks to Cheech for the information. One last problem though. I used "dictionaries, youth , spanish" as an example of what the end user might put into the search criteria. We have over 1,000 books so far added to the db so putting OR in for each criteria individually would take forever.

This is why I am using the variables option in DWMX which basically reads like this:

Name:
MMColParam

Default Value:
%

Run Time Value:
Request.QueryString("search") (which is the name given to the form)

Can I word the SQL like this? SELECT* FROM tblBooks WHERE Language1 LIKE 'MMColParam', 'MMColParam', 'MMColParam'
or am I totally wrong on this?

 

[Cheech]

sorry but the way it works in your case MMcolParam is whatever is typed into the search box, as in my first example above.

Why not have seperate search boxes for langauge, title and the other columns then you can select on them.

Cheech

[Peace][Pipe]

 

[JoeMost]

I built a site (UD4) using Tom Muck's Search extension...
works great.

http://www.tom-muck.com/extensions/help/dynamicsearchasp/

JoeM