Jump to content

Empty form varibles causing mySQL query to break


Jiin

Recommended Posts

LAMP, php: 5, mysql: 5.1.47

 

I have an html form with multiple drop down boxes enabling customers to search for property. They can use all of the boxes or some of the boxes to perform the search. Ideally customers should be able to use the search criteria they want to use(The drop down boxes they choose) and they are returned appropriate results.

 

The problem is the unused search boxes cause the mysql SELECT parameter variables to be empty. When I query my db with an empty variable it gives me the following message:

 

MySQL returned an empty result set (i.e. zero rows). ( Query took 0.0005 sec )

When I query it using all the varibles(all the drop down boxes) then it works fine.

 

My query is:

SELECT *
FROM rental_properties
WHERE City = '$maybe_empty_postvar'
AND TYPE = '$maybe_empty_postvar'
AND Bedrooms = '$maybe_empty_postvar'
AND Bathrooms = '$maybe_empty_postvar'
AND Neighborhood = '$maybe_empty_postvar'
AND Price = '$maybe_empty_postvar'
LIMIT 0 , 30

 

 

I am stuck :confused: Does anyone have any suggestions how I can solve this issue?

Perhaps I can determine which $_POST variables are set and append each one to the end of the mysql WHERE statement.  This will generate a query based only on valid $_POST['var']'s , in other words only append the values to the query that the user set via the form.

 

I will post the code when I get it working.

 

 

Perhaps I can determine which $_POST variables are set and append each one to the end of the mysql WHERE statement.  This will generate a query based only on valid $_POST['var']'s , in other words only append the values to the query that the user set via the form.

 

I will post the code when I get it working.

 

 

Actually, that's precisely what I suggested.

Solved it. Here is the relevant SQL code followed by the html/php:

 

SELECT *
FROM rental_properties
WHERE Country = 'USA'
AND Neighborhood = 'Madison at Soho'
AND TYPE = 'condominium'
AND City = 'NewYork'
AND Bedrooms = '2'
AND Bathrooms = '2'
AND Rent
BETWEEN 500
AND 1500
LIMIT 0 , 30

 

 

And the html/php

<?php
//White List method to sanitize user input
function clean($input) {
return preg_replace('/[^a-zA-Z0-9\s]_/', '', $input);    //only allows letters, numbers spaces and underscores.
}
?>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml" >
<head>
<title>Safe Form</title>
</head>

<body>
<form action="" method="POST">
<input name="Neighborhood" type="text" id="Neighborhood">
<input name="Type" type="text" id="Type">
<input name="City" type="text" id="City">
<input name="Bedrooms" type="text" id="Bedrooms">
<input name="Bathrooms" type="text" id="Bathrooms">
<input name="Min_Rent" type="text" id="Min_Rent">
<input name="Max_Rent" type="text" id="Max_Rent">
<input type="submit" name="Submit" value="Submit">
<input name="reset" type="reset" id="reset" value="Reset">
</form>

<?php
//Build base query, I automatically add "Country = USA" so I don't have to programaticaly determine to use mysql syntax "x = y" or "And x = y"
$query = "SELECT * FROM rental_properties WHERE Country = 'USA'";
foreach($_POST as $key => $value) {
$cleankey = clean($key);
$cleanvalue = clean($value);

if($cleankey == 'Neighborhood' && $cleanvalue != '') {
$query .= " AND $cleankey = '$cleanvalue'";

} elseif($cleankey == 'Type' && $cleanvalue != '') {
$query .= " AND $cleankey = '$cleanvalue'";

} elseif($cleankey == 'City' && $cleanvalue != '') {
$query .= " AND $cleankey = '$cleanvalue'";

} elseif($cleankey == 'Bedrooms' && $cleanvalue != '') {
$query .= " AND $cleankey = '$cleanvalue'";

} elseif($cleankey == 'Bathrooms' && $cleanvalue != '') {
$query .= " AND $cleankey = '$cleanvalue'";

} elseif($cleankey == 'Min_Rent' && $cleanvalue != '') {
$min_rent = $cleanvalue;

} elseif($cleankey == 'Max_Rent' && $cleanvalue != '') {
$max_rent = $cleanvalue;
}
}

if($min_rent != '' && $max_rent != '') {
$query .= " AND Rent BETWEEN $min_rent AND $max_rent";
}


echo "The SQL is:<br />
$query";
?>
</body>

</html>

Archived

This topic is now archived and is closed to further replies.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.