[Resolved] SELECT WHERE field is greater than n1 and less than n2?

rupertbj

I have a MySql database of properties with areas in sq ft which I need to query for results of matches where the area falls between say 1,000 sq ft and 2,000 sq ft and by their postcode.

This is the MySql query so far that works beautifully for the postcode bit only:

$sql = "SELECT * FROM av WHERE postcode LIKE '%$pc%' ORDER BY areasf DESC";

I have been trying to get this to search for properties by $pc and $minarea and $maxarea. These strings come from a search form.

Thank you in advance for any help you can offer!

Rupert

Shrike

$sql = "SELECT * FROM av WHERE postcode LIKE '%$pc%' AND minarea > '$minarea' AND maxarea < '$maxarea' ORDER BY areasf DESC";

rupertbj

Thanks Strike!

I'm getting there now, but could you tell me why there is a value of 11,369 sq ft when the search is for EC3 between 1,000 sq ft and 2,000 sq ft?

Please have a look at the code in action at: http://www.hexellwylie.com/search_av.php

Thank you in advance for your help.

Rupert

Shrike

I think my query is a bit wrong, it should be where

squarefeet > '$min' AND squarefeet < '$max'

rupertbj

Strike,

This is the code I am using:

		$sql = "SELECT * FROM av WHERE postcode LIKE '%$pc%' AND areasf <= '$maxarea' AND areasf >= '$minarea' ORDER BY areasf ASC";

But it still has the same problem.

Weedpacket

Could it be because areasf is a string field and not numeric? "11,369"<"2,000".

Shrike

Ah good eyesight Weedpacket 🙂

rupertbj

But $areasf is drawn from a database where it is stored as 11369 and not as 11,369. It is printed as 11,369 on the table because the output is formatted.

rupertbj

Weedpacket.......Yes is see now.

I had the entry as a VARCHAR(10) in the MySql Database and have now changed it to INT(10) and I'm a happy man now!

Thanks for your help!

Rupert