Posted by Bob Stearns on 11/14/05 04:15

C. David Rossen wrote:
> 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
>
>

Something like the following should work:


$sql = "select * from tablename where ";
if(!empty($Field1)) {
$sql .= "Field1 like $Field1%";
$opr = " AND ";
}
if(!empty($Field2)) {
$sql .= "$opr Field2 like $Field2%";
$opr = " AND ";
}
if(!empty($Field3)) {
$sql .= "$opr Field3 like $Field3%";
$opr = " AND ";
}
if(!empty($Field4)) {
$sql .= "$opr Field4 like $Field4%";
$opr = " AND ";
}
if(!empty($Field5)) {
$sql .= "$opr Field5 like $Field5%";
$opr = " AND ";
}
$result = mysql_query($sql);

If you want any more fields, look into arrays, both in your html and
your php.

[Back to original message]