In MySQL how do retrive multiple values from a Search form with three fields?

Question

Hi.

I have a two tables:

1. Clients
2. Cities

Then in the search form I have three fields:

1. A text input form to fill with the name of the client
2. A select box to choose the city where they want to search(table cities)
3. Another select box to choose the main business that the user wants to search for(businness is included in the clients table)

Depending on the choices of the form field it will make a search from the two tables.

Im thinking in doing multiple SQL querys depending on the user choices.

The problem is that maybe the user will not fill the input form with the name of the client, or maybe it will, but will not choose a city, etc. etc...

So will I have to do multiple ifs to show multiple sql querys depending on user choice? maybe i have a less confusing solution...

Thanks.

Answer 1

Perhaps your database structure could be altered to help you. For example, I assume you have a relationship between Cities and Clients. It sounds like Businesses is something like "Tech" or "Search" in which case could you have multiple clients with the same Business? Could be worth porting businesses to a separate table and then referring to those. Then you can have a simple query such as:

SELECT *
FROM clients
WHERE clients.city = {form result: cities.id}
AND clients.business = {form result: businesses.id}
ORDER BY clients.name
LIMIT 0, 30

Now assuming the user does not fill in one of the fields (e.g. city) you have two choices: force them to fill it in by validating user input or use it as a wildcard, in which case you could remove the WHERE clients.city clause from your query. Assuming you're giving your <select> options values of an id from Cities.

Programmatically I'd build the query in this case:

$queryStr = 'SELECT * FROM clients';
if (!empty($_POST['city']) && !empty($_POST['business'])) {
    $queryStr .= ' WHERE city = ' . filter($_POST['city']) . ' AND business = ' . filter($_POST['business']);
} elseif (!empty($_POST['city'])) {
    $queryStr .= ' WHERE city = ' . filter($_POST['city']);
} elseif (!empty($_POST['business'])) {
    $queryStr .= ' WHERE business = ' . filter($_POST['business']);
} else {
    // pass
}
$queryStr .= ' ORDER BY name LIMIT 0, 30';

Where filter is your choice of filtering method.

Answer 2

Simply query the DB with your criteria. If clients/cities actually exist, then they'll show up, otherwise the query will return an empty set. Easy as that, no need to validate the existence of either of them - the user should be smart enough to be able to come up with an existing city name for example.

The only thing you need to do is to mysql_real_escape_string().