Home > Archive > PHP SQL > May 2005 > search results are coming back wrong
You are viewing an archived Text-only version of the thread.
To view this thread in it's original format and/or if you want to reply to
this thread please [click here]
| Author |
search results are coming back wrong
|
|
|
| I have put together an online estate agent but the search results are coming
back wrong it will retrieve the correct results if you search with the lower
at 0 and the greater price at 1,000,000 but if you search from 1 to 600,000
or any combinations it only brings back one result I cant understand it,
this is in terrace/town house, bedrooms 1. but the others seem to work ok
ie: semi detached.
My query is
SELECT *
FROM properties
WHERE county = 'colname' AND property_type = 'property' AND no_of_bedrooms
>= 'bedrooms' AND 'pricefrom' <= price AND 'priceto' >= price
the page is http://www.kwiksale.co.uk/england.php
Can anyone help?
Thanks
| |
| news.tpg.com.au 2005-05-31, 8:57 pm |
| It's sorting by the text value rather than the number.
If you search between 0 and 1,000,000 it will only find results starting
between 0 and 1. You'll notice that a search up to 700,000 will result in a
2 bedroom townhouse for 69,950.
I believe there is a function called between() that could be worth looking
into.
Alternatively use strlen() to count the numbers in the price (i.e. 6 for
anything in the 100,000s) and then use left() to find the first number for
sorting purposes. A little more work but suitable if you're only searching
by the 100,000s.
Careful with the < / <= 1,000,000 - using <= 999,999 might be easier for
you.
It's also probably a good idea to always place the field name on the left of
an operator as it works from right to left
i.e. price >= 'pricefrom' AND price <='priceto'
Hope that helped.
"chris" <c.delgado@ntlworld.com> wrote in message
news:DR2ne.7340$rf4.5558@newsfe5-win.ntli.net...
> I have put together an online estate agent but the search results are
coming
> back wrong it will retrieve the correct results if you search with the
lower
> at 0 and the greater price at 1,000,000 but if you search from 1 to
600,000
> or any combinations it only brings back one result I cant understand it,
> this is in terrace/town house, bedrooms 1. but the others seem to work ok
> ie: semi detached.
> My query is
>
> SELECT *
> FROM properties
> WHERE county = 'colname' AND property_type = 'property' AND no_of_bedrooms
>
> the page is http://www.kwiksale.co.uk/england.php
> Can anyone help?
> Thanks
>
>
| |
|
| Thanks very much for the speedy response. We will have a play with the
between function. Also, just noticed that I had set the column in the db to
varchar
"news.tpg.com.au" <carol500@tpg.com.au> wrote in message
news:429ccf8c$1@dnews.tpgi.com.au...
> It's sorting by the text value rather than the number.
>
> If you search between 0 and 1,000,000 it will only find results starting
> between 0 and 1. You'll notice that a search up to 700,000 will result in
> a
> 2 bedroom townhouse for 69,950.
> I believe there is a function called between() that could be worth looking
> into.
> Alternatively use strlen() to count the numbers in the price (i.e. 6 for
> anything in the 100,000s) and then use left() to find the first number for
> sorting purposes. A little more work but suitable if you're only searching
> by the 100,000s.
> Careful with the < / <= 1,000,000 - using <= 999,999 might be easier for
> you.
>
> It's also probably a good idea to always place the field name on the left
> of
> an operator as it works from right to left
> i.e. price >= 'pricefrom' AND price <='priceto'
>
> Hope that helped.
>
> "chris" <c.delgado@ntlworld.com> wrote in message
> news:DR2ne.7340$rf4.5558@newsfe5-win.ntli.net...
> coming
> lower
> 600,000
>
>
|
|
|
|
|