Need a multiparameter SQL statement that allows blank fields

Report · Dec 09, 2006

I'm trying to build a search results page for a restaurant database, so that when people search by multiple criteria, only the restaurants that match all the criteria show up. However, I also want people to be able to leave search fields blank if they choose not to use them, but still have results show up for the fields they entered. I tried using an "AND" statement between my parameters, but that doesn't allow people to leave fields blank, so if not all fields are filled out, no results are shown. I tried using an "OR" statement, but then any results that match any of the criteria show up, rather than narrowing the results down to only the results that match all the criteria entered. For example, if someone searched by a certain "Zip Code" and also a certain "Food Type", the "OR" statement returns all restaurants that are in that Zip Code, as well as all restaurants that are in that Food Type, rather than only the restaurants that are both in that Zip Code AND Food Type. I realize this is probably a pretty simple problem, but I'm new to SQL and I don't know how to do this. Thanks,
-Sean

Report · Dec 10, 2006

Try something like this,

WHERE Zip_Code LIKE 'varZip_Code' AND Food_Type LIKE 'varFood_Type' and so
on.

If you have spaces in your field name LIKE Food Type or Zip Code You will
have problems also.

"seans887" <webforumsuser@macromedia.com> wrote in message
news:elfn7v$l9n$1@forums.macromedia.com...
> I'm trying to build a search results page for a restaurant database, so
that
> when people search by multiple criteria, only the restaurants that match
all
> the criteria show up. However, I also want people to be able to leave
search
> fields blank if they choose not to use them, but still have results show
up for
> the fields they entered. I tried using an "AND" statement between my
> parameters, but that doesn't allow people to leave fields blank, so if not
all
> fields are filled out, no results are shown. I tried using an "OR"
statement,
> but then any results that match any of the criteria show up, rather than
> narrowing the results down to only the results that match all the criteria
> entered. For example, if someone searched by a certain "Zip Code" and also
a
> certain "Food Type", the "OR" statement returns all restaurants that are
in
> that Zip Code, as well as all restaurants that are in that Food Type,
rather
> than only the restaurants that are both in that Zip Code AND Food Type. I
> realize this is probably a pretty simple problem, but I'm new to SQL and I
> don't know how to do this. Thanks,
> -Sean
>

Report · Dec 10, 2006

I just tried using the "LIKE" statement, unfortunately I got the same result as using an equal sign. There aren't any spaces in the name either. Right now my statement reads

SELECT *
FROM DCeats
WHERE DCeats.Zip_Code LIKE '#URL.ZipCode#' AND DCeats.Food
LIKE '#URL.Foodtype#'

It sill doesn't show any results when one field is left blank.