CODEWITHSUNDEEP
phpsqlsearch
Brent
Brent

Reputation: 2485

SQL Query Price Compare

I am running a SQL query to seach my database. I can currently search everything until i compare prices.

Working Code Without Price Compare

$query = "SELECT * FROM propertie_ids WHERE (`search` LIKE '%".$searchquery."%') AND (`location` = '$location') AND (`bathroom` = '$bathroom') AND (`bedroom` = '$bathroom');

Broken Code With Price Compare

$query = "SELECT * FROM propertie_ids WHERE (`search` LIKE '%".$searchquery."%') AND (`location` = '$location') AND (`bathroom` = '$bathroom') AND (`bedroom` = '$bathroom') AND price BETWEEN ('0') AND  ('10000000000')";

Any ideas why this does not work, I have checked and all my tables are named correctly.

Upvotes: 1

Views: 1872

Answers (3)

Ragavendran Ramesh
Ragavendran Ramesh

Reputation: 358

http://sqlfiddle.com/#!2/abbcb/4/0

I think price column should be in INT , you should remove quotes from the query , it will work. Instead of cast you can use like price*1

$query = "SELECT * FROM propertie_ids WHERE (`search` LIKE '%".$searchquery."%') AND (`location` = '$location') AND (`bathroom` = '$bathroom') AND (`bedroom` = '$bathroom') AND price BETWEEN 0 AND  10000000000";

or 

`SELECT * FROM test where price*1 between '5' and '13'`

Here between 0 and 10000000000 means it includes 0 and 10000000000 too. I recommend going between 1 and 10000000000.

Upvotes: 1

Nadeem_MK
Nadeem_MK

Reputation: 7689

I think price should be in decimal/double/currency. And you're testing string values here! 

$query = "SELECT * FROM propertie_ids 
WHERE (`search` LIKE '%".$searchquery."%') AND (`location` = '$location') 
AND (`bathroom` = '$bathroom') AND (`bedroom` = '$bathroom') 
AND price BETWEEN 0 AND 10000000000";

And if the price is stored in string, you'll have to convert it, as you can't state a string between two strings!

$query = "SELECT * FROM propertie_ids 
WHERE (`search` LIKE '%".$searchquery."%') AND (`location` = '$location') 
AND (`bathroom` = '$bathroom') AND (`bedroom` = '$bathroom') 
AND CAST(price as DECIMAL(10,2)) BETWEEN 0 AND 10000000000";

Upvotes: 1

Mureinik
Mureinik

Reputation: 311143

Single quote denote strings in SQL, so using them will cause your query to do a lexicographical comparison. If you remove them, you'll have the numerical comparison you probably meant. Additionally, since you have price as a string, you'd probably need to cast that too.

$query = "SELECT * FROM propertie_ids WHERE (`search` LIKE '%".$searchquery."%') AND (`location` = '$location') AND (`bathroom` = '$bathroom') AND (`bedroom` = '$bathroom') AND CAST (price AS DECIMAL) BETWEEN (0) AND  (10000000000)";

Upvotes: 1

Related Questions