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

Question

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.

Answer 1

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!

Answer 2

You could try

WHERE City like '$city'

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

Answer 3

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");

Answer 4

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