How to optimize queries in mysql

Question

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?

What did you tried when solving your question? Provide some code, please. — Alma Do
– Alma Do, Commented Aug 16, 2013 at 6:30
You can check availability of those values(location, minprice and maxprice) in PHP code and then generate query string respectively. This is better than using conditions in MySQL query. If you want more specific answer, just post some code snippet. — HILARUDEEN S ALLAUDEEN
– HILARUDEEN S ALLAUDEEN, Commented Aug 16, 2013 at 6:36

Jack Cole · Accepted Answer · 2013-08-17 23:50:19Z

4

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");

edited Aug 17, 2013 at 23:50

answered Aug 16, 2013 at 6:33

Jack Cole

1,8142 gold badges25 silver badges45 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

jszobody Over a year ago

You'll want to make sure $queries_stringed isn't empty before you run the query, or you'll get a query error.

Roy M J · Accepted Answer · 2013-08-16 06:45:35Z

0

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

answered Aug 16, 2013 at 6:45

Roy M J

6,9367 gold badges53 silver badges79 bronze badges

Comments

Paul Seleznev · Accepted Answer · 2013-08-16 06:56:10Z

0

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

answered Aug 16, 2013 at 6:56

Paul Seleznev

6821 gold badge11 silver badges24 bronze badges

Collectives™ on Stack Overflow

How to optimize queries in mysql

3 Answers 3

1 Comment

Comments

Comments

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

Comments

Comments

Related