[Resolved] Help writing a basic search function

[homncruse]

Okay, I need some help writing a basic search function. The variables come from the previous page, and may or may not have values. The problem is that the WHERE part of the sql statement never enters the equation. I know there HAS to be an easier (and much more efficient) way of doing this, so if there is, someone please tell me.

I'm no good at writing this function, obviously, and I know it's not to "proper convention" with how I'm using the passed variables, but it's only in PHP4 and it works just fine.

if($loc <> ""){
	if($sqlst <> ""){
		$sqlst = "$sqlst AND ";
	}
	else{
		$sqlst = " WHERE ";
	}
	$sqlst = "loc='$loc'";
}

if($type <> ""){
	if($sqlst <> ""){
		$sqlst = "$sqlst AND ";
	}
	else{
		$sqlst = " WHERE ";
	}
	$sqlst = "type='$type'";
}

if($minprice <> ""){
	if($sqlst <> ""){
		$sqlst = "$sqlst AND ";
	}
	else{
		$sqlst = " WHERE ";
	}
	$sqlst = "price>'$minprice'";
}

if($maxprice <> ""){
	if($sqlst <> ""){
		$sqlst = "$sqlst AND ";
	}
	else{
		$sqlst = " WHERE ";
	}
	$sqlst = "price<'$maxprice'";
}

if($minsize <> ""){
	if($sqlst <> ""){
		$sqlst = "$sqlst AND ";
	}
	else{
		$sqlst = " WHERE ";
	}
	$sqlst = "sqft>'$minsize'";
}

if($maxsize <> ""){
	if($sqlst <> ""){
		$sqlst = "$sqlst AND ";
	}
	else{
		$sqlst = " WHERE ";
	}
	$sqlst = "sqft<'$maxsize'";
}

if($minacre <> ""){
	if($sqlst <> ""){
		$sqlst = "$sqlst AND ";
	}
	else{
		$sqlst = " WHERE ";
	}
	$sqlst = "acre>'$minacre'";
}

if($maxacre <> ""){
	if($sqlst <> ""){
		$sqlst = "$sqlst AND ";
	}
	else{
		$sqlst = " WHERE ";
	}
	$sqlst = "acre<'$maxacre'";
}

if($minbed <> ""){
	if($sqlst <> ""){
		$sqlst = "$sqlst AND ";
	}
	else{
		$sqlst = " WHERE ";
	}
	$sqlst = "bed>'$minbed'";
}

if($maxbed <> ""){
	if($sqlst <> ""){
		$sqlst = "$sqlst AND ";
	}
	else{
		$sqlst = " WHERE ";
	}
	$sqlst = "bed<'$maxbed'";
}

if($minbath <> ""){
	if($sqlst <> ""){
		$sqlst = "$sqlst AND ";
	}
	else{
		$sqlst = " WHERE ";
	}
	$sqlst = "bath>'$minbath'";
}

if($maxbath <> ""){
	if($sqlst <> ""){
		$sqlst = "$sqlst AND ";
	}
	else{
		$sqlst = " WHERE ";
	}
	$sqlst = "bath<'$maxbath'";
}

$sqlst = "SELECT * FROM listings $sqlst";

if($orderby <> ""){
	$sqlst = "$sqlst ORDER BY $orderby";
}

$result = mysql_query($sqlst,$db);

while ($row = mysql_fetch_array($result)) {

?>

[tsinka]

Hi,

you might need to use $GET (get variables) or $POST (post variables).

You can do something like this (assuming that the values come from a form with method="post"):

  $arrWhere = array();
  $sqlst = "";

  if(isset($_POST['loc']) && !empty($_POST['loc'])) { 
    $arrWhere[] = "loc='".mysql_escape_string($_POST['loc'])."'"; 
  }

  if(isset($_POST['type']) && !empty($_POST['type'])) { 
    $arrWhere[] = "type='".mysql_escape_string($_POST['type'])."'"; 
  }

  // do the same with the other variables
  // ....

  if (!empty($arrWhere)) {
    $sqlst = implode (" AND ",$arrWhere);
  }

  $sqlst = "SELECT * FROM listings WHERE $sqlst"; 

You might want to implement some form validation.

Thomas

[homncruse]

I had to change the end of the code to accomodate for a totally empty search, but other than that, the code works great! Thanks!

[tsinka]

yes ... I oversaw that I should've put the WHERE itself inside the if (!empty...) block.

The use of at least addslashes (or in case of MySQL mysql_escape_string) is very important regarding security to prevent attacks like SQL injection.

Thomas