CODEWITHSUNDEEP
phpmysqlsql
Sikander
Sikander

Reputation: 2835

Sql : Update sql query on the basis of posted values

i am doing a query on the basis of posted values for eg. following is the query 

$query= mysqli_query($connect,
                "SELECT * FROM residential  WHERE type='$type' AND unit_type='$unit_type' "
                . "AND  price BETWEEN '$min_price' AND '$max_price' "
                . " AND bedrooms BETWEEN '$min_bedrooms' AND '$max_bedrooms'");

now $unit_type , $min_price etc are coming from $_POST array, my problem is that if i don't receive any of the used values ($type ,$max_bedrooms, $min_bedrooms etc ) in query, then query does not work. my question is how do i update the query on the basis of only posted values .

Upvotes: 1

Views: 52

Answers (2)

Sanooj T
Sanooj T

Reputation: 1327

Use a $query variable and change it by your post values

$query= "SELECT * FROM residential  WHERE 1=1 "; 
if($type!=""){
 $query.=" AND  type='$type'";
}
if($unit_type!=""){
 $query.=" AND  unit_type='$unit_type'";
}
if(($min_price!="") && ($max_price!="")){
 $query.=" AND  price BETWEEN '$min_price' AND '$max_price' ";
}
if(($min_bedrooms!="") && ($max_bedrooms!="")){
 $query.=" AND bedrooms BETWEEN '$min_bedrooms' AND '$max_bedrooms'";
}
$result= mysqli_query($connect,$query);

Upvotes: 3

Jimmmy
Jimmmy

Reputation: 579

you can (should) perform some validating before putting variables into query. There you can check if vars are sent (and valid types etc.) and if they are not, you can replace them with your default values.

There is another way to do this, in your query you can use COALESCE() function where you specify default value if your value is not present ex.: COALESCE('$unit_type', 'coin').

As mentioned in comments, you should be aware of SQL injection. You can avoid it by strict validating&encoding sent variables or by using some prepared statements for composing sql queries (dibi, pdo ..).

Hope it helps :)

Upvotes: 2

Related Questions