Jiin Posted June 18, 2010 Share Posted June 18, 2010 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 Does anyone have any suggestions how I can solve this issue? Quote Link to comment https://forums.phpfreaks.com/topic/205201-empty-form-varibles-causing-mysql-query-to-break/ Share on other sites More sharing options...
fenway Posted June 18, 2010 Share Posted June 18, 2010 Then don't add them to the query -- don't blame mysql for executing a query that you type. Quote Link to comment https://forums.phpfreaks.com/topic/205201-empty-form-varibles-causing-mysql-query-to-break/#findComment-1074151 Share on other sites More sharing options...
Jiin Posted June 19, 2010 Author Share Posted June 19, 2010 Hey dude, this isn't the OpenBSD user group. I don't recall blaming mysql, I recall describing my issue and asking for help. Quote Link to comment https://forums.phpfreaks.com/topic/205201-empty-form-varibles-causing-mysql-query-to-break/#findComment-1074224 Share on other sites More sharing options...
Jiin Posted June 19, 2010 Author Share Posted June 19, 2010 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. Quote Link to comment https://forums.phpfreaks.com/topic/205201-empty-form-varibles-causing-mysql-query-to-break/#findComment-1074465 Share on other sites More sharing options...
fenway Posted June 20, 2010 Share Posted June 20, 2010 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. Quote Link to comment https://forums.phpfreaks.com/topic/205201-empty-form-varibles-causing-mysql-query-to-break/#findComment-1074489 Share on other sites More sharing options...
Jiin Posted June 20, 2010 Author Share Posted June 20, 2010 I know. Quote Link to comment https://forums.phpfreaks.com/topic/205201-empty-form-varibles-causing-mysql-query-to-break/#findComment-1074641 Share on other sites More sharing options...
Jiin Posted June 21, 2010 Author Share Posted June 21, 2010 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> Quote Link to comment https://forums.phpfreaks.com/topic/205201-empty-form-varibles-causing-mysql-query-to-break/#findComment-1075049 Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.