Multiple Selections In List Menu For DB Search

[Enviweb]

Hi all,

Here's my situation. I set up a database for a local employment service to search their job listings. There are four search fields. Position, State/Location, Salary, and Key words. All work as designed except the State search. The form element is a list menu with multiple selections checked. The State search works only when one item is selected. The search criteria is displayed on the results page and all selected states are delimited with ", " (e.g. Arizona, California, etc.). I need to search each selection in the same database field from the list menu. Does anybody have any ideas for a solution. All responses would be deeply appreciated.

URL:

http://www.lsijobs.com/job_search.asp

Programming Language:
ASP, Java Script.

SQL:
Where Location LIKE '%job_region%' AND Salary LIKE '%job_salary%' AND Title
LIKE '%job_position%' AND KeyWords LIKE '%job_words%'

Variables:
Session.Form("region&quot
Session.Form("salary&quot
Session.Form("position&quot
Session.Form("words&quot

I use MS Personal Web Server on my local machine and run the database on a
MYSQL server. I do this all the time and have not had any problems until
now. It does not work on either machine.

Thanks In Advanced,
Doug

 

[forecasting]

This is an example using the PUBS database;
SELECT au_lname, state
FROM authors
WHERE state IN ('CA', 'IN', 'MD')

As you can see, the states need to be in and IN clause, and each needs to be wrapped in single parentheses. You could probably modify your the values submitted in the form to be wrapped by parens.

hope this helps.

 

[Tarwn]

Exactly. The problem is that when a list (or any object in a form for that matter) passes multiple values tied to the same name, it supplies them as a comma-delimeted string. So when you do your search that used to work with one entery you might be sending to the db something like:
"SELECT * FROM Wherever WHERE name LIKE '%userinput%'"
but with multiple selections or inputs you will be sending:
"SELECT * FROM Wherever WHERE name LIKE '%userinput1,userinput2,userinput3%'"
and the database attempts to match that entire string to one record in the database.
How to solve it?
Well if you look above and use the IN statement, you can simply wrap your string in ()'s and be done with it, but if you want to continue using LIKE's it's a little longer.
I am using request.Form's for my example:

<%
'beginning of sample sql
Dim sql_string
sql_string = &quot;SELECT * FROM Wherever WHERE &quot;

'flag that will tell us we have added a first element to the SQL
Dim fFlag, i
fFlag = false

'first declare an array
Dim myArray

If Request(&quot;region&quot;) <> &quot;&quot; Then

  'then split up the form values
  myArray = split(Request(&quot;region&quot;),&quot;,&quot;)

  'then build this portion of the SQL

  sql_string = sql_string & &quot;(&quot;

  'start it off, the rest need OR's
  sql_string = sql_string & &quot; Location LIKE '%&quot;&myArray(0)&quot;%'&quot;

  For i = 1 to UBound(myArray)
    sql_string = sql_string & &quot; OR Location LIKE '%&quot;&myArray(i)&quot;%'&quot;
  Next

  sql_string = sql_string & &quot;)&quot;
  fFlag = true
End If

'then continue with next field
If Request(&quot;position&quot;) <> &quot;&quot; Then
  'if already have stuff in SQL stmt, we need an AND
  If fFlag = true Then sql_string = sql_string & &quot; AND &quot;

  'then split up the form values
  myArray = split(Request(&quot;region&quot;),&quot;,&quot;)

  'then build this portion of the SQL

  sql_string =sql_string & &quot;(&quot;

  '...and so on for any fields with multiple selects
  
%>

your select stmt will come out looking like:
"SELECT * FROM Wherever WHERE (region LIKE '%val1%' OR region LIKE '%val2%') AND ( whatever LIKE '%val3%' ) AND (etc)"
with all of your OR stmts in their own sections, AND'd with the other major field names.

If you have any problems, i apologize, that was completely on the fly.
-Tarwn
You have to declare your variables!?! What kind of stupid rule is that?

 

[slechols]

I am trying your script, because I need the same functionality, but I keep getting syntax errors. Here is my code (I am getting a syntax error on '%"&prob_Array(0)"%'":


Dim sql_prob
sql_prob="Select * from problems where "

Dim pFlag, i, nFlag, j, sFlag, k
pFlag=false

Dim prob_Array, new_Array, susp_Array

If request("prob_select&quot="PCN" then
prob_Array=split(Request("prob_pcn&quot,",&quot
sql_prob=sql_prob & "("
sql_prob=sql_prob & PCN like '%"&prob_Array(0)"%'"

For i = 1 to UBound(prob_Array)
sql_prob=sql_prob & " or PCN like '%"&prob_Array(i)"%'"
Next
sql_prob=sql_prob & &quot"
pFlag=true
end if

 

[Tarwn]

The SQL statement will be dependant on what type of db your using, if it is an MS Access db it will want *'s instead of %'s.
Also, two quick corrections, the rest looks fine:

If request("prob_select")="PCN" then
    prob_Array=split(Request("prob_pcn"),",")
    sql_prob=sql_prob & "("
    sql_prob=sql_prob & PCN like '%"&prob_Array(0)"%'"

The last line of this code is missing a quote and an ampersand, it should be:
sql_prob=sql_prob & " PCN like '%"&prob_Array(0)&"%'"

And again here:

For i = 1 to UBound(prob_Array)
    sql_prob=sql_prob & " or PCN like '%"&prob_Array(i)"%'"
Next

Second line should be:
sql_prob=sql_prob & " or PCN like '%"&prob_Array(i)&"%'"

Just remember on any additions to your SQL string after this section that you will need to start the string with a space as your end paran does not have a following space.


I apologize to anyone else that copied my code from above, it was on-the-fly and I sometimes tend to think faster than I am typing, resulting in missing characters.

-Tarwn