SQL Multiple search criteria

[Abernus]

Hi all,

I have been seraching for help high and low and have hit a brick wall. I used to do a little SQL etc back at Uni but have not touched it for years and now find myself needing it again.

I have written a database in Access 2000 called Build.mdb and am writing a web frontend for it in Dreamweaver. What I am trying to do is allow users to select a search criteria by dropdowns and then pull the data based on their selections.

The database at the moment only has a few rows in, here are the rows I am wanting to serach on (I have left out other none searchable data):

ID.....Primary_Profession....Type_of_Build....Where_Used
1......Monk.....................Support.............GvG
2......Elementalist.............Spike...............HA
3......Necromancer..............Spike...............PvP
4......Elementalist.............Spike...............HA

Now the first page has the following listboxes:
Primary_Profession - populated manually with the data
Type_of_Build - populated manually with the data
Where_Used - populated manually with the data

Here is the code for this page:

<%@LANGUAGE="VBSCRIPT"%>
<!--#include file="Connections/Mythos1.asp" -->
<%
Dim Recordset1
Dim Recordset1_numRows

Set Recordset1 = Server.CreateObject("ADODB.Recordset")
Recordset1.ActiveConnection = MM_Mythos1_STRING
Recordset1.Source = "SELECT * FROM Builds"
Recordset1.CursorType = 0
Recordset1.CursorLocation = 2
Recordset1.LockType = 1
Recordset1.Open()

Recordset1_numRows = 0
%>
<style type="text/css">
<!--
.style2 {font-family: Verdana, Arial, Helvetica, sans-serif; font-weight: bold; font-size: 10px; }
-->
</style>

<form name="form1" method="post" action="test.asp">
<table width="100%" border="0" cellspacing="0" cellpadding="0">
<tr>
<td width="43%"><p align="center" class="style2">Primary Profession
<select name="Primary_Profession" id="Primary_Profession">
<option>Please Select</option>
<option value="Monk">Monk</option>
<option value="Necromancer">Necromancer</option>
<option value="Ranger">Ranger</option>
<option value="Elementalist">Elementalist</option>
</select>
</p>
</td>
<td width="34%">
<div align="center" class="style2">
Type Of Build
<select name="Type_of_build" id="Type_of_build">
<option>Please Select</option>
<option value="Support">Support</option>
<option value="Spike">Spike</option>
</select>
</div></td>
<td width="23%"><div align="center" class="style2">Where Used
<select name="Where_Used" id="Where_Used">
<option>Please Select</option>
<option value="GvG">GvG</option>
<option value="HA">HA</option>
<option value="PvP">PvP</option>
</select>
</div></td>
</tr>
</table>
<p align="center">
<input type="submit" name="Submit" value="Submit">
</p>
</form>
<p>&nbsp; </p>
<p>&nbsp;</p>
<p>&nbsp; </p>

<%
Recordset1.Close()
Set Recordset1 = Nothing
%>

Now the other page contains the database with the following SQL query:

SELECT *
FROM Builds
WHERE (((Builds.Primary_Profession)='MMColParam1') AND ((Builds.Type_of_Build)='MMColParam2') AND ((Builds.Where_Used)='MMColParam'))
ORDER BY Builds.Primary_Profession, Builds.Type_of_Build, Builds.Where_Used;

My Problem is that if the user selects a value in every list box on the first page they will get a result. But what I want it to be able to do is allow users to only select 1 or 2 values and still pull data bases on the 1 or 2 selections. What is actually does in this case at the moment is return no data.

Please can anyone help me and tell me what I am doing wrong in my SQL query?

P.S I hope this makes sence.

[TheNige]

You'll probably have to build your SQL query conditionally based on if they selected values in each of the drop down lists. If they only selected 1 value then your query will only have the WHERE clause looking for that value. If it has 2 then only search for those two in your WHERE cluase.

[Abernus]

Thanks for the update, but still need help. I am not sure how to set out the SQL statement. Are you saying that it (logicaly) should look like this:

IF
MMColParam1=''
THEN
WHERE
......
ELSE IF
MMColParam1='' AND MMColParam2=''
THEN WHERE
.....

[TheNige]

Yes.

Test your various queries in Access using the SQL view and you can tell what type of results you will get before coding anything in your GUI.

[Abernus]

Thanks for putting me on the right track now it's off to the library to get books on SQL so I know what commands are used and whats not valid :D

[Abernus]

O.K Back asking for more help again. Do I need to use Stored procedures (I have no idea what these are) I have been trying for the best part of 8 hours solid and I am still no further forward

This is the best I can come up with:

IF MMColParam=1 AND MMColParam2=1
THEN
SELECT *
FROM Builds
WHERE ((Builds.Primary_Profession)='MMColParam1')
ELSE
IF IF MMColParam1=1 AND MMColParam2=1
THEN
SELECT *
FROM Builds
WHERE ((Builds.Where_Used)='MMColParam')
ORDER BY Builds.Primary_Profession, Builds.Type_of_Build, Builds.Where_Used;

However I get the usuall very non helpfull message: "Please enter either a SELECT statement or a call to a stored procedurethat returns a recordset"

The default value of MMColParam is 1 so I figure if that value it returned it means that the use didn't make a selection on this variable on the previous page.

Please can someone tell me where I am going wrong and give an example please :D

[Abernus]

Hi peep's, I am still having problems with this. Can anyone give me any pointers as to what I am doing wrong, please.

Thanks

[wrkalot]

here is a little snippet that "may" help. Should be easy enough to expand on.

If request.querystring("SearchType") = "" Then
response.write "what ever..."
end if
If request.querystring("SearchType") = "Product" Then
strSearch = Split(trim(request.querystring("search")), " ")
For i = 0 to (UBound(strSearch))
strQL = "SELECT * FROM Products WHERE Product LIKE '%" & strSearch(i) & "%'"
Next
strQL = strQL & "ORDER BY CDYear AND Artist DESC"
end if
If request.querystring("SearchType") = "Artist" Then
strSearch = Split(trim(request.querystring("search")), " ")
For i = 0 to (UBound(strSearch))
strQL = "SELECT * FROM Products WHERE Artist LIKE '%" & strSearch(i) & "%'"
Next
strQL = strQL & "ORDER BY CDYear AND Artist DESC"
end if
If request.querystring("SearchType") = "Tracks" Then
strSearch = Split(trim(request.querystring("search")), " ")
For i = 0 to (UBound(strSearch))
strQL = "SELECT * FROM Products WHERE Tracks LIKE '%" & strSearch(i) & "%'"
Next
strQL = strQL & "ORDER BY CDYear AND Artist DESC"
end if

[aspdaddy]

Hers one way to build the search dynamically:

if request("txt1")<>"" then
strSearch=strSearch & " AND( col1='" & request("txt1") & ') "
end if

if request("txt2")<>"" then
strSearch=strSearch & " AND( col2='" & request("txt2") & ') "
end if

strSQL = "SELECT * FROM tbl WHERE 1=1 " & strSearch

[wingnut]

This may help you though its been awhile since you posted!

<% Dim SQL

SQL = "WHERE "

'1st dropdown
If Request.QueryString("Primary_Profession") <> "" Then
SQL = SQL & "Primary_Profession = '" & Request.QueryString("Primary_Profession") & "' "
Else
SQL = SQL & "Primary_Profession = '%' "
End IF
' 2nd dropdown
If Request.QueryString("Type_of_build") <> "" Then
SQL = SQL & "AND Type_of_build = '" & Request.QueryString("Type_of_build") & "' "
End IF
' 3rd dropdown
If Request.QueryString("Where_Used") <> "" Then
SQL = SQL & "AND Where_Used = '" & Request.QueryString("Where_Used") & "' "
End IF

%>
<%
Dim Recordset1
Dim Recordset1_numRows

Set Recordset1 = Server.CreateObject("ADODB.Recordset")
Recordset1.ActiveConnection = MM_Mythos1_STRING
Recordset1.Source = "SELECT * FROM Builds " & SQL & " ORDER BY Primary_Profession"
Recordset1.CursorType = 0
Recordset1.CursorLocation = 2
Recordset1.LockType = 1
Recordset1.Open()

Recordset1_numRows = 0
%>
<style type="text/css">
<!--
.style2 {font-family: Verdana, Arial, Helvetica, sans-serif; font-weight: bold; font-size: 10px; }
-->
</style>

<form name="form1" method="get" action="test.asp">
<table width="100%" border="0" cellspacing="0" cellpadding="0">
<tr>
<td width="43%"><p align="center" class="style2">Primary Profession
<select name="Primary_Profession" id="Primary_Profession">
<option>Please Select</option>
<option value="Monk">Monk</option>
<option value="Necromancer">Necromancer</option>
<option value="Ranger">Ranger</option>
<option value="Elementalist">Elementalist</option>
</select>
</p>
</td>
<td width="34%">
<div align="center" class="style2">
Type Of Build
<select name="Type_of_build" id="Type_of_build">
<option>Please Select</option>
<option value="Support">Support</option>
<option value="Spike">Spike</option>
</select>
</div></td>
<td width="23%"><div align="center" class="style2">Where Used
<select name="Where_Used" id="Where_Used">
<option>Please Select</option>
<option value="GvG">GvG</option>
<option value="HA">HA</option>
<option value="PvP">PvP</option>
</select>
</div></td>
</tr>
</table>
<p align="center">
<input type="submit" name="Submit" value="Submit">
</p>
</form>
<p>&nbsp; </p>
<p>&nbsp;</p>
<p>&nbsp; </p>

<%
Recordset1.Close()
Set Recordset1 = Nothing
%>