CODEWITHSUNDEEP
phpmysql
nzy
nzy

Reputation: 864

How to optimize queries in mysql

My mysql query for search is :

$result = mysql_query("SELECT * FROM room_tb WHERE location ='{$location}' AND price BETWEEN '$minprice' AND '$maxprice' ")or die('Could not connect: ' . mysql_error()); ;

This makes a compulsion to enter both the location and min and max price in form. I want a query that can make user to enter either location or max and min price, as well as allow user to search by both fields. What should i do?

Upvotes: 3

Views: 103

Answers (3)

Jack Cole
Jack Cole

Reputation: 1794

When I am generating my queries with optional fields, I create an array of each field, then join them with implode

$query_array = array();
$queries_stringed = '';

if(strlen($location) > 0){
    $query_array[] = "location ='{$location}'";
};
if(strlen($minprice) > 0 && strlen($maxprice) > 0){
    $query_array[] = "(price BETWEEN '$minprice' AND '$maxprice')";
};
if(!empty($query_array)){
    $queries_stringed = "WHERE ".implode(" AND ", $query_array);
};

$result = mysql_query("SELECT * FROM room_tb $queries_stringed");

Upvotes: 4

Paul Seleznev
Paul Seleznev

Reputation: 682

As an addition to those answers - you shouldn't trust users inputs and should escape given strings or use PDO instead of mysql_ functions

Upvotes: 0

Roy M J
Roy M J

Reputation: 6938

Thsi ought to do it for you : 

$query = "SELECT * FROM room_tb ";

if($location){
$query .= " WHERE location ='{$location}' ";
}

if(($minprice)&&($maxprice)&&(!$location)){
$query .= " WHERE price BETWEEN '$minprice' AND '$maxprice'";
}

if(($minprice)&&($maxprice)&&($location)){
$query .= " AND price BETWEEN '$minprice' AND '$maxprice'";
}

$result = mysql_query($query)or die('Could not connect: ' . mysql_error());

Cheers

Upvotes: 0

Related Questions