What is the best approach for this - featured listing?

sgtpepper

Hello,

I have a mysql database that is for real estate. It is fairly large and lists properties from multiple agents. I have a search form and a query like so:

$query="SELECT * FROM residential WHERE town like ' $selecttown%' AND list_price
like '%$selectprice%' AND bedrooms like '%$bedroomsel%' AND baths like '%$bathselect%' LIMIT 0,15";

The variables like $bathselect, $bedroomsel etc are based on the form output.

I would like to do the following:

1) Add to this query something like this

AND listing_agent like 'Century 21'

However, my goal will be to display listings that match Century 21 above the rest of the listings.

Basically, trying to place listings for 'Century 21' seperate...like a featured agent. With the rest of the query results below.

Any ideas?

Thanks....

sfullman

this may not work in every version of mysql:

$query="SELECT *
FROM residential
WHERE
town like ' $selecttown%' AND
list_price like '%$selectprice%' AND
bedrooms like '%$bedroomsel%' AND
baths like '%$bathselect%' AND
IF(realtor like '%Century 21%', 1, 0) AS Featured
ORDER BY Featured DESC
LIMIT 0,15";

(we order desc because we want to go from 1-featured to 0-not featured.

Also why are you using like so much? 50000 (50K) is going to get prices like 500,000 and 150,000. Shouldn't you be using BETWEEN() or < or >? Using exact matches or <, > will make your queries a bit faster.

Sam

sgtpepper

Sam,

Thanks for the "like" tip. I'll clean up the query soon. Currently, I have it as

$query="SELECT * FROM residential WHERE town like '%$selecttown%' AND list_price like
'%$selectprice%' AND bedrooms like '%$bedroomsel%' AND baths like '%$bathselect%' IF
(lister_office_name like '%Century 21%', 1, 0) AS featured ORDER BY featured DESC LIMIT 0,15";

But now get the following error:
You have an error in your SQL syntax near 'if (lister_office_name like '%Century 21%', 1, 0) AS featured ORDER BY featured ' at line 1

This isnt related to the version conflict you mentioned is it?

sfullman

you forgot a comma:

$query="SELECT * FROM residential WHERE town like '%$selecttown%' AND list_price like
'%$selectprice%' AND bedrooms like '%$bedroomsel%' AND baths like '%$bathselect%' IF
(lister_office_name like '%Century 21%', 1, 0) AS featured ORDER BY featured DESC LIMIT 0,15";

do this:
$query="SELECT * FROM residential WHERE town like '%$selecttown%' AND list_price like
'%$selectprice%' AND bedrooms like '%$bedroomsel%' AND baths like '%$bathselect%',
IF
(lister_office_name like '%Century 21%', 1, 0) AS featured ORDER BY featured DESC LIMIT 0,15";

after bathselect --

get in the habit of writing your queries on multiple lines, much easier to catch errors, e.g.

SELECT
Field1 AS A,
Field2 AS B
FROM
table1 [, table2, table3 etc.]
WHERE
Condiment='Pickles' AND
Sandwich = 'Big Mac' AND
[etc. etc.]
ORDER BY something ASC, something_else DESC
LIMIT a, z

much easier to read and you catch missing commas!!!

Sam

sgtpepper

Hmmm....ok well I changed my code to be a little easier to read 🙂

$query="SELECT * FROM residential
WHERE town like '%$selecttown%'
AND list_price like '%$selectprice%'
AND bedrooms like '%$bedroomsel%'
AND baths like '%$bathselect%',
IF
(lister_office_name like '%Century 21 Highview Realty%', 1, 0)
AS featured
ORDER BY featured
DESC LIMIT 0,15";

Now I get the following error....

You have an error in your SQL syntax near ' IF (lister_office_name like '%Century 21 Highview Realty%', 1, 0) AS feature' at line 5

Any ideas? I appreciate the help and tips very much.

tsinka

I think you'll have to put the ,IF(...) in front of the FROM and replace the * by the list of fields.

SELECT *,IF(...) AS featured FROM might work also but I can't test it now.

One more point:

you use e.g.

like '%$selectprice%'

MySQL will have to search through all rows by doing this because you're doing a wildcard match.

Is $selectprice a substring of the field value or does the value in the field exactly match $selectprice ?

If it matches exactly then you should rather use

= '$selectprice' and put an index on that field (same for the other fields). That would speed up things a lot.

You can use EXPLAIN <query> to check if MySQL uses any indexes.

sfullman

my bad.. :-)

and it's always better to enumerate fields vs using *

Sam

sgtpepper

Thank You!!!

This seemed to work:

$query="SELECT * ,IF (lister_office_name like 'Century 21', 1, 0)
AS featured
FROM residential
WHERE town like '%$selecttown%'
AND list_price like '%$selectprice%'
AND bedrooms like '%$bedroomsel%'
AND baths like '%$bathselect%'
ORDER BY featured
DESC LIMIT 0,15";

Now, a question 🙂

With this the data is sorted properly....what if I wanted the output...grouped.

In other words, instead of a list that is listing Century21 first than followed by the rest....how can I display the Century 21 listings as 'Featured Listings' than display the other group of listings with a heading that says 'All the rest" or something?

So I guess I am looking for a way to make my Century21 listings stand out....but still show all listings.

Thanks for any suggestions.

sfullman

You'll need to do this using PHP.

Each record, buffer the value of the agent. When the value changes, you're in the rest of the list.

Or, run two separate queries.

Query results are always 2 dimensional matrices, and if you group, you lose the individual records.

Sam

sgtpepper

This is working great for my featured listing purpose

But, how can I add another order by? For example I want to list Century21 first....with highest price to lowest price.....followed by the rest of the data.

I tried:
$query="SELECT *
FROM residential
WHERE
town like ' $selecttown%' AND
list_price like '%$selectprice%' AND
bedrooms like '%$bedroomsel%' AND
baths like '%$bathselect%' AND
IF(realtor like '%Century 21%', 1, 0) AS Featured
ORDER BY Featured, listing_price DESC
LIMIT 0,15";

But that didnt seem to work.

Any ideas?

tsinka

Hi,

I think it would order by Featured ASC and list_price DESC so try

ORDER BY Featured DESC, listing_price DESC

instead

sgtpepper

That did it. Thanks 🙂