Query issue

[rutetuti]

Hi, a bit of help for this PHP newbie please.

I have a database of properties and I want to allow users to search on up to 5 criteria via a form,
property type - ptype
Price - price
Location - location
Number of bedrooms - beds
Space for a pool - spool
The following query works...but only if ALL fields have a value, if I leave a field empty it returns 0 properties.

$query = "SELECT * from property_search where ptype='" . $_POST['ptype'] . "' AND price BETWEEN " .$_POST['minprice']." AND ".$_POST['maxprice']. " AND location='" . $_POST['location'] . "' AND beds>='" . $_POST['beds'] . "' AND spool='" . $_POST['spool'] . "'";

How do I amend the query to assume a wildcard for any field with a null entry? For instance, if I leave all fields empty it should return the whole database.

Thanks in advance.

[rocknbil]

Welcome aboard rutetuti, rule one of any server side programing: treat all input as the poison it is. There are a number of nasty things people can do if you use your post variables directly without cleansing, as you have here. Second, your input fields are the same name as your database columns - double-bad. Dig around here for topics on this, for this thread, I'll leave it at that.

To answer your question, you would do something that dynamically builds the select. I'm going to leave your post variables as you have them, but mind the above before deploying this in a public program:

$where = NULL;

if (isset($_POST['ptype'])) { 
 // since this is the first item, we don't need AND 
 // Note also, I'm adding a SPACE before the field. While 
 // this is not needed in a comma separated update, 
 // it's a very good habit to form that will prevent 
 // "stupid select errors" 
 $where = " ptype='" . $_POST['ptype'] . "'" 
} 

// Note this works if min or max alone are entered 
if (isset($_POST['minprice']) or isset($_POST['maxprice'])) { 
 // If $where was set above, we need an AND 
 if ($where) { $where .= " and"; } 
 if (isset($_POST['minprice']) and isset($_POST['maxprice'])) { 
  $where .= " price between '" .$_POST['minprice']. "' and '" . $_POST['maxprice']. "'" 
 } 
 else { 
  if (isset($_POST['minprice'])) { $where .= " price >= '" . $_POST['minprice'] . "'"; } 
  else { $where .= " price <= '" . $_POST['maxprice'] . "'"; } 
 } 
} 

if (isset($_POST['location'])) { 
 if ($where) { $where .= " and"; } 
 $where .= " location='" . $_POST['location'] . "'"; 
} 
if (isset($_POST['beds'])) { 
 if ($where) { $where .= " and"; } 
 $where .= " beds >= '" . $_POST['beds'] . "'"; 
} 
if (isset($_POST['spool'])) { 
 if ($where) { $where .= " and"; } 
 $where .= " spool = '" . $_POST['spool'] . "'"; 
} 

$query = "select * from property_search";
if ($where) { $query .= " where $where"; }

You will also need to do something for order and limit too . . . follow the same logic.

[rutetuti]

Many thanks for your input Rocknbil..you've given me loads to think AND read about!

I tried your suggestion but found that null cells were still giving 0 returns. I then found an article and I changed all instances of "isset" to "!empty" and the search works a treat!

Only sanitisation and pagination to go...no doubt I'll be back with more questions.

Cheers

[edited by: coopster at 7:35 pm (utc) on April 16, 2009]
[edit reason] removed link to non-authoritative site [/edit]