Search by Multiple Fields

[RazorSA]

Hi,

I have a database of people and their personal attributes.
Name, Eye Colour, Hair Colour, Height, Weight, etc.

I want to make a page that will have a drop-down list box with all of the correct informaition for each attribute on a search page.

e.g. Hair Colour --> Brown; Blonde; Green (Hard-coded)
Eye Colour --> Blue; Yellow; Purple (Hard-coded)
Height --> 120-130; 130-140; 140-150 (Hard-coded)

I want the user to be able to search by all fields or just by one field. Something like:

Select * from Users Where Hair='Blonde' and Eye='%' and Height='%'

Could also do this:

Select * from Users Where Hair='Blonde' and Eye='%' and Height='120-130'

The only problem is that I think you can only use the wildcard character for the following:

Select * from Users Where Hair='Blonde' and Eye='Br%' and Height='12%'

Is this correct and how can I fix the situation ???

Select from Users Where Hair='Blonde' and Eye='.' and Height='.*' (Windows is tempting me to do this)

Thanks

[Lars_Berg]

Wildcards (%_) are only significant when using a like predicate

Select * from Users Where Hair='Blonde' and Eye like 'Br%' and Height like '12%' 

Can't you just omit the restrictions on the columns where the user has not specified any search criteria?

[RazorSA]

What do you mean : "omit the restrictions on the columns" ???

Do you mean like this :

Select * from Users Where Hair='Blonde' and Eye like '' and Height like ''

????

I did not think that that would work at all

[Lars_Berg]

I meant

Select * from Users Where Hair='Blonde' 

(

Select * from Users Where Hair='Blonde' and Eye like '%' and Height like '%'

will work
)