Jump to content

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>

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • 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.