1,105,290 Community Members

select count with complicated query

Member Avatar
adam.adamski.96155
Junior Poster
189 posts since Oct 2012
Reputation Points: 43 [?]
Q&As Helped to Solve: 40 [?]
Skill Endorsements: 6 [?]
 
0
 

Hello :)
I'm working with a database of postcodes. I have an existsing query that returns the postcodes within a certain radius:

select *, acos(cos(51.496502411798 * (PI()/180)) *cos(-0.13982862499836 * (PI()/180)) *cos(lat * (PI()/180)) *cos(lng * (PI()/180))+cos(51.496502411798 * (PI()/180)) *sin(-0.13982862499836 * (PI()/180)) *cos(lat * (PI()/180)) *sin(lng * (PI()/180))+sin(51.496502411798 * (PI()/180)) *sin(lat * (PI()/180))) * 3959 as Dist from SW having Dist < 1 order by Dist

Dist 1 is the number of miles for the search radius.

I am making a script that will allow the user to enter a postcode and return a list of postcodes in the given radius. There can be over 20,000 results of which only 1000 will be displayed. Currently, I get all results into a PHP array and use the count() function to return the total, and just display upto 1000.
I would like to make the query produce only a count of relevant matches, and then make a follow up query with a limit of 1000 which will stop the unneccesary collection of data over 1000 rows.

Thanks for your time :D

Member Avatar
pritaeas
mod_pritaeas
11,309 posts since Jul 2006
Reputation Points: 1,420 [?]
Q&As Helped to Solve: 1,835 [?]
Skill Endorsements: 155 [?]
Moderator
Featured
Sponsor
 
0
 

Just add LIMIT 1000 after your order by.

Member Avatar
adam.adamski.96155
Junior Poster
189 posts since Oct 2012
Reputation Points: 43 [?]
Q&As Helped to Solve: 40 [?]
Skill Endorsements: 6 [?]
 
0
 

For a moment I thought I was being REALLY stupid :D
Goedendag Pritaeious, but that only returns 1000 results even if there are 25000 matches. I want to make an initial query that will tell me the exact amount of matches and no data, and then a second query collecting the data with a limit of 1000, or one that does both if it is possible.
Thanks for your speedy consideration :D

Member Avatar
pritaeas
mod_pritaeas
11,309 posts since Jul 2006
Reputation Points: 1,420 [?]
Q&As Helped to Solve: 1,835 [?]
Skill Endorsements: 155 [?]
Moderator
Featured
Sponsor
 
1
 

Ah, okay. Just use the original query and select only COUNT(*) AS resultcount. That way the server does the counting, but only one record gets transferred to your script.

select count(*) as resultcount
from SW 
where (acos(cos(51.496502411798 * (PI()/180)) *cos(-0.13982862499836 * (PI()/180)) *cos(lat * (PI()/180)) *cos(lng * (PI()/180))+cos(51.496502411798 * (PI()/180)) *sin(-0.13982862499836 * (PI()/180)) *cos(lat * (PI()/180)) *sin(lng * (PI()/180))+sin(51.496502411798 * (PI()/180)) *sin(lat * (PI()/180))) * 3959) < 1
Member Avatar
adam.adamski.96155
Junior Poster
189 posts since Oct 2012
Reputation Points: 43 [?]
Q&As Helped to Solve: 40 [?]
Skill Endorsements: 6 [?]
 
0
 

It's perfect!
Thank you Pritaeious, as usual, a fast and concise solution :D

Question Answered as of 1 Year Ago by pritaeas
Member Avatar
pritaeas
mod_pritaeas
11,309 posts since Jul 2006
Reputation Points: 1,420 [?]
Q&As Helped to Solve: 1,835 [?]
Skill Endorsements: 155 [?]
Moderator
Featured
Sponsor
 
0
 

If I were you I'd put them in two stored procedures. Keeps your code nice and readable.

You
This question has already been solved: Start a new discussion instead
Post:
Start New Discussion
Tags Related to this Article