Need more specific filtering thru my database-please help

[terror]

hello all,

I've succeeded in creating a database search that will retrieve a wine name depending on what the user types in the dynamic text field. I am using this code to retrieve anything similar to what the user types. First it uses a dropdown list where the user can select red, white or champagne. I use this dropdown as a filter for the db.

sqlStr="SELECT*FROM wine WHERE wine_name LIKE '%" &_
Request.form("mySearch&quot&"%'"

'if red wine is chosen from drop down
if Request.form("winetype&quot= "red" THEN
sqlStr=sqlStr & "AND wine_type='red'"
end if

Can someone tell me how to go about having my sqlStr more specific. As of right now, if the user types in the letter 'a' then 'Robert Mondavi' because it has the letter 'a' in it. I assume I need some sort of string breakdown that checks ea. letter in order as its typed. Can anyone give me direction

 

[jitter]

hmmm...so what would you like it to return if the user types in 'a'?

 

[terror]

I'm thinking the way this should work to be of any use, if the user types in 'cabernet' or 'c', they should see all cabernet wines (robert mondavi cabernet, louis jadot cabernet, etc).if he types in 'r' then all robert mondavi wines would show
If the user types in 'a', they should be prompted to be more specific because no wine words begin with 'a'. I guess, if I can set it up so the first letter of a word is a match, then return results. If the letter is in the middle of a word, then prompt the user to be more specific.
does that make any sense?
Or, do you think this is totally unnecessary?

 

[bassguy]

I have developed a few different search tools for image libraries part number etc. I have found that the easiest way is to check the length of your search string and to just require at least 3 letters...if they put in fewer than 3 I just give a screen that says the search returned too many and to type in more data to be more specific. I know this is a bit of a harsh way to go about it....but really, we are talking about typing in 3 letters....if the user cannot get that close then maybe they do not need any more wine anyway

bassguy

 

[wujen]

Terror,

Why not have an option box for:

Winery(RourRivers, Carey,Vina Casa etc etc)
Type of wines(Pinot Noir,Pinot Gris, Rose, Sanv Blanc, Melbec etc).
Both

Then have a list box open for the winery(or type or both); they selected.

Then based on their selection ---then all the types of winery(type of wine or both) will open up the database for the information that they need.

Then this will eliminate some of the headaches of search functions on both sides of using and programming the website.

Aaron

 

[terror]

wujen,

I'm a little dense, so to recap...
you're saying I should use a checkbox for the wineries. Depending on what they check, I use that as a filter.

I also use a dropdown list which will also act as a filter.

so I just keep adding these variables to my strSQL string like

strSQL = strSQL & "wineryName"
strSQL = strSQL & "wineType"

Do i do this with a bunch of if then statements? or is there anything like functions in ASP?
I'm very new to all this so please bear with me. thanks

 

[wujen]

terror,


I would use if... then with sql statement with in it like you have listed above.

Aaron

 

[hithere]

Terror,
There was a posting here a couple(?) of weeks back about populating multiple list boxes and when a selection is made in the first one, it populates the 2nd box with options. That might be something to investigate. Also, you might also consider instead of multiple if/then statements, create on statement and couple it with a case statement. Should be a little cleaner and makes it easier to follow/keep track of the logic.

i.e.
Select Case "wine"
case "Chardonnay"
sqlstr=sqlstr & "AND wine_type='Chardonnay'"
case "Cabernet"
etc. etc.
end select

hth
mb

 

[Swany]

Depending on the size of the list, and the amount of traffic you expect to receive you could also do something like the following (you could also make a stored procedure to do this in Oracle or SQL Server):

'Please note. I just typed this in. It may or may not
'work without modifications. It should point you in the
'right direction if you want to do this sort of search

set db = server.createobject("ADODB.connection&quot
set rs = server.createobject("ADODB.recordset&quot
db.open "dsn=myDSN"

strSQL = "select * from wines"

'get all the wines
rs.open sql, db

'fetch the user input
wine_to_find = request.form("wine&quot

'lets see if any words begin with our search criteria
do while not rs.eof
db_wine = rs("wine_name&quot

'set the starting position in our string
char_pos = 1
do
space_pos = instr(char_pos, db_wine, " &quot
if space_pos = 0 then
if char_pos =1 then
response.write "No wines match that criteria"
else
response.write "End of matching wines"
end if
exit do
end if

'break out each word in the string
wine_word = mid(db_wine, char_pos, _
space_pos - char_pos -1)

'check to see if the start of this string matches
'the string we are looking for
if left(wine_word, len(wine_to_find)) = _
wine_to_find then
response.write db_wine & &quot;<BR>&quot;
end if

'move our pointer to the next word in the string
char_pos = space_pos + 1
loop 'for each word
rs.movenext
loop 'for each record

rs.close
db.close .
.. Eat, think and be merry .
... ....................... .

 

[terror]

thanks to you all, I think I have enough info to get thru this now. I appreciate all the input.
have a great day

 

[BigMag]

Easy,

if it's about the first letter, just get rid of the first &quot;%&quot; in your query and it will work fine.

Greetings,

M. BigMag, The Netherlands.
someone@euronet.nl (no kidding!)

 

[terror]

thanks, BigMag, hopefully, you just saved me alot of time. Obviously, I'm not getting anything done now. There is war in the air and I can't concentrate on a trivial database. I hope none of you people are in the city

 

[Swany]

BigMag,

that would work if he wasn't looking to match &quot;My Favorite Cabernet&quot; when the user enters the letter C

.
.. Eat, think and be merry .
... ....................... .

 

[terror]

okay then, I guess I'm going Swany's route or the case statement route.

 

[BigMag]

Terror,

From Holland, I'm sympathizing.

M. BigMag, The Netherlands.
someone@euronet.nl (no kidding!)

 

[terror]

BigMag,
not to continue this here, but a brief thanks for your sympathy. We are banding together and helping each other