Tom
Tom

Reputation: 137

Using Custom Variables with PHP Prepare statement

Here I go... I have a search function for users, like this:

if ($_GET['s_country']){    
   $s_country = htmlentities($_GET['s_country'],ENT_QUOTES,"UTF-8");
   $s_country=trim($s_country);
   $s_country_row = " and country= ?";
} else {
   $s_country="";
   $s_country_row = " and (country= ? or not country= '')";
}
$s_city = "";
$s_city_row = " and (city= ? or not city= '')";
if ($_GET['s_city']){        
   $s_city = strip_tags($_GET['s_city']);
   $s_city=trim($s_city);
   $s_city_row = " and city= ?";
} 

$search = mysqli_prepare($dbconnect, "SELECT id FROM user WHERE gender=? $s_country_row $s_city_row");
mysqli_stmt_bind_param($search, 'iss', $s_gender, $s_country, $s_city);

In the above example I have used my own way to dismiss variables. I need to dismiss/remove variables that are not searched for or have no input.

If "country" is not searched for, it should return all rows with all "country" values.

Is there any better way to do this? (Without prepare statement it is quite easy to customize everything, but I hope security does not mean less flexibility).

Thank you for reading this

Upvotes: 0

Views: 42

Answers (1)

Deepak Kedia
Deepak Kedia

Reputation: 174

$country = isset($_GET['s_country'])? " AND country=\'$_GET['s_country']\'" : '';
$query = " SELECT id FROM user WHERE gender=? $country ";

This is the pseudo idea here query is forming in such a way that - if it contains country then : SELECT id FROM user WHERE gender=? and country = 'US'; - if country is not defined then : SELECT id FROM user WHERE gender=?

as no conditions for country in the second query this will throw all country rows with particular gender

Upvotes: 1

Related Questions