help structuring a query 1

[tyutghf]

I have a form with several fields that the user can fll in, if it is filled in then it grabs the recuired results from the database.

First field is area which can be filed in, if it is then grab the only fields with that data, if not then grab with anything in that row.

Next are many feature tickboxes, the user can tick none or as many as they want to get the results, so the form is structured such

area dropdown

feature 1 tickbox
feature 2 tickbox
feature 3 tickbox
feature 4 tickbox
feature 5 tickbox
feature 6 tickbox
feature 7 tickbox
feature 8 tickbox
feature 9 tickbox
feature 10 tickbox
feature 11 tickbox

Here my attempt at the query builder

SQL = "SELECT * FROM articles "

strNext = 0
strArea = 0
strAnd = 0

if request.querystring("area") <> "" then
strNext = 1
strArea = 1
SQL = SQL & "WHERE area = '"&request.querystring("area")&"' "
end if

if request.querystring("feature1") <> "" then
if strNext = 0 then
SQL = SQL & "WHERE feature1 = '1' "
else
if strTown = 0 and strAnd = 1 then
SQL = SQL & "OR feature1 = '1' "
else
SQL = SQL & "AND feature1 = '1' "
strAnd = 1
end if
end if
strNext = 1
end if

if request.querystring("feature2") <> "" then
if strNext = 0 then
SQL = SQL & "WHERE feature2 = '1' "
else
if strTown = 0 and strAnd = 1 then
SQL = SQL & "OR feature2 = '1' "
else
SQL = SQL & "AND feature2 = '1' "
strAnd = 1
end if
end if
strNext = 1
end if

etc etc upto 11

SO, if they select no area but feature 1 then the query should be

select * from articles WHERE feature1 = '1'

but if they select an area and feature 1

select * from articles WHERE area = 'areaselected' AND feature1 = '1'

my code above works so far, but if they select an area and multiple features

it should create

select * from articles WHERE area = 'areaselected' AND feature1 = '1' OR feature2 = '1'

but it is creating

select * from articles WHERE area = 'areaselected' AND feature1 = '1' AND feature2 = '1'

so instead of picking out from the database all records wityh the selected area and eitehr feature 1, or feature2 or both it is picking out the secelted area and the selected features which is wrong.

Does this make sense?

 

[sugarflux]

I've been there!!

My advice would be to start by creating the WHERE clause in the SQL variable and then add the SELECT at the end. This will allow you to test for existing conditions. In fact do it all back to front...

i.e.
strSQL=""

If Request.QueryString("Feature1")<>"" Then
strSQL=strSQL & " feature1=1 "
End If
If Request.QueryString("Feature2")<>"" Then
if strSQL<>"" Then strSQL=strSQL & " OR "
strSQL=strSQL & " feature2=1 "
End If
if Request.QueryString("Area")<>"" Then
if strSQL<>"" Then
strSQL=" WHERE Area='" & Request.QueryString("Area") & "' AND " & strSQL Else strSQL=strSQL & " WHERE Area='" & Request.QueryString("Area") & "'"
Else: strSQL=strSQL & " WHERE " & strSQL
End If
strSQL= "SELECT * FROM Articles " & strSQL

Hope this makes sense!!
sugarflux

 

[tyutghf]

I structured it back to front as you suggested which makes the whole thing much simpler, thanks mate. Star for you