Search query

mikawhat

i need some help with a search query. i tried several ways but no luck. i get the following values from a form

$type = $_POST['type']; //property type
$baths = $_POST['baths']; //number of bathrooms
$town = $_POST['town']; //location
$beds = $_POST['beds']; //number of beds
$price1 = $_POST['price1']; //min value
$price2 = $_POST['price2']; //max value
$sp = $_POST['sp']; //swimming pool? (checkbox)

if any of the values is not specified then the query should search for any value.

Piranha

I suggest that you do lots of if-statements:

$sql = "SELECT columns FROM table WHERE 1 ";
if (!empty($_POST['type'])
{
    $sql .= sprintf(" AND type = %s ", mysql_real_escape_string($_POST['type']);
}
if (!empty($_POST['baths'])

And so on.

mikawhat

This should be ok? i've never done something like this before...

	$query = "SELECT * FROM category WHERE ";
	if (!empty($_POST['type']) 
	{ 
		$query .= sprintf(" type = %s ", mysql_real_escape_string($_POST['type']); 
	}
	if (!empty($_POST['town']) 
	{ 
		$query .= sprintf(" AND town = %s ", mysql_real_escape_string($_POST['town']); 
	}
	if (!empty($_POST['beds']) 
	{ 
		$query .= sprintf(" AND beds = %s ", mysql_real_escape_string($_POST['beds']); 
	}
	if (!empty($_POST['baths']) 
	{ 
		$query .= sprintf(" AND baths = %s ", mysql_real_escape_string($_POST['baths']); 
	}
	if (!empty($_POST['sp']) 
	{ 
		$query .= sprintf(" AND sp = %s ", mysql_real_escape_string($_POST['sp']); 
	}

Piranha

There is one good way of knowing if it works: Test it on the development server. That way you will learn from possible errors as well.

Edit: One suggestions is to use ' for variables in the query. Another one is to see what happens if type is empty. Remember that codes from me is only pseudocode, you have to check it to make sure that it will work.

Sxooter

Better to use a bit of array handling functions to do this.

Untested code ahead, but you should get the idea:

<?php
$query = "select * from table where ";
$fields = array("type","towns","beds");
$count = 0;
foreach ($fields as $f){
  if (isset($_GET[$f]) || istrlen($_GET[$f])<>0){
    if ($count){
      $query.=" OR ";
    }
    $query.=$f." like '%".$_GET[$f]."%' ";
    $count++;
}
print $query;
?>

Like I said, that may be buggy, but you get the idea I hope.

The advantage there is that once you have it working, adding and removing fields involves changing your table and adding entries to a single array.