...

View Full Version : search criteria: if a field is any[eg beds] THIS IS CORRECT ?



lse123
11-25-2007, 12:08 PM
how to implement code [mysql/html] so if someone choose "any" for bedroom COLUMN[DROP DOWN MENU] results to show independence to bedroom column[according ONLY OTHER CRITERIA] ??

This query is correct for this ?
$query = "SELECT * FROM realestatetable WHERE (type=$type OR type='any') AND (area=$area OR area='any') AND (bedrooms=$beds OR bedrooms='any') AND price>$min AND price<$max ORDER BY price desc";

TheShaner
11-26-2007, 05:55 PM
No, that will not work as the query will look for the word "any" in the bedrooms field, just like it will in type and area.

With a dynamic query like yours, you must dynamically create your query based on the selections the user makes, like so:

// Base query
$query = "SELECT * FROM realestatetable WHERE price>$min AND price<$max";

// If the user didn't select "any" for type, search for the type
if (strcasecmp($type, 'any') !== 0)
$query .= " AND type = '$type'";
// If the user didn't select "any" for area, search for the area
if (strcasecmp($area, 'any') !== 0)
$query .= " AND area = '$area'";
// If the user didn't select "any" for bedrooms, search for the number of bedrooms
// (This assumes a number, which is why no quotes around $bedrooms)
if (strcasecmp($bedrooms, 'any') !== 0)
$query .= " AND bedrooms = $bedrooms";

// Now add the ORDER BY clause
$query .= " ORDER BY price DESC";
-Shane

lse123
11-26-2007, 07:10 PM
Please note that html form has choice for
bedrooms
any
1
2
3
4
+5
well , my code will work ?

TheShaner
11-26-2007, 07:38 PM
Please note that html form has choice for
bedrooms
any
1
2
3
4
+5
And how is the info the DB stored? In the bedrooms field, does it just store the number? If so, with your selection choice, you'll have to take my code and modify it a bit so that it can properly search (see below). To be honest, you really need to do some tutorials on SQL queries.

if (strcasecmp($bedrooms, '1') === 0)
$query .= " AND bedrooms = 1";
else if (strcasecmp($bedrooms, '2') === 0)
$query .= " AND bedrooms = 2";
else if (strcasecmp($bedrooms, '3') === 0)
$query .= " AND bedrooms = 3";
else if (strcasecmp($bedrooms, '4') === 0)
$query .= " AND bedrooms = 4";
else if (strcasecmp($bedrooms, '+5') === 0)
$query .= " AND bedrooms > 4";
-Shane

lse123
11-26-2007, 08:06 PM
bedrooms=$beds OR bedrooms='any'

this syntax for string field is ok ?

TheShaner
11-26-2007, 08:20 PM
bedrooms=$beds OR bedrooms='any'

this syntax for string field is ok ?

No, that will not work as the query will look for the word "any" in the bedrooms field, just like it will in type and area.
Are you reading my posts at all? I said from the beginning that it isn't and that you need set up your query like I'm doing in my examples. Your bedrooms field will not have the word "any" in it, so you can't search on that term. If someone selects "any", what you do is NOT search the bedrooms field.

-Shane

lse123
11-27-2007, 07:25 AM
I MUST USE
if (strcasecmp($bedrooms, 'any') !== 0)
$query .= " AND bedrooms = $bedrooms";

OR

if (strcasecmp($bedrooms, '1') === 0)
$query .= " AND bedrooms = 1";
else if (strcasecmp($bedrooms, '2') === 0)
$query .= " AND bedrooms = 2";
else if (strcasecmp($bedrooms, '3') === 0)
$query .= " AND bedrooms = 3";
else if (strcasecmp($bedrooms, '4') === 0)
$query .= " AND bedrooms = 4";
else if (strcasecmp($bedrooms, '+5') === 0)
$query .= " AND bedrooms > 4";

lse123
11-27-2007, 08:24 AM
What if prics vars : $min and $max can be 'any' too ?

TheShaner
11-27-2007, 04:12 PM
Whenever you have a field that can be ANY value, when you create your query, DO NOT search for it in your WHERE clause. Just leave it out.

If your user specifies an actual number for price, that's when you search the price field.

-Shane

lse123
11-28-2007, 12:24 PM
In the case of PRICE field I can setup html form so when a user chose 'any' this corresponse to min value for MIN FIELD and this corresponse to max value for MAX FIELD ? I mean inside the html form(values that give form to php/mysql script) ....



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum