Search form help

[applebiz89]

I have a search form on my site where a user can search for other members.

 

I have a criteria of: age, name, instrument, purpose

 

I want the search so that if a user selects for example; age - 18 and instrument - guitar. It comes up with the results of everyone with this. The same goes for having a search for all of the criteria as well as say only by the members name.

 

However if I use a select statement where I use AND, so 'SELECT * FROM profiles WHERE age = $age AND name=$name AND instrument = $instrument";

 

It will only come up with a result if ALL the search fields are input. With OR it will always come up with a result, but I want this search to be specific and allow the user to either just search one field, or all of them and come up with a result that meets this criteria.

 

So what I'm saying is, is there anyway to integrate AND and OR together so I can meet this all in one search form..

 

Thanks

 

applebiz

[aeroswat]

I have a search form on my site where a user can search for other members.

 

I have a criteria of: age, name, instrument, purpose

 

I want the search so that if a user selects for example; age - 18 and instrument - guitar. It comes up with the results of everyone with this. The same goes for having a search for all of the criteria as well as say only by the members name.

 

However if I use a select statement where I use AND, so 'SELECT * FROM profiles WHERE age = $age AND name=$name AND instrument = $instrument";

 

It will only come up with a result if ALL the search fields are input. With OR it will always come up with a result, but I want this search to be specific and allow the user to either just search one field, or all of them and come up with a result that meets this criteria.

 

So what I'm saying is, is there anyway to integrate AND and OR together so I can meet this all in one search form..

 

Thanks

 

applebiz

 

You don't make sense. First you say you want them to be able to search 2 fields. Second you say that you want them be only able to search one field or all fields. Which is it?

 

A quick solution to your problem without knowing exactly what you want is to use all fields in your query and wildcard characters.

SELECT * FROM tbl WHERE age='%" . $age . "%' AND name='%" . $name . "%' AND instrument='%" . $instrument . "%' AND purpose='%" . $purpose . "%'

[applebiz89]

I'm not sure how to word it better. I want all of them. So the form has four fields; the ones i mentioned.

 

I want it so...the user can search for a member with age=z name=y and instrument = q, however if they just want to search for an age on its own they can.

 

If I use as you said an AND, it means that all search fields have to be satisfied otherwise it returns no results. If I use OR results for any of them fields will come back...so if I searched age-18 and instrument-guitar. Using OR would return everyone with the age 18 aswell as anyone who plays guitar not strictly someone who is 18 and who plays guitar.

 

I know the solution for this would be AND as you said, but once you add more search fields in it becomes harder as all of these have to be filled in.  I want it so that they can pick and choose what they search..in the one form. Just like most searches work on other sites.

[aeroswat]

I'm not sure how to word it better. I want all of them. So the form has four fields; the ones i mentioned.

 

I want it so...the user can search for a member with age=z name=y and instrument = q, however if they just want to search for an age on its own they can.

 

If I use as you said an AND, it means that all search fields have to be satisfied otherwise it returns no results. If I use OR results for any of them fields will come back...so if I searched age-18 and instrument-guitar. Using OR would return everyone with the age 18 aswell as anyone who plays guitar not strictly someone who is 18 and who plays guitar.

 

I know the solution for this would be AND as you said, but once you add more search fields in it becomes harder as all of these have to be filled in.  I want it so that they can pick and choose what they search..in the one form. Just like most searches work on other sites.

 

The solution I have provided you will do exactly what you want it to. It's not very efficient but it will do exactly what you want it to. If you want a strict query to be set then you need to add to the query string based on what form inputs are filled.

 

here's the code i'm sure u can figure it out.

$count=1;
$qry = "SELECT * FROM tbl"
if($input_name != '') {$qry .= ($count==1? " WHERE": " AND") . "name='" . $name . "'"; $count++;}
if($input_instrument != '') {$qry .= ($count==1? " WHERE": " AND") . "instrument='" . $input_instrument . "'";$count++;}
if($input_purpose != '') {$qry .= ($count==1? " WHERE": " AND") . "purpose='" . $input_purpose . "'";$count++;}
if($input_age != '') {$qry .= ($count==1? " WHERE": " AND") . "age='" . $input_age . "'";$count++;}