Posted by C. David Rossen on 11/14/05 01:51

Hello:

I am struggling with creating a search on multiple criteria (fields) of a
MySql database.
I have a search form that has 5 different criteria, each with a value of one
of the fields
in the database. Here is what I have done but it doesn't work the way I
want:

Declared Variables:
$Field1=$_POST['Field1'];
$Field2=$_POST['Field2'];
$Field3=$_POST['Field3'];
$Field4=$_POST['Field4'];
$Field5=$_POST['Field5'];

Query:
$result = mysql_query("select * from tablename where Field1 LIKE '%$Field1%'
AND Field2 LIKE '%$Field2%' AND Field3 LIKE '$Field3%' AND Field4 LIKE
'$Field4%'
AND Field4 LIKE '$Field4%' AND Field5 LIKE '$Field5%'ORDER BY LastName
ASC");

This gets the results:
while($r=mysql_fetch_array($result))
{
$Field1=$r["Field1"];
$Field2=$r["Field2"];
$Field3=$r["Field3"];
$Field4=$r["Field4"];
$Field5=$r["Field5"];
$Field6=$r["Field6"];
$Field7=$r["Field7"];
$Field8=$r["Field8"];
$Field9=$r["Field9"];
$Field10=$r["Field10"];
$Field11=$r["Field11"];
$Field12=$r["Field12"];
$Field13=$r["Field13"];

There are 13 fields in the table, but the form only searches on the 5 in the
query. I want the
user to be able to only search on the fields they want. For example, even
though there are 5
fields in the form, they may only want to search on 2, or 3, etc. What I
don't understand is how
you account for this in the query. The above query assumes they are going to
search on all 5
fields in the form, but what if they don't. I also want to put at the top of
the output something
like: "Results for Field1="selection1", Field2="selection2", etc. Again, not
sure how you account
for the fields they did not care to search on. Not sure how you do all of
this. If you can give
me some advice or point me to some resources, I would greatly appreciate it.
Thanks.

David

[Back to original message]