Search mySQL with PHP, using a WHERE wildcard or an IF statement?

I'm letting users search my database for data by city. My query looks like:

$results = mysql_query("SELECT * FROM mydb WHERE City='".$city."' LIMIT 10");

I want a user to be able to search 'all cities', so I'd like to either remove the WHERE statement if $city=='all cities'; or use a wildcard for the WHERE statement that matches all cities in the db.

I used to have an IF statement that switched between two queries, but I want to add more filters like country/all countries, zipcode/all zipcodes, etc, So I'd rather keep one dynamic SQL query.

Well, you could still have just one query and build the where clause dynamically, as such:

$where = '';

// conditional statements (if/else, switch) for populating the where clause
$where .= " WHERE City = '{$city}'";
$where .= " AND Country = '{$country}'";

$results = mysql_query("SELECT * FROM mydb{$where} LIMIT 10");

One way would be a case statement:

WHERE City = case when '$city' = 'All cities' then City else '$city' end

If the user is searching for 'All cities', this turns the WHERE statement into:

WHERE City = City

Which is always true (at least for non-null cities ;))

P.S. Make sure you're running these queries using a read-only MySQL account. The user could enter funny stuff into the $city parameter!

You could try

WHERE City like '$city'

and permit the users to enter wildcards, if you think they'd be up to it.

although not PHP programmer, this pseudocode might offer an option... conditionally build out your where clause. Additionally, I would do it with parameterized queries instead of direct string building to prevent sql-injection attacks.

cYourSQL = "select * from YourTable where "

cAndRequired = ""

if city is NOT "all cities"
  cYourSQL = cYourSQL + cAndRequired + " city = 'YourParameterValueProvided' "
  cAndRequired = " AND "
endif

Now, always add your country selection

cYourSQL = cYourSQL + cAndRequired + " country = 'YourCountryValue' LIMIT 10 " 

Run the query