PHP/MYSQL advanced search script. How?

Question

I need some guidance to make an advanced search script for a website I'm working on.

I already know how to search the database for simple queries. The problem I'm encountering right now is how to search, when using multiple select boxes. For example:

This is just a simple form with different search options. The question is:

The visitor can choose to search on a country or city, both or even with all three options.

How do I catch that in the PHP script? Do I have to check if for example a city has been chosen, and fire a query based on that? But if I do that I would have to make different queries based on each select option.

In pseudo-code it would be something like this: (I imagine)

if country and city and something else is not null, launch a query to search in all three tables in the database.

But what to do when just the country has been chosen? Or just the city?

Is there a simple way to accomplish this?

Thanks in advance.

Answer 1

I like using an array to join conditions so I don't have to worry about leading or trailing AND's.

$conditions = array();
if ($formCondition1) {
  $conditions[] = 'state = "'.$somevalue.'"';
}
if ($formCondition2) {
  $conditions[] = 'country = "'.$somevalue.'"';
}
...
if ($formConditionN) {
  $conditions[] = 'N = "'.$somevalue.'"';
}

//finally join the conditions together, the simplest case is with ANDs (if you need to add ORs, which it sounds like you don't, then this code would be a bit more complex)

$sqlStatement = 'SELECT field1, field2 FROM tableABC WHERE '.implode(' AND ', $conditions);

EDIT: don't forget to escape the input to prevent injection attacks, and of course test to make sure there are at least 1 condition before running the query. EDIT: lol jswolf and I think very much alike :)

Answer 2

I make a $where array, add my conditions to it as necessary, and then implode it with ' AND ' as the glue. So something like:

$where = array();
if $city is defined
    $where[] = "city = '".mysql_real_escape_string($city)."'";
fi
if $country is defined
    $where[] = "country = '".mysql_real_escape_string($country)."'";
fi
...

if(count($where)) {
    $query.= ' WHERE '.implode(' AND ', $where);
}

Answer 3

I would try something like:

$qry = "SELECT * FROM table WHERE ";
if ($country != '') {
    $qry .= "country='".mysql_real_escape_string($country)."' AND "
}
if ($city != '') {
    $qry .= "city='".mysql_real_escape_string($city)."' AND "
}
$qry .= '1';
$res = mysql_query($qry);

The query is built up depending on what is set. Note the "1" on the end of the query string which is always true. This is needed to follow the "WHERE" if $country and $city are both empty, or to follow the last "AND" if they are not.