Query Result Problem

[QuePID]

I have a html form with 10 input fields.  The user does not have to fill in all ten input forms.  The form posts to a php which then assigns the ten $_Post values to 10 local variables that are then used to build a query string.

 

The building of the query string looks like this: ($maxsearch=1000)

 

$query = "SELECT * FROM iknew

              WHERE Private = 0 AND

              Herbarium LIKE '%$searchherbarium%' AND

              Genus LIKE '%$searchgenus%' AND

              Species LIKE '%$searchspecies%' AND

              State LIKE '%$searchstate%' AND

              County LIKE '%$searchcounty%' AND

              Family LIKE '%$searchfamily%' AND

              Locality LIKE '%$searchlocality%' AND

              Accession LIKE '%$searchaccession%' AND

              Collector LIKE '%$searchcollector%' AND

              Symbol LIKE '%$searchsymbol%'

              LIMIT $maxsearch";

 

My echo of the query string looks like this:

 

SELECT * FROM iknew WHERE Private = 0 AND Herbarium LIKE '%WVA%' AND Genus LIKE '%%' AND Species LIKE '%%' AND State LIKE '%%' AND County LIKE '%%' AND Family LIKE '%%' AND Locality LIKE '%%' AND Accession LIKE '%%' AND Collector LIKE '%%' AND Symbol LIKE '%%' LIMIT 1000

 

The above query yields 555 results (should yield 772).

 

whereas the below query yields 772 results (correct)

 

SELECT * FROM table WHERE Herbarium LIKE '%WVA%' LIMIT 1000

 

I have been wrapping the variables with %'s as I need some type of pattern match as spelling errors in the input fields is common place.

 

What is the best way to handle empty input fields with my query string while still using pattern matching?

[PFMaBiSmAd]

You need to dynamically build your query string and only include the AND field_name LIKE '%$variable%' for those form fields that did have a value.