How can I use PHP to search a database table for multiple fields entered by a user in a form?

Question

I made an HTML form that contains two text fields: one for the "name" and the other for the "phone".

The user enters data in both fields and my PHP script searches for the colomn that matches both fields (name and phone). I use the following command:
$result = mysql_query("SELECT * FROM applicants WHERE name='$name' AND phone='$phone");

The problem is that I want if the user leaves the "phone" filed empty, then the PHP code searches using the "name" only and ignore the "phone". If the user enters the phone only, then the search ignores the name and only uses the phone for search. If the user leaves both fields empty, then the search returns all the rows in the database table. I want a method that can work easily if the number of files is more than 2 (ex: name, phone, country, ... etc.)

Answer 1

You have 3 options, each increasing in complexity, but increasingly useful.

1) Change your SQL statement to $name OR $phone

2) Create 3 separate SQL statements (e.g. one to search just for name, one for phone, and one for both). Then check if any of the fields are empty, run the appropriate statement

3) More complex, but scalable;

Create the first part of your SQL statement and an array of the fields you will use. Then create a loop which checks each of the fields in the array and then appends the SQL statement as necessary.

This is more time consuming, but if you add more fields in your html form, all you need to do is add the name of the field to the array and it's automatically dealt with. Also saves time coding for each individual field.

Answer 2

when the form is submitted
do this

$strQuery = "select * from applicants";
if($name!="")
{
$strQuery .= " where name='".$name."'";
}
if($phone!="")
{
if(strlen($strQuery)==24)
{
$strQuery .= " where phone='".$phone."'";
}
else
{
$strQuery .= " and phone='".$phone."'";
}
}
$result = mysql_query($strQuery);
?>

You can apply the above procedure for any number of form inputs.