[SOLVED] All possibilities between a price range

[mausie]

Hi all,

 

I am working on a website where you can find a lot of objects in continents, countries with a specific price range.

On the result page will be refine result options like choosing a other continent, country, type etc. At the end of these links will also be the number of results that would be found on this option    Example: United Kingdom (40 000) instead of Spain you searched.

 

Because I thought searching will be kind of heavy throwing a count for all possibilities I came up with the idea to create a cache table with all the possibilities and the number of counts. This would run once at night.

 

Example table:

continent     country     type         minPrice       maxPrice        count

0                1              6             20 000         60 0000         990

 

Getting number of results of what someone searches is very easy this way. No counts. Just search country, minPrice and maxPrice and get the column count. This would speed up the searching by users a lot.

 

=====

 

But now the hard part. Building this query to fill the table.

 

Finding the results like per country and type aint hard. It will be just a group over country and type and count and insert into cache table as mentioned above. But the price ranges are a different story.

For example the following prices are available on the form where you select minPrice and maxPrice on the website:

 

25,000

50,000

75,000

100,000

125,000

150,000

175,000

200,000

225,000

250,000

275,000

300,000

325,000

350,000

375,000

400,000

450,000

500,000

550,000

600,000

650,000

700,000

750,000

800,000

 

This means there are A LOT of possibilities people choose from.

25,000 – 50,000

25,000 – 75,000

etc

50,000 – 75,000

65,000 -  100,000

etc  etc

 

I’ve tried to search all possibilities between country, type and minPrice, maxPrice through PHP. But 25,000 queries will be executed. No success

I’ve tried to build up a whole query that searches like:

SELECT `country_id`,

count(`id`) AS `count`,

`price_data` BETWEEN 0 AND 25000 AS `0till25000`,

`price_data` BETWEEN 0 AND 50000 AS `0till50000`,

`price_data` BETWEEN 0 AND 500000 AS `0till500000`,

`price_data` BETWEEN 0 AND 800000 AS `0till800000`

FROM `table`

GROUP BY `country_id`, `0till25000`, `0till50000`, `0till500000`, `0till800000`;

etc  with all possibilities

 

But this query gets so big because of all the possibilities. MySQL won’t accept it.

 

Does any1 know of any intelligence function by MySQL that can try all possibilities between

25,000 and 800,000 with specific steps?

 

Or any other tips?

 

Thanks in advance!

 

Maurice

 

 

 

[fenway]

Why not use SUM( IF( yourfield between x and y), 1, 0) multiple times?

[mausie]

This is indeed possible. But then I would still get a 1mb query with all the ranges to test.

Me and my team decided not to cache price ranges and let the system cache it when the user searches for it.

 

Thanks for the help anyway!